OpsMgr 2012 Data Warehouse Health Check Script

Written by Tao Yang

Note (19/06/2015): This script has been updated to version 1.1. You can find the details of version 1.1 here: http://blog.tyang.org/2015/06/19/opsmgr-2012-data-warehouse-health-check-script-updated/. The download link at the end of this post has been updated too.

I’m sure you all would agree with me that the OpsMgr database performance is a very common issue in many OpsMgr deployments – when it has not been designed and configured properly. The folks at Squared Up certainly feels the pain – when the OpsMgr Data Warehouse database is not performing at the optimal level, it would certainly impact the performance of Squared Up dashboard since Squared Up is heavily relied on the Data Warehouse database.

So Squared Up asked me to build a Health Check tool specific to OpsMgr data warehouse databases, in order to help customers identify and troubleshooting the performance related issues with the data warehouse DB. Over the last few weeks, I have been working on such a script, focusing on the data warehouse component, and produces a HTML report in the end.

We have decided to make this tool not only available to the Squared Up customers, but also to the broader community, free of charge. So on that, BIG Thank-You to Squared Up’s generosity.

Before I dive into the details,  I’d like to show you what the report looks like. You can access the sample report generated against my lab MG here:


As shown in this sample, the report consists of the following sections:

Management Group Information

  • Management group name and version
  • server names for RMS Emulator, Operational DB SQL Server, Data Warehouse SQL server
  • Operational DB name, Data Warehouse DB name
  • Number of management servers, Windows agents, Unix agents, managed network devices and agentless managed computers
  • Current SDK connection count (total among all management servers)

Data Warehouse SQL Server information

  • Server hardware spec and OS version
  • SQL server version and collation
  • Minimum and Maximum assigned memory to the SQL server

Data Warehouse SQL DB information

  • DB Name, creation date, collation, recovery mode
  • Current state, is broker enabled, is auto-shrink enabled
  • Current DB size (both data and logs), free space %
  • Growth settings, last backup date and backup size

Temp DB configuration

  • File size, max size and growth settings for each file used by Temp DB

SQL Service Account Configuration

  • If the SQL Service account has “Perform volume maintenance tasks” and “Lock Pages in Memory” rights

Data Warehouse Dataset Configuration

  • Dataset retention setting
    • Retention setting for each dataset
    • current row count, size and % of total size of each dataset
  • Dataset aggregation backlog
  • Staging Table Row Count for the following tables:
    • Alert.AlertStage
    • Event.EventStage
    • Perf.PerformanceStage
    • State.StateStage
    • ManagedEntityStage

Key SQL and OS performance counters

  • SQL performance counters
    • SQLServer.Buffer.Manager\Buffer cache hit ratio
    • SQLServer.Buffer.Manager\Page.Life.Expectancy
  • Operating System performance counters
    • Logical Disk(_total)\Avg. disk sec/Read
    • Logical Disk(_total)\Avg. disk sec/Write
    • Processor Information (_total)\% Processor Time

Collect Data Warehouse performance related events from each management server

  • Event ID: 2115
  • Event ID: 8000
  • Event ID: 31550-21559

Since each environment is different, therefore I didn’t want to create a fix set of rules to flag any of above listed items good or bad. but instead, at the end of each section, I have included some articles that can help you to evaluate your environment and identify if there are any discrepancies.


This script has the following pre-requisites:

  • The user account that is running the script (or the alternative credential passed into the script) must have the following privileges:
    • local administrator rights on the Data Warehouse SQL server and all Management servers
    • A member of the OpsMgr Administrator role
    • SQL sysadmin rights on the Data Warehouse SQL server
  • WinRM (PowerShell Remoting) must be enabled on the Data Warehouse SQL Server
  • The OpsMgr SDK Assemblies must be available on the computer running the script:
    • The script can be executed on a OpsMgr management server, web console server, or a computer that has OpsMgr operations console installed
    • OR, manually copy the 3 DLLs from “<management server install dir>\SDK Binary” folder to the folder where the script is located.

Executing the script

The only required parameter is –SDK <OpsMgr Management Server name>, where you need to specify one of your management server (doesn’t matter which one). Additionally, if you use the –OpenReport switch, the HTML report will be opened in your default browser in the end. If you use -OutputDir to specify a directory, the reports will be saved to this directory instead of script root directory. If the directory you’ve specified is not valid, the script will save the reports to the script root directory instead (updated 19/06/2015). You can also use –verbose switch to see the verbose output:


.\SCOMDWHealthCheck.ps1 –SDK “OpsMgrMS01” -OutputDir C:\Temp\Reports\ –OpenReport –Verbose

Or if you need to specify alternative credential:

$password = ConvertTo-SecureString –String “password12345” –AsPlainText –Force

.\SCOMDWHealthCheck.ps1 –SDK “OpsMgrMS01” –Username “domain\SCOM.Admin” –Password $Password –OpenReport –Verbose


The report outputs the following files:

  • Main HTML report
  • Main Report in XML format
  • Windows Event export from each management server in a separate HTML page
  • Windows Event export from each management server in a separate CSV file

Note: The XML file is produced so if anyone wants to develop another set of tool to analyse the data for their own environment, it would be very easy to read the data from the XML file.

The script writes the list of the file it generated as output:


Possible Areas for Improvement

Due to the limited environments that I have access to, I am unable to test this script in environments where Data Warehouse DB is installed on a named SQL instance or a SQL Always-On setup. So if your environment is setup this way, please contact me and let me know what’s working and what’s not. This issue is now fixed in version 1.1 (Updated 19.06/2015)


I couldn’t have done this by myself. I’d like to thank the following people (in random order) who helped me in testing and provided feedbacks:

Folks from Squared Up: Glen Keech, Richard Benwell

SCCDM MVPs: Marnix Wolf, David Allen, Daniele Grandini, Cameron Fuller, Simon Skinner, Scott Moss, Fleming Riis

And, the legendary Kevin Holman

I’d also like to thank for all the people who has indirectly contributed to this tool (where I included links to their awesome articles and publications in the report). Some of them are already listed above, but here are few more: Paul Keely (Author for the SQL Server Guide for System Center 2012 whitepaper), Michel Kamp, Bob Cornelissen, Stefan Stranger and Oleg Kapustin.


You can download the script from the link below. Please place the 2 files in the zip file in the same directory:


Lastly, as always, please feel free to contact me if you’d like to provide feedback.



34 comments on “OpsMgr 2012 Data Warehouse Health Check Script

  1. Great looking script. I think that i have found a small possible bug 🙂

    On one of my machines I am having an issue where it dosnt seem to be getting the DW server / DW DB name. It seems like line 205 $Item.declaredFields dosnt exist. There is DeclaringMethod and DeclaringType but no Declared Fields.

    That server is windows 2012 R2 with SCOM 2012 R2 RU 6 Console and Powershell. It also has WMF 5.0.

    My other workstation is running windows 7 and it seems to work correctly.


    • Hey Davey,
      Thanks for the feedback. I’ve never tried this on a machine running Powershell 5.0. I’ll give it a try and see if I can identify the problem.


    • Hi Davey,

      I’m wondering if you could test the updated script for me on a PoSh 5.0 machine? if you are OK with that, could you send me an email and I’ll reply with the updated script? Thanks.

  2. Wow…awesome and definitely very generous. I am going to try it in my TST 2012 R2 UR5 infra ASAP. I also am using SQL named instances (all my stuff is 2012 versions – OS, SQL).

  3. Attempting to run but receive error that the script cannot be loaded as it “is not digitally signed.”

    File D:\scripts\DW HealthCheck Script\SCOMDWHealthCheck.ps1 cannot be loaded. The file D:\scripts\DW HealthCheck
    Script\SCOMDWHealthCheck.ps1 is not digitally signed. You cannot run this script on the current system. For more information
    about running scripts and setting execution policy, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
    + CategoryInfo : SecurityError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnauthorizedAccess

  4. More info on getting pre-reqs configured would be appreciated rather than having thousands of us searching the web for resolution. VERBOSE: [server] Connecting to remote server failed with the following error message : The WSMan client cannot process the request. Proxy is not supported under HTTP transport. Change the transport to HTTPS and specify valid proxy information and try again.

    • Hi Rob, I did include an error message in this section (line 850): Write-Error “Unable to establish WinRM (PS Remoting) session to the OpsMgr Dataware House SQL server $DWServerName. Please make sure WinRM is enabled and properly configured.”

      Do you think this is not sufficient and I need to add more information? such as use “Enable-PSremoting -force” to enable WinRM? maybe?

      • Hi Tao.

        That is the error message you also get if the DW server is not identified. It may be worth while putting in the verbose messages if it dose identify the DW server and DB. Related to my issue above. 🙂


        • Thanks Davey. Are you use a named SQL instance?

          • No Just default instance. But the server that i am running on is running WMF 5.
            DW and DB are on separate servers. I can see if i can stand up a PDT build with DW on a named instance or give you the xml for it you you want ?

          • yeah, with WMF 5, I found the differnces between version 5 and version 4. it will get fixed in the next release. Don’t worry about standing up another environment. Someone else emailed me with the same issue and I’m trying to get some details from him. Thanks for your help. I’ll add a few more verbose lines in this section.

  5. When I run this, it generates a lot of errors related to not being able to log into the OperationsManagerDW DB. I did some investigation and found in the verbose logging it says…

    VERBOSE: Connecting to SQL DB master on server oldDWserver.domain.com using integrated security.

    I obfuscated the name, but you can see that it’s unable to connect to the warehouse server because the warehouse server name is wrong (old, outdated). We had to move our warehouse database to a different server a while back. We followed the instructions here: https://technet.microsoft.com/en-us/library/hh268492.aspx?f=255&MSPPError=-2147217396

    I even double checked all the registry values and tables and they are all, indeed, pointing to the correct new DW server. However, whatever strategy you are using to discover the DW server is using outdated information. Any ideas? Is there a spot in that KB that MS missed?

    • I’m reading the data warehouse server name from SCOM SDK. if you open the script, you’ll see a Get-OMDefaultSettings function. I used that TechNet instruction to move the DW in my lab in the past as well, seems working fine. Did you check those tables on the Ops DB and the new DW server is indeed configured?

    • Well I fixed it by use the SDK that you used and setting the correct value with SetDefaultValue method. Basically I pasted all the code in Get-OMManagementGroupDefaultSettings into an interactive PowerShell to set up the variables. Then explored the results until I found the setting I wanted (the data warehouse server). So then I stored that in a variable.

      $var = $arrRumtimeTypes[7].DeclaredFields[1].GetValue(“DataWarehouseServerName”)

      Then i changed the value for the stored variable like this…


      After that, the script works. Looks like a pretty major step was left out of that KB as far as updating the settings for what is exposed in the Settings SDK class. Who knows what else in the future would rely on that and break.

      I hope this helps somebody else. 🙂

  6. Hi,
    First of all thanks for sharing the script.
    I tried to run it in my OpsMgr 2012 but i get the following error msg:

    PS C:\DW HealthCheck Script> C:\DW HealthCheck Script\SCOMDWHealthCheck.ps1
    cmdlet SCOMDWHealthCheck.ps1 at command pipeline position 1
    Supply values for the following parameters:
    (Type !? for Help.)
    You cannot call a method on a null-valued expression.
    At C:\DW HealthCheck Script\SCOMDWHealthCheck.ps1:186 char:2
    + $TopLevelNestedTypes = $SettingType.GetNestedTypes()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Test-PsRemoting : Cannot bind argument to parameter ‘ComputerName’ because it is an empty string.
    At C:\DW HealthCheck Script\SCOMDWHealthCheck.ps1:845 char:51
    + $bDWRemotingEnabled = Test-PsRemoting -Computer $DWServerName
    + ~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [Test-PsRemoting], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Test-PsRemoting

    C:\DW HealthCheck Script\SCOMDWHealthCheck.ps1 : Unable to establish WinRM (PS Remoting) session to the OpsMgr Dataware House SQL server
    . Please make sure WinRM is enabled and properly configured.
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,SCOMDWHealthCheck.ps1

    Pre-requisites check failed. This script is unable to continue. Please make sure all pre-requisites are met and then re-run the script.

    Please help.
    Thank you.

  7. Hello,

    Thank you for sharing this, it looks great.

    I’ve a problem running this script, I get the following error:

    C:\Temp\DW HealthCheck Script\DW HealthCheck Script\SCOMDWHealthCheck.ps1 : Unable to establish WinRM (PS Remoting)
    session to the OpsMgr Dataware House SQL server server-SQL5\server_SQL5. Please make sure WinRM is enabled and properly

    The server “server-SQL5\server_SQL5” is an old DW Server which is not used anymore. I’ve migrated the DW some weeks ago. No idea where the script gets this config from?

    I used technet manual to move the DW to the new server..


    • Hi Nicolas, someone has also experienced this issue (see previous comments), the script is getting the DW server name via SDK. It’s on my to-do list to find out exactly which table does the SDK query to get the data. but for now, you can refer to the previous comment on how to update this setting via SDK

      • Hello Tao, thanks for the input, I’ve managed to change it. Unfortunately I still getting the same error (now with the correct server):

        C:\Temp\DW HealthCheck Script\SCOMDWHealthCheck.ps1 : Unable to establish WinRM (PS Remoting) session to the OpsMgr Dataware House SQL server server-ha-sco\sql6. Please make sure WinRM is enabled and properly configured.
        At line:1 char:1

        I’ve tested WinRm Connection to server-ha-sco and it is working. It looks like the script connects to server-ha-sco\sql6 instead of server-ha-sco only.

        • ah, sorry, forgot to respond to this in your previous comment. yes, it’s a known issue for named SQL instances. I’m getting it fixed. the updated version should be published in few days (I’m waiting for some test results).

  8. Pingback: SCOM: Add community power and keep the engine running… | Dieter's System Center Blog

  9. Tao,

    Very good script!!! Congratulations! and thanks for sharing. I ran the script and the report did not bring the Data File Current Size MB, Data File Max Size DB and Data Growth Settting. The value is System.Object[]. My Sql server is 2012 Ent. AwaysOn. Any tip to fix this?

  10. 09.01.2016 – The download link is broken.

    • I had an issue with the updated Download Manager WordPress plugin which prevent people from accessing the site. In order to get the site back and up, I had to disable plugin – thus broke the link. it should be fixed now.

  11. Thank you so much for this fantastic script. A great job from all involved. 🙂

  12. Hi Tao,
    When i run the script, it returns a SQL server name that is not correct. Actually, it is a old SQL server name. We migrated quiete a while ago… How does this work? I checked the registry and this old name is nowhere to be found.

Leave a Reply