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 Uncategorized | Tagged , , , , , , , , , , , | 6 Comments

Leave a Reply

6 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 .

wpDiscuz