SQL Server Health Check

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.

Executing:

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"

Notify of
avatar
Sort by:   newest | oldest | most voted
Santosh
Guest

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

Balakrishna.B
Guest

Nice Uday..good script..

Arvind
Guest

Really appreciateyour efforts, Great Job Uday .

latha
Guest
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 »
wpDiscuz