New v5 release
There is a lot of scripts or tools to get information of SQL Server instance and databases but I didn’t find one that give all information I need at the same place with exporting or printing capabilities. Starting with SQL Server 2005, there is some
nice standard reports available in SQL Server Management Studio, but these reports couldn’t be enough to get a good overview for diagnostic purposes : you have to run also a lots of custom scripts to get more advanced information.
This set of SSMS custom reports will help to have such an quick overview of instance and database (on premise) and to answer the main questions when you discover a new server.
You'll get various information, easy available :
- Server information : version, edition, build, os platform, hardware, services, configuration options, memory configuration, security, etc…
- Storage : disk allocated, file sizes and allocation for all databases
- Last errors in SQL Server errorlog
- Performance information : wait states, cpu, memory usage and main performance counters, io latency, tempdb utilization, etc…
- Databases informations : database options, storage allocation, transaction logs and backups information, mixed collations, objects information, etc...
- Tables and index informations : primary keys (clustered, nonclustered, no primary key), unique constraints, existing indexes, tables without index, size of index keys, etc…
TSQL scripts of these reports are coming from various sources : my own scripts and other internet resources, like those of Glenn Berry or
System requirements and limitations
This custom SSMS report is intended to use only with Management Studio 2012 or later, but can get the information from instances of versions from 2005 to 2016 (some informations will be not available with oldest versions).
Sysadmin rights are required to run the reports : the reports temporarly modify instance options (sp_configure), and create a temporary udf.
List of reports :
- Main Server Dashboard.rdl : main information and starting point, other reports are linked to this one (but they can be used also separately).
- Server Report.rdl : instance information
- All Databases Report.rdl : databases overview
- Server Performance.rdl : instance performance informations
- Database Report.rdl : general database information. Could also to be run from a database in object explorer.
- Tables Report : tables informations. Could also to be run from a database in object explorer.
- Index Report : index informations. Could also to be run from a database or a single table object in object explorer.
- Statistics Report (since v2) : informations about columns and index statistics. Could also to be run from a database, a single table or a single statistic.
There is also a SQLAzure SSMS report, available with another download.
Installation and usage
These reports are provided "as is". Even the risk is low, use with your production servers is at your own risk.
Copy the rdl files to a folder that is accessible to the SQL Server Management Studio client. Right-click on the SQL instance in object explorer, select "custom reports" and browse to the location of the rdl file.