How to Recreate the SBSMonitoring Database

How to Recreate the SBSMonitoring Database

  • Comments 16
  • Likes

[Today's post comes to us courtesy of Damian Leibaschoff from Commercial Technical Support]

In SBS 2008 and SBS 2011 standard, we have a service called the Windows SBS Manager that is responsible for a number of tasks around monitoring, alert reporting and maintenance. This service relies on a SQL database running on a SQL express instance called SBSMonitoring. Under certain circumstances, the database may become unusable, when this happens you can experience behaviors like the SBS console crashing, or incorrectly reporting the status of machines. When this happens there might be a need to create a new, blank, database to regain functionality. Since all the information collected is dynamic, by creating a new database you would lose only any custom reports you may have created in the SBS Console, report customizations, and all the archived reports. Furthermore, upon recreating the database, it will need some time to contact all clients and report on their status, this time may vary, since clients may be offline, it is recommended to wait 48hrs before trying to validate the data.

To recreate the databases use these steps:

  1. Download the following package:
  2. https://skydrive.live.com/self.aspx/.Public/MonitoringDBRecreate/Create-SBSMonitoringDb.zip?cid=d5fe25afb6c3615f&sc=documents
  3. Extract the files to c:\windows\temp
  4. Complete the one set of the following steps according to the version you are working with:

For SBS 2008

  1. Launch an administrative PowerShell session
  2. Launch the script:
    C:\windows\temp\Create-SBSMonitoringDb.ps1
  3. After completing the script and verifying that that monitoring is working, complete the steps in this article: http://blogs.technet.com/b/sbs/archive/2009/07/14/sbs-2008-console-may-take-too-long-to-display-alerts-and-security-statuses-display-not-available-or-crash.aspx

For SBS 2011 Standard

  1. Launch "C:\Program Files\Windows Small Business Server\Bin\MoveDataPowerShellHost.exe" as an administrator
  2. Launch the script:
    C:\windows\temp\Create-SBSMonitoringDb.ps1

Trouble Shooting:

File C:\windows\temp\create-sbsmonitoringdb.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.
By default we should have the required code execution policy on our SBS servers, this issue is usually related to the way the script was downloaded into the affected server. Open the properties of the download script file from Windows Explorer and click on the Unblock option.

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
  • Hooray!  I've been waiting for this for a long time.  I've copying SMSMonitoring databases from working servers to fix broken ones.

  • If you get problems with this script, search "Stop-Service DataCollectorSvc" and the next line "Stop-Service 'MSSQL$SBSMONITORING'" and add -force before starting the script

  • Ran this script and it did not work. no errors, no new database. SBS2008 up to date.

    Anything else?

  • Works on SBS 2011 fine, THANKS

  • Thought our SBS2011 was really sluggish due to sql memory usage. Checked SBSMonitoring db and it was over 10GB. This fix did it and now have great performance.

  • Hi,
    I have a problem with stoping these 2 services: ' If you get problems with this script, search "Stop-Service DataCollectorSvc" and the next line "Stop-Service 'MSSQL$SBSMONITORING'" and add -force before starting the script"

    Any suggestions on how to force them to stop?

    Thanks in advance

  • To clarify I'm getting this:
    PS C:\windows\temp> .\Create-SBSMonitoringDb.ps1
    Stop-Service : Cannot stop service 'SQL Server (SBSMONITORING) (MSSQL$SBSMONITO
    RING)' because it has dependent services. It can only be stopped if the Force f
    lag is set.
    At C:\windows\temp\Create-SBSMonitoringDb.ps1:28 char:13
    + Stop-Service <<<< 'MSSQL$SBSMONITORING'
    + CategoryInfo : InvalidOperation: (System.ServiceProcess.Service
    Controller:ServiceController) [Stop-Service], ServiceCommandException
    + FullyQualifiedErrorId : ServiceHasDependentServices,Microsoft.PowerShell
    .Commands.StopServiceCommand

    Rename-Item : The process cannot access the file because it is being used by an
    other process.
    At C:\windows\temp\Create-SBSMonitoringDb.ps1:45 char:5
    + ren <<<< $dbpath -NewName "SBSMonitoring.mdf.$date"
    + CategoryInfo : WriteError: (C:\Program File...SMonitoring.mdf:S
    tring) [Rename-Item], IOException
    + FullyQualifiedErrorId : RenameItemIOError,Microsoft.PowerShell.Commands.
    RenameItemCommand

    Backup file created - remove it after functionality is restored to recover the d
    isk space .2014_12_07_12_37_09
    Rename-Item : The process cannot access the file because it is being used by an
    other process.
    At C:\windows\temp\Create-SBSMonitoringDb.ps1:49 char:5
    + ren <<<< $ldpath -NewName "SBSMonitoring_log.LDF.$date"
    + CategoryInfo : WriteError: (C:\Program File...itoring_log.LDF:S
    tring) [Rename-Item], IOException
    + FullyQualifiedErrorId : RenameItemIOError,Microsoft.PowerShell.Commands.
    RenameItemCommand

    Backup file created - remove it after functionality is restored to recover the d
    isk space C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBSMONITORING\MSSQL\D
    ata\SBSMonitoring_log.LDF.2014_12_07_12_37_09

    GAC Version Location
    --- ------- --------
    False v4.0.30319 C:\Program Files\Windows Small Business Server\Bin\Mon...
    Changed database context to 'SBSMonitoring'.

    (1 rows affected)


    PS C:\windows\temp>

  • Figured out force command, but now getting following. I see that MSSQL$SBSMONITORING gets stop after my command, but as soon as i execute next line with .\Create-SBSMonitoringDb.ps1 it shows as started:

    PS C:\windows\temp> Stop-Service 'MSSQL$SBSMONITORING'-force
    PS C:\windows\temp> .\Create-SBSMonitoringDb.ps1
    Stop-Service : Cannot stop service 'SQL Server (SBSMONITORING) (MSSQL$SBSMONITO
    RING)' because it has dependent services. It can only be stopped if the Force f
    lag is set.
    At C:\windows\temp\Create-SBSMonitoringDb.ps1:28 char:13
    + Stop-Service <<<< 'MSSQL$SBSMONITORING'
    + CategoryInfo : InvalidOperation: (System.ServiceProcess.Service
    Controller:ServiceController) [Stop-Service], ServiceCommandException
    + FullyQualifiedErrorId : ServiceHasDependentServices,Microsoft.PowerShell
    .Commands.StopServiceCommand

    Backup file created - remove it after functionality is restored to recover the d
    isk space .2014_12_07_12_52_35
    Backup file created - remove it after functionality is restored to recover the d
    isk space C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBSMONITORING\MSSQL\D
    ata\SBSMonitoring_log.LDF.2014_12_07_12_52_35

    GAC Version Location
    --- ------- --------
    False v4.0.30319 C:\Program Files\Windows Small Business Server\Bin\Mon...
    Changed database context to 'SBSMonitoring'.
    Msg 208, Level 16, State 1, Server MBDMSERVER\SBSMONITORING, Line 2
    Invalid object name 'ReportConfiguration'.


    PLEASE HELP

  • I continue having this problem I try this solution but this is what I get when I try to execute it: (I have SBS 2011)

    MoveData PowerShell

    PS C:\Windows\system32> C:\windows\temp\Create-SBSMonitoringDb.ps1
    Start-Service : No pudo iniciarse el servicio 'SQL Server (SBSMONITORING)
    (MSSQL$SBSMONITORING)'.
    En C:\windows\temp\Create-SBSMonitoringDb.ps1: 54 Carácter: 1
    + Start-Service 'MSSQL$SBSMONITORING'
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OpenError: (System.ServiceProcess.ServiceControl
    ler:ServiceController) [Start-Service], ServiceCommandException
    + FullyQualifiedErrorId : StartServiceFailed,Microsoft.PowerShell.Commands
    .StartServiceCommand

    Start-Service : No se puede iniciar el servicio 'Administrador de Windows SBS
    (DataCollectorSvc)' debido al error siguiente: No se puede iniciar el servicio
    DataCollectorSvc en el equipo '.'.
    En C:\windows\temp\Create-SBSMonitoringDb.ps1: 61 Carácter: 1
    + Start-Service DataCollectorSvc
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OpenError: (System.ServiceProcess.ServiceControl
    ler:ServiceController) [Start-Service], ServiceCommandException
    + FullyQualifiedErrorId : CouldNotStartService,Microsoft.PowerShell.Comman
    ds.StartServiceCommand


    GAC Version Location
    --- ------- --------
    False v4.0.30319 C:\Program Files\Windows Small Business Server\Bin\Mon...
    HResultado 0xFFFFFFFF, nivel 16, estado 1
    SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFF
    FF].


    PS C:\Windows\system32>

  • Perfect! Works a treat in SBS2011 Standard!

  • Completely unthrilled with this script which merely succeeded in trashing my monitoring database. Here is the partial output:

    PS C:\> .\\Windows\Temp\Create-SBSMonitoringDb.ps1
    WARNING: Waiting for service 'SQL Server (SBSMONITORING) (MSSQL$SBSMONITORING)' to finish stopping...
    WARNING: Waiting for service 'SQL Server (SBSMONITORING) (MSSQL$SBSMONITORING)' to finish stopping...
    Backup file created - remove it after functionality is restored to recover the disk space .2015_02_16_05_40_53
    Backup file created - remove it after functionality is restored to recover the disk space C:\Program Files\Microsoft SQL
    Server\MSSQL10_50.SBSMONITORING\MSSQL\Data\SBSMonitoring_log.LDF.2015_02_16_05_40_53
    WARNING: Waiting for service 'SQL Server (SBSMONITORING) (MSSQL$SBSMONITORING)' to finish starting...
    WARNING: Waiting for service 'Windows SBS Manager (DataCollectorSvc)' to finish starting...
    Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'file:///c:\program files\Windows small business server\bin\MonitoringCommon.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded."
    At C:\Windows\Temp\Create-SBSMonitoringDb.ps1:64 char:39
    + [system.reflection.assembly]::LoadFrom <<<< ("c:\program files\Windows small business server\bin\MonitoringCommon.dll
    ")
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    New-Object : Cannot find type [Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContent]: make sure the assembly containing this type is loaded.

    followed by about a dozen other errors the same type, complaining about this DLL mismatch.

  • I suggest editing the script to change the line "Stop-Service 'MSSQL$SBSMONITORING'" to "Stop-Service 'MSSQL$SBSMONITORING' -force"

  • I had to add the -force to those two lines as well to the script.
    After that I manually ran a report and it worked fine. Thank you!

  • Ok, many thanks for this!

  • Thanks for the info on this fix. Our server was S U P E R Sluggish. This fix solved the problem. Unblock option is helpful!