SQL Server Health Check

SQL Server Health Check


Hello there, if you are a SQL DBA the most common task that you perform is “SQL Server Health Check”. You may already have an automated report but this is for doing a quick SQL Server Health Check using native T-SQL. This script helps you in generating HTML report using T-SQL. There are situations where we need to quickly perform SQL Server Health Check:

  • You got a request to check the health of a SQL Server instance
  • When there is a complaint raised against the instance performance
  • When SQL Server restarted
  • Etc.

This SQL Server Health Check Reports:

SQL Server Instance Details: SQL Version, Edition, Service Pack, Processor Type etc.

Error Log: Errors logged into SQL Server error log from Last 4 days

Instance Last Recycle Information: Last recycle time and total uptime information

Tempdb usage: File location and space available information.

CPU Usage: CPU usage information

Memory Usage: Memory usage details

Performance Counters Data: Major performance counter values

Missing Backup Report: Database with no full backup from last 48 hours or without any backup

Connection Information: Host wise connection information

Log Space Usage Report: Log files usage information for all databases. It highlights the value with red color when the percentage use > 80.

Job Status Report: Reports current running jobs information

Blocking Report: Reports blocking information if any

Long running Transactions: Retrieves the long running transactions if any

Most important Note:

There is only one place (Memory Usage Capturing Buffer Pool information) where you need to comment / un-comment out based on the SQL Server version on which this script is running.


To get the SQL Server Health Check report, once the stored procedure got created, execute it by passing the required parameters:

EXEC usp_SQLhealthcheck_report

@MailProfile = ‘TEST_MAIL’ ,

@MailID = ‘DBA@org.com’,

@Server = ‘PROD_Server001’;

@MailProfile (Required): SQL Server database mail profile

@MailID (Required): Email recipient

@Server (Optional): If not mentioned then it takes @@SERVERNAME


SQL Server Health Check Script:

Download the script file: SQLServer_HealthCheck


Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , | 8 Comments

Leave a Reply

8 Comments on "SQL Server Health Check"

newest oldest most voted
Notify of

Super Brother….I follow you
Hope you remember me….:) Santosh


Nice Uday..good script..


Really appreciateyour efforts, Great Job Uday .

please add below in your script under perfmon counters –Batch request per sec DECLARE @BRPS BIGINT SELECT @BRPS=cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Batch Requests/sec%’ WAITFOR DELAY ‘000:00:10’ SELECT (cntr_value-@BRPS)/10.0 AS “Batch Requests/sec” FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Batch Requests/sec%’ also top 10 SQL statements in the cache for tuning SELECT top 10 text as “SQL Statement”, last_execution_time as “Last Execution Time”, (total_logical_reads+total_physical_reads+total_logical_writes)/execution_count as [Average IO], (total_worker_time/execution_count)/1000000.0 as [Average CPU Time (sec)], (total_elapsed_time/execution_count)/1000000.0 as [Average Elapsed Time (sec)], execution_count as “Execution Count”, qp.query_plan as “Query Plan” FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp order by total_elapsed_time/execution_count… Read more »