SQL Server System Report

There's a lot of information that can be retrieved from an instance of SQL Server; some of it you will find helpful, some of it you won't (it all depends on your environment, needs, and perspective). I've found, however, that there are certain key elements that I want to be kept up to date with, either as a way to spot potential problems or just for my own personal knowledge. With that in mind, I decided to construct a general "System Report" procedure that I could run either on-the-fly and display the results on-screen, or deploy and schedule on any instance and have the results automatically delivered to my inbox.

To output to the screen, simply type and run:

EXECUTE dbo.usp_SQL_Server_System_Report

There are two optional input parameters:

1. @v_Output_Mode: defaulted to NULL, you can pass a value of 'E' to have the results sent via e-mail (see below for further details)
2. @vUnused_Index_Days: defaulted to 7, this is the number of days the server has to have been online (since the last SQL Server startup) before the report will execute the "Unused Indexes" section of the code (see below for further details)

Additional Notes:

• Any piece of the code which produces an empty result set will be omitted from the final report (eliminating blank report sections)
• All numeric values use commas for thousandths separators
• All "size" values are rounded and converted to MB
• All "duration" values are in DAY(s) HH:MM:SS format, with any leading zeroes (when the time unit contains all zeroes) replaced by underscores for easy readability (example: a duration of 8 minutes and 12 seconds will be represented as _ Day(s) __:08:12, and a duration less than a second will be represented as _ Day(s) __:__:__)
• If you run the procedure using the e-mail output option, the subject line of the report will automatically be set to: SQL Server System Report: "Your Server Name"

A nice part about this procedure is that it sets up the initial framework to store various data set results into permanent tables (if you wish, and are willing to add a bit of extra code) so that the results can be analyzed and trended at a later point in time.

Notes Specific To The E-Mail Output Option:

Personally, I use the e-mail output option and have it set to run daily through a SQL Server Agent Job, though your needs may differ (if you decide to use an alternate schedule then you may want to adjust the code accordingly as some stats are for the last 24 hours only and will not be very useful if you plan to run the procedure say once a week). Also, feel free to remove any sections which you feel add little or no value to your particular environment.

The end result is an HTML formatted series of data grids (with header labels) sent in the body of an e-mail via sp_send_dbmail (database mail must be set up before you can use this option!). Only 1-2 variables need to be set within the code before it can be deployed:

1. @vRecipients: e-mail address(es) to send the report to (separated by semicolons if there's more than one)
2. @vCopy_Recipients: e-mail address(es) to CC the report to (separated by semicolons if there's more than one)

Report Output:

Each section of the report is listed below (e-mail / HTML output version shown), along with additional details and sample screenshots (most of which have had the data blurred from privacy reasons, with the output field descriptions listed under each section).

Server Instance Property Information:

This is a quick overview of your system's general properties:

• NetBIOS Name: NetBIOS name of the SQL Server
• Server Name: name of the SQL Server
• Edition: product edition
• Version: product version in the form of 'major.minor.build'
• Level: level of the version (original, service pack, beta)
• Clustered: indicates if instance is configured in a failover cluster
• Online Since: date since SQL Server last started
• Uptime: number of days since SQL Server last started
• Connections: shows the number of connections (successful or not) since SQL Server was last started
• Reads: disk reads (not cache reads) since SQL Server last started
• Writes: disk writes since SQL Server last started
• First Day Of Week: shows what the default first day of the week is on the server
• Windows Version: shows the operating system and version information for the instance

Fixed Drives Free Space:

Listing of the fixed drives attached to the server, along with the free space available on each:

• Drive Letter: letter assigned to the drive
• Free Space (MB): free space remaining on the drive

Database Size (Summary) / Distribution Stats:

Listing of each database's size stats, along with how the space has been distributed / allocated.

NOTE: this only displays for databases which are online, set to read / write mode (not read-only), are not in standby mode, and are not a database snapshot:

• Database Name: name of the database
• Total Size (MB): total size of the database (includes both data and log files)
• Unallocated (MB): space in the database that has not been reserved for database objects
• Reserved (MB): space allocated by objects in the database
• Data (MB): space used by data
• Index (MB): space used by indexes
• Unused (MB): space reserved for objects in the database, but not yet used

Database Recovery Model / Compatibility / Size (Detailed) / Growth Stats:

A more detailed view of the database space allocation, with suppression of duplicate columns (Database Name, Recovery Model, Compatibility) for easy readability, along with some general database attributes:

• Database Name: name of the database
• Recovery Model: recovery model the database has been set to
• Compatibility: SQL Server compatibility behavior
• File Type: type of file (rows, log, full-text)
• File Name: logical name of the database file
• Create Date: date the database was created (or renamed)
• File Size (MB): size of the file
• Max Size: maximum size the file can grow to
• Growth Increment: increment by which the file will grow when an auto-growth operation occurs

Last Backup Set Details:

Details surrounding the last set of backups to run for all backup types (will only list databases currently connected to the instance and will also include databases which have never been backed up). Includes suppression of duplicates on the Database Name column for easy readability:

• Database Name: name of the database
• Backup Set ID: backup set identification number
• Backup Type: type of backup performed (database, log, differential, etc.)
• Database Version: database version number
• Server Name: name of the server which created the backup
• Machine Name: name of the computer which created the backup
• Backup Start Date: start date of the last backup operation
• Backup Finish Date: finish date of the last backup operation
• Duration: time taken for the backup to execute
• Backup Size (MB): size of the backup set
• Days Ago: number of days since the backup was last executed

SQL Server Agent Jobs (Last 24 Hours):

List of all SQL Server Agent Jobs executed in the last 24 hours (either scheduled or manually):

• Job Name: name of the job
• Last Run Date / Time: date the job was last executed
• Last Status: last execution status of the job
• Duration: time taken to execute the job
• Next Run Date / Time: next scheduled execution of the job (if any)
• Days Away: number of days until the next scheduled execution of the job (if applicable)

Unused Indexes:

List of all unused indexes per database. By default, this section of code will not run until at least 7 days have passed since the server was last started (allowing for a decent amount of index statistics to be collected). You can change this number by passing a different value to the @vUnused_Index_Days input parameter if you feel this is too low or too high for your particular environment. The output includes "drop index" statements so you can easily update your tables, though each result should be carefully examined before any decision is made to run the code. It only checks indexes on tables which are not MS shipped, are not primary or foreign keys, and are not unique constraints.

NOTE: this only displays for databases which are online, set to read / write mode (not read-only), are not in standby mode, and are not a database snapshot:

• Database Name: name of the database
• Schema: name of the schema
• Object Name: name of the object
• Column Name: name of the indexed column
• Index Name: name of the index
• Disabled: indicates if the index is disabled
• Hypothetical: indicates if the index is hypothetical
• Drop Index Statement: SQL statement to drop the index

All friendly suggestions for additional report sections are always welcome. Enjoy!