By: Jeremy Kadlec | Comments | Related: > Tools
Problem
Getting insight into a SQL Server issue can be difficult without a comprehensive set of information. To first determine all of the configurations and information sources is difficult enough, but to capture the information in an automated manner on a regular basis can quickly become a major challenge. But, if you have an issue, this information is extremely valuable to determine changes to the system over time and their subsequent impacts.
Solution
One option to capture a comprehensive set of SQL Server system information for troubleshooting purposes is via 1 or more of the Microsoft Product Support (MPS) Reporting Tools. The MPS reports are free from Microsoft and cover many of their platforms. These tools were developed for Microsoft Product Support Services to gather information to troubleshoot an issue. The MPS reports can become very valuable to accurately and efficiently troubleshooting a critical issue with Microsoft by being able to compare the configurations prior to and during a serious issue.
Overview
The MPS Report for SQL Server 2000 and 2005 is MPSRPT_SQL.exe. The report can be executed with either a lite or full mode parameter. Depending on the mode and amount of information on the SQL Server, 10 to 30 minutes is necessary to run the report which is fairly quick and painless as compared to the value of the data. With that being said, as a best practice the reports should be running during low usage time periods and according to some of the documentation, the full parameter option should only be used based on direction from Microsoft.
How do I execute this report?
Just double click on the MPSRPT_SQL.exe file in Windows Explorer to start the process in lite mode.
Another option is to start the process at a command prompt. Below is sample code to execute MPSRPT_SQL.exe at a command prompt with the quiet option (/Q):
- Lite mode - MPSRPT_SQL.EXE /C:"cmd /c Setup.cmd /Q"
- Full mode - MPSRPT_SQL.EXE /C:"cmd /c Setup.cmd /F /Q"
Below is a screen shot of the report as it begins to run:
What data is collected?
The lite and full reports capture information on the following items:
- Hardware configs
- Windows system information and Event logs
- Registry and security information
- Hotfix information
- Net Framework Registry
- Cluster Information
-
SQL Server, Reporting Services, OLAP configs
For a complete listing of the information collected review the README.txt file.
How do I automate running this report?
As a best practice, when running this report as a proactive means to capture data, it should be run during off hours when the system has a minimal amount of usage. To automate this process during off hours, the SQL Server MPS report can be run via a Job on a weekly, monthly or quarterly basis. In addition, it is recommended to move the output to a file server that is backed up to be able to access the reports in the future if a failure occurs at the Windows volume level.
Since the MPS reports are already installed, the code needed to generate a new SQL Server MPS report via a SQL Server Job in lite mode is as follows:
- MPSRpt.cmd /Q
What is the output for the reports?
Three *.cab files are generated and written to the C:\WINDOWS\MPSReports\SQLServer\rpt\cab directory by default. In the aggregate 100's of files are generated based on the software installed on the server. Each one of the files are clearly named and can be reviewed.
What are the other MPS reports that are available?
Below outlines the currently available MPS reports:
- Alliance Edition (Mpsrpt_alliance.exe): A general, all-purpose edition of the utility that captures a broad range of configuration information
- Cluster Edition (Mpsrpt_cluster.exe): An edition that captures information that is relevant to Windows Cluster Service issues
- Directory Services Edition (Mpsrpt_dirsvc.exe): An edition that captures information that is relevant to Directory Services issues
- Network Edition (Mpsrpt_network.exe): An edition that captures information that is relevant to networking issues
- Setup Edition (Mpsrpt_setupperf.exe): An edition that captures information that is relevant to setup and performance issues
- Software Update Services Edition (Mpsrpt_sus.exe): An edition that captures information that is relevant to Software Update Services issues
- SQL Edition (Mpsrpt_sql.exe): An edition that captures information that is relevant to SQL.
- MDAC Edition (Mpsrpt_mdac.exe): An edition that captures information that is relevant to MDAC.
- Exchange Edition (Mpsrpt_exchange.exe): An edition that captures information that is relevant to Microsoft Exchange.
Source - Overview of the Microsoft Configuration Capture Utility (MPS_REPORTS)
Next Steps
- You never know when your SQL Server may have an issue. As such, plan to download and run these reports to capture this system information just in case you need to open a support case.
- As you deploy new SQL Servers in your environment, run these reports to capture a baseline set of information. As significant changes are made, rerun these reports or setup SQL Server Jobs to execute and capture the data on a regular basis.
- If you have an issue, go back to earlier reports to validate the configurations are equal or determine the differences and adjust the SQL Server configurations as needed.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
