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