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

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

  • Comments 12
  • Likes

[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
    clip_image001
  • 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Comments
  • upon running the cmd i get the following error:

    Sqlcmd:  Error:  Syntax error at line 300 near command ''' in file 'c:\updateSBSMonitoring.sql'

  • I have followed the procedure provided in this blog and receive the following error:

    "Sqlcmd: Error:  Syntax error at line 300 near command ''' in file c:\updateSBSMonitoring.sql"

    What am i doing wrong?

  • AAS,

    Please re-download the file.  The file has less than 50 lines.  If yours is reporting 300+, the file must have become corrupted during download.

    Thanks,

    Justin

  • Whilst this appears to have resolved the "Not Available" problem we were experiencing I have noticed that the sbsmonitoring_log database has grown 1Gb overnight.

    Will this be reduced during maintenance or is it something I will need to address ?

    Thank you

  • After redownloading it and running the instructions it worked.  Thanks.

  • Hello

    Could you please tell me how often the SBS Monitoring database is supposed to run it's cleanup job ? I applied your sql file 3 days ago, and my database still has 90 days of events logged.

    Does the cleanup job also get logged in the event viewer ? That way I can see when it was last run.

    Thank you

  • Hello David,

    The cleanup job should run nightly.  Depending on the server hardware your job may be timing out before it has time to complete.  The cleanup job was designed to only have to clean up a few days (usually 1) worth of data.  When we make the change we are cleaning up 60 days worth of data, which can take a lot longer.  In your case I reccomend you run the cleanup job manually.

    Download and edit the updated script so that it only contains:

    USE SBSMonitoring

    EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]

    Then rerun the main steps 2 and 3 (backup and then run script).  This will manually launch the cleanup, and can take 5-15 minutes to run.

    Note: we updated the script to manually launch the cleanup, but it appears that you have the previous version, so make sure you download the newest copy.

    Thanks,

    Justin

  • Thank you Justin. The cleanup job is working fine now, as is my console.

    Having run the updated SQL I found my sbsmonitoring_log file increased by 2Gb in size. I'm not sure if this is consistent with your findings.

  • The author does a great job of arranging this vast body of knowledge into one blog. I do agree w/the other reviewers on that point.

  • I have applied this fix successfully, and when I generate a summary report the console still crashes. Is there any other suggestions that you could have me try ?

  • I have applied the fix successfully. But the sbs console still crashes when i run a daily summary report. Any other suggestions ?