SBS 2008 Console May Take Too Long to Display Alerts and Security Statuses, Display Not Available, or Crash

[Today's post comes to us courtesy of Damian Leibaschoff and Justin Crosby]

Under certain conditions, the Windows SBS Console may show one or several of the following symptoms:

• The Other Alerts in the Home tab will show as “Not Available”
  
• The Console.Log may also contain exceptions like this one:
  
  [5164] 090620.094247.9164: Exception:
  ---------------------------------------
  An exception of type 'Type: System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has occurred.
  Timestamp: 06/20/2009 09:42:47
  Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
                     at System.Data.SqlClient.SqlDataReader.get_MetaData()
                     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
                     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
                     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                     at System.Data.SqlClient.SqlCommand.ExecuteScalar()
                     at Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.MonitoringSQLDataStore.GetAlertCountPerType(ComputerType type)
  
• The Security and Other Alerts status will take very long to update in the Home tab.
• The Security Status and Other Alerts will take a very long to populate in the Network, Computers tab.
• Manually generating a report in the Reports tab may cause the console to crash if the report includes the Security content option.

If you are seeing one or multiple symptoms like the ones described above, the following steps should help improve the overall performance of the Windows SBS console and prevent them from happening. There is always the chance that you might be experiencing an issue that is unrelated to this post but does present itself with similar symptoms, if that is the case, then the following steps might not provide relief.

1. Download the following file to the server you are going to be working on:
1. http://cid-d5fe25afb6c3615f.skydrive.live.com/self.aspx/.Public/updateSBSMonitoring.sql
2. We recommend you save the file to an easy to access path, such as c:\windows\temp.
2. Complete a backup of the SBSMonitoring Databases
1. Open Services from Administrative Tools, Services
2. Accept the UAC prompt
3. Find and stop the SQL Server (SBSMONITORING) service.
4. Make a copy of the files in the following folder:
1. C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data
5. Once the file backup is completed, start the SQL Server (SBSMONITORING) service.
3. Proceed to run the following set of commands to implement the improvements
1. Open an Administrative Command Prompt (Run As Admin)
2. Type the following command, substitute the path to the updateSBSMonitoring.sql file as needed (We recommend that you do NOT copy & paste this command directly from the blog post):
1. Sqlcmd -S %computername%\SBSMonitoring -E  -i c:\windows\temp\updateSBSMonitoring.sql
2. Success will show an output like this:
   Changed database context to ‘SBSMonitoring’
   (1 rows affected)

This command can take up to 10 minutes depending upon the speed of the hardware.  Once complete results should improve immediately.

For more information about monitoring your Windows SBS 2008 server, read Monitoring and Reporting in Windows SBS 2008.

Updated 7/17/2009: Included command to immediately begin clean-up.