SQL Server Health Check HTML Report is the improved version of the old report and it can help you to quickly monitor the health of a given SQL Server instance. This report fetches and process the data from system tables and dynamic management views. It reports the crucial factors that showcase the current database instance performance. It’s been written using T-SQL script. We can run it on any SQL Server instance starting from 2008 R2 to 2019. Save the result as .HTML format and the same can be viewed through any web browser. We neither tested it on Azure nor RDS instances, thereof we may need to do some code changes for cloud instances. Script download is available at the end of this post.
Report Parts
Report contains below parts:
SERVER DETAILS
INSTANCE INFORMATION
DISK SPACE USAGE
TEMPDB USAGE
CPU USAGE
MEMORY USAGE
PERFORMANCE COUNTER DATA
MISSING BACKUP REPORT
DATABASE CONNECTIONS
DATABASE LOG SPACE USAGE
SQL AGENT JOB STATUS
BLOCKING PROCESS INFORMATION
LONG RUNNING TRANSACTIONS
TOP 20 TABLES
INDEX ANALYSIS
SQL ERRORLOG
Execution Procedure
Create the stored procedure “[usp_SQLhealthcheck_report]”
Execute the procedure Ex: EXECUTE [usp_SQLhealthcheck_report];
Pass Mail profile and Email details @MailProfile, @MailID. Not mandatory
Save Result As: Save output as .HTML on your local
Make sure that the saved report is on the same folder where Images folder exists. So that we can see the LOGO at right corner.
Output Format
Direct HTML report
Send email with HTML as an attachment
Critical Performance Counter Data
This section captures the critical performance counters from management views. Some of those counters plays the vital role in system performance evaluation.
Page Life Expectancy (PLE): How many seconds a page is being available in Memory.
Buffer Cache Hit Ratio (BHR): What is the percentage of database calls reaching Buffer Cache instead of Disk storage.
Grants Pending: If any Memory requests pending from OS
Memory Low Alerts: 0 indicates no such alerts registered.
Recommendation for Continues Monitoring
If we want to design performance trends, then I would recommend the below parameters for monitoring on continues basis:
CPU Usage
SQL Process Utilization
System Idle
Memory Usage
Total_Physical_Memory_MB
Physical_memory_in_use_GB
Performance Counter Data
Page Life Expectancy
Buffer Cache Hit Ratio
Memory Grants Pending
Memory Grants Outstanding
Process_Physical_Memory_Low
Process_Virtual_Memory_Low
Blocking Process Information
Sample Screenshots from HTML Report:
Create procedure on the required database:
Execute the procedure. I don’t need to send email rather just need a HTML report
Store the output as in HTML report
Create Images folder and create the required company logo and name it as Logo_Image.jpg
Open the report in a web browser, Sample Screenshots
Summary:
SQL Server Health Check HTML Report is useful to quickly get the performance snapshot of a database instance. After going through the overall system parameters, except the minor issues we can clearly state that the both instances are optimally performing. Index maintenance and the database log file maintenance are the critical activities to be considered. Please make note that the HC report tool is not reporting the SQL code issues and its scope is restricted to the database system level performance.
Note:
Always test the script on Pre-Production environment
I just executed it on my machine and it’s generating a HTML report just like a magic.
Such an altruistic attitude. We’ll get paid, if we provide the same report to any of our clients.
You are awesome sir. It will be very helpful in handling incidents.
We are already using the old script that you provided earlier. This looks amazing sir. I am going to update my old one with the latest code. I think I need your help in displaying my company logo on report. Loved the HTML format. Sir, you are my SQL Hero 🙂
Sriram Kumar
3 years ago
It’s really helpful. Thank you so much Uday.
I need to have some customer specific datafeed status report on this healthcheck. Can you please help me out in adding that to the base code?
In the last, you will find “Download” image. Please click on it to get the download.
Sreeni Reddy
3 years ago
Your script is a piece of cake. Really helpful to all.
If possible please include below ones too.
*** List of OFFLINE/SUSPECT databases on all SQL Servers ***
*** Newly created Logins during last 24 hours ***
*** Newly created databases during last 24 hours ***
I’ll see if we can add those details. You can add / modify the code as per your requirement.
All the best.
Uday Arumilli
soni
3 years ago
Hi, thanks for the good script, but its not include/check the database inside the server/instance. pls can you tell me how to also check the database inside the instance.
Hi Sir,
What an excellent report in HTML format with details and it is very much useful to monitor SQL server health.
Thanks a lot for providing this script.
However, when I am trying to modify or alter the SP, getting below error i.e text is encrypted. Please help me on this.
Property TextHeader is not available for StoredProcedure ‘[dbo].[usp_SQLhealthcheck_report]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.
Suresh, in the script in after CREATE PROCEDURE you find WITH ENCRYPTION, please remove it and execute the procedure. So you would be able to get the code from SP_HELPTEXT.
Many thanks for your response.
Now I am able to modify.
Vedprakash Sharma
3 years ago
Hi All,
How can we send this report in email body.
Sivakrishna
3 years ago
Could you help with including database parameters as well? Also, how to send this report by email.
Praveen
3 years ago
Hi Sir,
The script is nice and helpful to do the health checks quickly ….. can we run the script outside of the server and if we pass the SQL instance it should get the results for that server ? is it possible ?
When i tested the script of passing the different SQL server instance names, the health report is generating only for local server not for remote servers.
The script is intended to run on single instance. Apparently, to get stats from remote servers we may need to get use of PS scripts and loop through the required server instances.
Thanks
Uday ARumilli
peiris
3 years ago
You have not noted the way to create stored procedure. Please guide for that
Standard visits recorded here are the simplest strategy to value your vitality, which is the reason why I am heading off to the site regular, looking for new, fascinating information. Many, bless your heart!
I think this is an extraordinary article. You make this information interesting and secures. You give perusers a lot to consider and I esteem such a forming.
<a href=”https://360digitmg.com/hrdf-scheme”>hrdf claimable training</a>
A decent blog consistently concocts new and energizing data and keeping in mind that perusing I have feel that this blog is truly have each one of those quality that qualify a blog to be a one.
<a href=”https://360digitmg.com/course/data-analytics-using-python-r”>360DigiTMG data analytics course</a>
pmp certification in malaysia
3 years ago
I like this post,And I figure that they making some incredible memories to scrutinize this post,they may take a good site to make an information,thanks for sharing it to me
<a href=”https://360digitmg.com/course/project-management-professional-pmp”>pmp </a>certification in malaysia
Set aside my effort to peruse all the remarks, however I truly delighted in the article. It’s consistently pleasant when you can not exclusively be educated, yet in addition, engaged!<a href=”https://360digitmg.com/what-is-the-difference-between-analysis-and-analytics”>difference between analysis and analytics </a>
sreeharsha
3 years ago
I have been following your site sir and mostly i used your script to dig into the issues. Now health check scripts its amazing.
Saying thank you to you is just small word.
I am glad you found it useful. Thanks for the feedback 🙂
Regards
Uday Arumilli
hrdf contribution
3 years ago
This is a great motivational article. In fact, I am happy with your good work. They publish very supportive data, really. Continue. Continue blogging. Hope you explore your next post
<a href=”https://360digitmg.com/hrdf-training”>hrdf contribution</a>
Brett
3 years ago
Wow, this is awesome stuff. Just one question, I am using the email parameters which work perfectly. I am just wondering if i can embed a logo in the emails somehow.
Brett, I think we can explore that option. Since we are playing with HTML, indeed we can embed a web based LOGO url. I never tried it but will give a try on it.
Hi Uday Sir,
How we can share this health check report on email and is the any specific permission/port is required. please suggest or please share the script
Sravan
3 years ago
The script executed successfully on few servers but when I try to run on similar other 2 servers getting below error, please help me on that. Msg 137, Level 15, State 1, Procedure SQL_DB_healthcheck_report, Line 701 Must declare the scalar variable “@OriServer”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 702 Must declare the scalar variable “@SERVERNAME”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 737 Must declare the scalar variable “@version”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 1087 Must declare the scalar variable “@SERVERNAME”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 1275… Read more »
Vishal
3 years ago
There is no option to save report on local disk we have to manually save store procedure output in HTML format.
Jagan
3 years ago
Can you please help on to get all the drives with total size, free size and percentage_freespace..
Checked many blogs.. didnt get
Jegan
3 years ago
It’s really helpful. Thank you so much Uday.
yash
3 years ago
Many Many Thank you for this script, I just want to know that is this script will work for DB server on which AG is configured, because backup scenario is little bit different in AGs
Rajesh
3 years ago
Index Analysis & Top 20 Tables data has not been fetching from all DBs hosted on an instance of SQL Server. It is fetching data from DB where stored procedure is resides under and is working in the context of that DB alone.
Great work Uday!!
Sinu
3 years ago
I have scheduled in the job but getting failed
Date 3/16/2021 3:21:52 PM
Log Job History (checklist)
Step ID 1
Server SERVER2
Job Name checklist
Step Name EXECUTE master.dbo.usp_SQLhealthcheck_report
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The step is improperly defined (it needs a valid command) and so could not be run. The step failed.
Sourav Misra
2 years ago
How to get Top 20 tables and Index Fragmentation Details for all databases.
Sree
2 years ago
any changes needed to make this work on azure sql paas (elastic pool)
vamsi
2 years ago
Really thanks for the script. It helped a lot.
only problem for me is the Index fragmentation details. It seems not pulling for me on user databases. when i try to run the script in a database it works but when i run the SP it shows blank. Any help to fix this is really appreciated.
Ananda
2 years ago
Thanks Udaya for sahring HC script.
this is really excellent script for using database health checks on daily basis. working script awesome without any glitch.
Dilip Vishwakarma
2 years ago
Hello,
For multiple servers, I will be getting mails for each servers or we can configure in a way that only one mail will be recevied for approx. 60 Servers, Please suggest
mohamed anas
2 years ago
Hi Sir,
Amazing script , I’m late to noticed this cool stuff. Please guide me how to configure this script via email.
Thanks,
Anas
Last edited 2 years ago by mohamed anas
Vivek
2 years ago
Hi Uday,
Lovely set up and report Output. But not sure why Index Defrag is not giving any output?
Also; how can we run it for multiple servers in one go? it seems currently we have run it on every server individually.
Anji
2 years ago
Thank you for sharing good script.
can you please add below logic if possible?
need index analysis on all databases
top huge tables on all databases
save the output file directly to a folder with .HTML
Thanks
Anji
sri
2 years ago
most of the report doesnt work on azure sql database (paas with elastic pool) any insight?
Suma
1 year ago
excellent script and looks so detailed. Thanks a lot. It made so easy . But request you to share an updated script with background color that changes according to the result. For suppose, if the disk space is less and need to take action then it should be warned with a background color red and incase more space is available then should be shaded with background color green. Also, I’m bit confused why the “Index Analysis” & “Top 20 Huge Tables” were not resulting in any of the server. Is there any modification to be done in the script. ?… Read more »
SeethaRamaiah T
1 year ago
Nice script bro… it’s helped alot….. total server and used memory details are not getting
Thanks for your report. It is working fine in sql server 2012. But, It is not displaying any information for the following columns in sql 2019 server. I tried in one of the databases which has lot of I/O and active connections for many databases.
SQL Agent Job StatusBlocking Process InformationLong Running TransactionsTop 20 Huge TablesIndex AnalysisSQL ErrorLog
srini
1 year ago
Hi Uday
Please add DB Properties and drive related total space and free space with percentage in script.
Thank you.
I just executed it on my machine and it’s generating a HTML report just like a magic.
Such an altruistic attitude. We’ll get paid, if we provide the same report to any of our clients.
You are awesome sir. It will be very helpful in handling incidents.
Thank you so much sir.
–Manasa
Please i need the script
We are already using the old script that you provided earlier. This looks amazing sir. I am going to update my old one with the latest code. I think I need your help in displaying my company logo on report. Loved the HTML format. Sir, you are my SQL Hero 🙂
It’s really helpful. Thank you so much Uday.
I need to have some customer specific datafeed status report on this healthcheck. Can you please help me out in adding that to the base code?
Thanks again for sharing the wonderful script.
Sriram
where is the script?
Thomas,
In the last, you will find “Download” image. Please click on it to get the download.
Your script is a piece of cake. Really helpful to all.
If possible please include below ones too.
*** List of OFFLINE/SUSPECT databases on all SQL Servers ***
*** Newly created Logins during last 24 hours ***
*** Newly created databases during last 24 hours ***
Thanks Sreeni.
I’ll see if we can add those details. You can add / modify the code as per your requirement.
All the best.
Uday Arumilli
Hi, thanks for the good script, but its not include/check the database inside the server/instance. pls can you tell me how to also check the database inside the instance.
Thanks
Yes, It’s helpful if it include database name
Hi Sir,
What an excellent report in HTML format with details and it is very much useful to monitor SQL server health.
Thanks a lot for providing this script.
However, when I am trying to modify or alter the SP, getting below error i.e text is encrypted. Please help me on this.
Property TextHeader is not available for StoredProcedure ‘[dbo].[usp_SQLhealthcheck_report]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.
Suresh, in the script in after CREATE PROCEDURE you find WITH ENCRYPTION, please remove it and execute the procedure. So you would be able to get the code from SP_HELPTEXT.
Thanks
Uday Arumilli
Many thanks for your response.
Now I am able to modify.
Hi All,
How can we send this report in email body.
Could you help with including database parameters as well? Also, how to send this report by email.
Hi Sir,
The script is nice and helpful to do the health checks quickly ….. can we run the script outside of the server and if we pass the SQL instance it should get the results for that server ? is it possible ?
When i tested the script of passing the different SQL server instance names, the health report is generating only for local server not for remote servers.
Praveen,
The script is intended to run on single instance. Apparently, to get stats from remote servers we may need to get use of PS scripts and loop through the required server instances.
Thanks
Uday ARumilli
You have not noted the way to create stored procedure. Please guide for that
Standard visits recorded here are the simplest strategy to value your vitality, which is the reason why I am heading off to the site regular, looking for new, fascinating information. Many, bless your heart!
PMP
I think this is an extraordinary article. You make this information interesting and secures. You give perusers a lot to consider and I esteem such a forming.
<a href=”https://360digitmg.com/hrdf-scheme”>hrdf claimable training</a>
A decent blog consistently concocts new and energizing data and keeping in mind that perusing I have feel that this blog is truly have each one of those quality that qualify a blog to be a one.
<a href=”https://360digitmg.com/course/data-analytics-using-python-r”>360DigiTMG data analytics course</a>
I like this post,And I figure that they making some incredible memories to scrutinize this post,they may take a good site to make an information,thanks for sharing it to me
<a href=”https://360digitmg.com/course/project-management-professional-pmp”>pmp </a>certification in malaysia
Set aside my effort to peruse all the remarks, however I truly delighted in the article. It’s consistently pleasant when you can not exclusively be educated, yet in addition, engaged!<a href=”https://360digitmg.com/what-is-the-difference-between-analysis-and-analytics”>difference between analysis and analytics </a>
I have been following your site sir and mostly i used your script to dig into the issues. Now health check scripts its amazing.
Saying thank you to you is just small word.
Harsha,
I am glad you found it useful. Thanks for the feedback 🙂
Regards
Uday Arumilli
This is a great motivational article. In fact, I am happy with your good work. They publish very supportive data, really. Continue. Continue blogging. Hope you explore your next post
<a href=”https://360digitmg.com/hrdf-training”>hrdf contribution</a>
Wow, this is awesome stuff. Just one question, I am using the email parameters which work perfectly. I am just wondering if i can embed a logo in the emails somehow.
Brett, I think we can explore that option. Since we are playing with HTML, indeed we can embed a web based LOGO url. I never tried it but will give a try on it.
Thanks Breet.
Hi Uday Sir,
How we can share this health check report on email and is the any specific permission/port is required. please suggest or please share the script
The script executed successfully on few servers but when I try to run on similar other 2 servers getting below error, please help me on that. Msg 137, Level 15, State 1, Procedure SQL_DB_healthcheck_report, Line 701 Must declare the scalar variable “@OriServer”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 702 Must declare the scalar variable “@SERVERNAME”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 737 Must declare the scalar variable “@version”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 1087 Must declare the scalar variable “@SERVERNAME”. Msg 137, Level 15, State 2, Procedure SQL_DB_healthcheck_report, Line 1275… Read more »
There is no option to save report on local disk we have to manually save store procedure output in HTML format.
Can you please help on to get all the drives with total size, free size and percentage_freespace..
Checked many blogs.. didnt get
It’s really helpful. Thank you so much Uday.
Many Many Thank you for this script, I just want to know that is this script will work for DB server on which AG is configured, because backup scenario is little bit different in AGs
Index Analysis & Top 20 Tables data has not been fetching from all DBs hosted on an instance of SQL Server. It is fetching data from DB where stored procedure is resides under and is working in the context of that DB alone.
Great work Uday!!
I have scheduled in the job but getting failed
Date 3/16/2021 3:21:52 PM
Log Job History (checklist)
Step ID 1
Server SERVER2
Job Name checklist
Step Name EXECUTE master.dbo.usp_SQLhealthcheck_report
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The step is improperly defined (it needs a valid command) and so could not be run. The step failed.
How to get Top 20 tables and Index Fragmentation Details for all databases.
any changes needed to make this work on azure sql paas (elastic pool)
Really thanks for the script. It helped a lot.
only problem for me is the Index fragmentation details. It seems not pulling for me on user databases. when i try to run the script in a database it works but when i run the SP it shows blank. Any help to fix this is really appreciated.
Thanks Udaya for sahring HC script.
this is really excellent script for using database health checks on daily basis. working script awesome without any glitch.
Hello,
For multiple servers, I will be getting mails for each servers or we can configure in a way that only one mail will be recevied for approx. 60 Servers, Please suggest
Hi Sir,
Amazing script , I’m late to noticed this cool stuff. Please guide me how to configure this script via email.
Thanks,
Anas
Hi Uday,
Lovely set up and report Output. But not sure why Index Defrag is not giving any output?
Also; how can we run it for multiple servers in one go? it seems currently we have run it on every server individually.
Thank you for sharing good script.
can you please add below logic if possible?
Thanks
Anji
most of the report doesnt work on azure sql database (paas with elastic pool) any insight?
excellent script and looks so detailed. Thanks a lot. It made so easy . But request you to share an updated script with background color that changes according to the result. For suppose, if the disk space is less and need to take action then it should be warned with a background color red and incase more space is available then should be shaded with background color green. Also, I’m bit confused why the “Index Analysis” & “Top 20 Huge Tables” were not resulting in any of the server. Is there any modification to be done in the script. ?… Read more »
Nice script bro… it’s helped alot….. total server and used memory details are not getting
Thanks for your report. It is working fine in sql server 2012. But, It is not displaying any information for the following columns in sql 2019 server. I tried in one of the databases which has lot of I/O and active connections for many databases.
SQL Agent Job StatusBlocking Process InformationLong Running TransactionsTop 20 Huge TablesIndex AnalysisSQL ErrorLog
Hi Uday
Please add DB Properties and drive related total space and free space with percentage in script.
Thank you.