My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).
There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.
DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)
So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.
Monday, 13 September 2010
Thursday, 19 August 2010
SystemCenterReporting Reindex Script
Can be found here. It was referred to here and here, however, the links within those pages are no longer extant.
You'll have to register, and I give no warranty as to how workable this is: however, it's taken me half an hour to find the scdw_reindex1.zip or the scdw_reindex.zip file, so I thought I'd link.
Monday, 19 July 2010
State 38
Error: 18456, Severity: 14, State: 38.
Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]
And the Application Event log detail generally shows something like:
In Bytes
0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..
Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.
The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.
A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.
Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]
And the Application Event log detail generally shows something like:
In Bytes
0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..
Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.
The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.
A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.
"I can't take this database offline!"
An anguished cry, which I have heard twice today. The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database." One generally ends up with 'Database is in transition...Error 952' when trying to ascertain what the databases is up to, and very little seems to have an effect, including restarting SQL in some cases: and that assumes that restarting SQL is even an option.
Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.
USE master
GO
ALTER DATABASE databasename
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional
Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.
USE master
GO
ALTER DATABASE databasename
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional
Tuesday, 6 July 2010
SQL Configuration Manager Suddenly Unavailable
Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.
Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:
"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"
Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.
They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:
C:\Program Files (x86)\Microsoft SQL Server\100\Shared
And running this command in cmd fixed the problem. Phew.
C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"
Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:
"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"
Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.
They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:
C:\Program Files (x86)\Microsoft SQL Server\100\Shared
And running this command in cmd fixed the problem. Phew.
C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"
Wednesday, 23 June 2010
Oooh! Squee! New Tool
Microsoft® SQL Server® 2008 R2 Best Practices Analyzer.
Tells you what could be improved in your installation, and how to go about it
Tells you what could be improved in your installation, and how to go about it
- Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
- Determines if the configurations are set according to the recommended best practices
- Reports on all configurations, indicating settings that differ from recommendations
- Indicates potential problems in the installed instance of SQL Server
- Recommends solutions to potential problems
Sweet. Note: there have been some problems with installing it owing to Workgroups problems and Kerberos issues. Solution here.
More Fun And Games with SCOM
For various reasons, chief among them the requirement for a more scalable deployment and the fact that the SCOM Server was "really starting to struggle" according to our Chief System Support Analyst (and, he should know: he's the one that's using the server all the time), we decided to shift the OperationsManager database, the OperationsManagerDW database and the ReportingServer databases onto a shiney new SQL Server. I am all about the shiney, after all. I am a girl geek.
Now, I thought it would be simple enough. Shift the OpsManager db. Shift the OpsManagerDW db. Shift the Report Server stuff. There are some nice articles on how to do this. Including a whole article about moving Report Server databases. Go and have a look at them.
Firstly, notice how the article about moving Report Server is almost identical to the one about moving OperationsManagerDW? That was the first gotcha. If you are moving the entire SQL Server, you need to follow the instructions in the article about moving in the document entitled 'How to Move the Operations Manager Reporting Server in Operations Manager 2007'. Ignore the one about 'How to Move the OperationsManagerDW Database in Operatios Manager 2007', other than the information about the Logins (step 9 onwards) and data sources (step 13 onwards).
Second gotcha? There is no sensible time to move SQL Server Reporting Services (SSRS) if you have custom reports. If you move it before the SCOM Report Server component, then there is the risk that installing SCOM's Report Server will wipe the custom reports. If you move it after the SCOM Report Server component, then there is the risk that you destroy the SCOM Report Server: it relies on a functional instance of SSRS.
The mistake I made was to move OperationsManagerDW according to that document's instructions, and then try to move the ReportServer having moved over the Report Server Databases part way through the process. This was silly: the ReportServer component did not install properly, it destroyed SSRS to the point that I had to reinstall it, and the IIS Application Pools refused to run:
Source W3SVC
Category (0)
Event 3221226474
Computer SQLSCOM
Message
Application pool 'ReportServer' is being automatically disabled due to a series of failures in the process(es) serving that application pool.
Source W3SVC
Category (0)
Event 2147484657
Computer SQLSCOM
Message
A process serving application pool 'ReportServer' terminated unexpectedly. The process id was '7468'. The process exit code was '0xffffffff'.
(there were several errors with the exit code '0xffffffff')
These linked to:
Error loading configuration file: Root element is missing.
w3wp!library!5!22/06/2010-09:20:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
Report Server's Logs also gave me:
An internal error occurred on the report server. See the error log for more details., Incorrect security descriptor version;
and
w3wp!library!7!06/22/2010-11:01:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Unable to load assembly Microsoft.EnterpriseManagement.Reporting.Security;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EnterpriseManagement.Reporting.Security' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.EnterpriseManagement.Reporting.Security'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssembly(String name)
If I tried running the application pools under the Local\System Account, well, they didn't fail, but Reporting Services refused to function. I had service unavailable messages, I had unable to connect messages, and I had a lovely set of errors for which I was referred to the Installation Log File for SCOM Report Server, and to look for 'value 3'. I had error 108 all about me.
In other words, I had a partial installation of SCOM Report Server, and it destroyed SSRS. Nothing wanted to work.
I fixed this by:
Backing up everything I could think of.
Uninstalling and Reinstalling SSRS (I had tried to revert back to the original databases on the server, with the original .snk file - and every other .snk file I could find. No dice). With SP3 (as it was SQL 2005).
Using Add and Remove Programs to get rid of the SCOM Report Server, which also took out the Data Warehouse component, but not the Data Warehouse itself. Windows was under the impression SCOM Report Server was properly installed - trying to repair that particular component made no difference whatsoever.
Using the resetsrs.exe tool, as part of the SCOM installation disk to make sure that SSRS was working properly.
Keeping a close eye on IIS and Application Pools while installing.
Treble checking that SSRS was working: without SSRS working, SCOM Report Server won't install or work.
Stopping the SCOM Services.
Reinstalling SCOM Report Server, and only installing the Report Server element.
This worked: however, I'd lost my custom reports. I will, at some point, try shifting SSRS to another server to see what went wrong with moving SSRS - at the moment, I'm quite happy with the default reports. The custom reports can be recreated based on the screenshots we took of them as a backup before we failed to move SSRS. It wasn't the end of the world to lose them.
The final hiccup with the whole thing was the removal of SQL from the original SCOM server. This went without a hitch: until we got a whole batch of errors to inform us that the Scheduler Data Source Module Failed Initialization: The Microsoft Operations Manager Scheduler Data Source Module has some invalid configuration. The IIS Discovery Probe Module Failed Initialization: an Error initializing IISDiscoveryProbe from config. The answer, after a lot of swearing was here. Turns out that the MSXMLS parser got uninstalled with SQL: I had no idea that there was a dependency for that, and we simply reinstalled that. All was then well.
If I were going to do this again, I would make very sure that I could restore SSRS properly before I did anything else. I would also test moving everything in a VM, and test moving it in different orders, to work out how best to get SSRS and all custom reports across.
Now, I thought it would be simple enough. Shift the OpsManager db. Shift the OpsManagerDW db. Shift the Report Server stuff. There are some nice articles on how to do this. Including a whole article about moving Report Server databases. Go and have a look at them.
Firstly, notice how the article about moving Report Server is almost identical to the one about moving OperationsManagerDW? That was the first gotcha. If you are moving the entire SQL Server, you need to follow the instructions in the article about moving in the document entitled 'How to Move the Operations Manager Reporting Server in Operations Manager 2007'. Ignore the one about 'How to Move the OperationsManagerDW Database in Operatios Manager 2007', other than the information about the Logins (step 9 onwards) and data sources (step 13 onwards).
Second gotcha? There is no sensible time to move SQL Server Reporting Services (SSRS) if you have custom reports. If you move it before the SCOM Report Server component, then there is the risk that installing SCOM's Report Server will wipe the custom reports. If you move it after the SCOM Report Server component, then there is the risk that you destroy the SCOM Report Server: it relies on a functional instance of SSRS.
The mistake I made was to move OperationsManagerDW according to that document's instructions, and then try to move the ReportServer having moved over the Report Server Databases part way through the process. This was silly: the ReportServer component did not install properly, it destroyed SSRS to the point that I had to reinstall it, and the IIS Application Pools refused to run:
Source W3SVC
Category (0)
Event 3221226474
Computer SQLSCOM
Message
Application pool 'ReportServer' is being automatically disabled due to a series of failures in the process(es) serving that application pool.
Source W3SVC
Category (0)
Event 2147484657
Computer SQLSCOM
Message
A process serving application pool 'ReportServer' terminated unexpectedly. The process id was '7468'. The process exit code was '0xffffffff'.
(there were several errors with the exit code '0xffffffff')
These linked to:
Error loading configuration file: Root element is missing.
w3wp!library!5!22/06/2010-09:20:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
Report Server's Logs also gave me:
An internal error occurred on the report server. See the error log for more details., Incorrect security descriptor version;
and
w3wp!library!7!06/22/2010-11:01:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Unable to load assembly Microsoft.EnterpriseManagement.Reporting.Security;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EnterpriseManagement.Reporting.Security' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.EnterpriseManagement.Reporting.Security'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssembly(String name)
If I tried running the application pools under the Local\System Account, well, they didn't fail, but Reporting Services refused to function. I had service unavailable messages, I had unable to connect messages, and I had a lovely set of errors for which I was referred to the Installation Log File for SCOM Report Server, and to look for 'value 3'. I had error 108 all about me.
In other words, I had a partial installation of SCOM Report Server, and it destroyed SSRS. Nothing wanted to work.
I fixed this by:
Backing up everything I could think of.
Uninstalling and Reinstalling SSRS (I had tried to revert back to the original databases on the server, with the original .snk file - and every other .snk file I could find. No dice). With SP3 (as it was SQL 2005).
Using Add and Remove Programs to get rid of the SCOM Report Server, which also took out the Data Warehouse component, but not the Data Warehouse itself. Windows was under the impression SCOM Report Server was properly installed - trying to repair that particular component made no difference whatsoever.
Using the resetsrs.exe tool, as part of the SCOM installation disk to make sure that SSRS was working properly.
Keeping a close eye on IIS and Application Pools while installing.
Treble checking that SSRS was working: without SSRS working, SCOM Report Server won't install or work.
Stopping the SCOM Services.
Reinstalling SCOM Report Server, and only installing the Report Server element.
This worked: however, I'd lost my custom reports. I will, at some point, try shifting SSRS to another server to see what went wrong with moving SSRS - at the moment, I'm quite happy with the default reports. The custom reports can be recreated based on the screenshots we took of them as a backup before we failed to move SSRS. It wasn't the end of the world to lose them.
The final hiccup with the whole thing was the removal of SQL from the original SCOM server. This went without a hitch: until we got a whole batch of errors to inform us that the Scheduler Data Source Module Failed Initialization: The Microsoft Operations Manager Scheduler Data Source Module has some invalid configuration. The IIS Discovery Probe Module Failed Initialization: an Error initializing IISDiscoveryProbe from config. The answer, after a lot of swearing was here. Turns out that the MSXMLS parser got uninstalled with SQL: I had no idea that there was a dependency for that, and we simply reinstalled that. All was then well.
If I were going to do this again, I would make very sure that I could restore SSRS properly before I did anything else. I would also test moving everything in a VM, and test moving it in different orders, to work out how best to get SSRS and all custom reports across.
Subscribe to:
Posts (Atom)