[sp_HealthCheck] – Get SQL Server Health Details
Hi Friends,
Ever wanted to check the heartbeat of SQL Server. It is a very common activity for a SQL Server user to check CPU, SQL Memory Utilization, Available Memory with OS, Currently executing queries, Space Utilization of Volumes/Drives, Usage of Data/Log Files, Blocking Tree, and any session running ROLLBACK/CHECKDB/BACKUP command.
We can get all this information by using my stored procedure [sp_HealthCheck]. Below is the latest code for the procedure:-
Following is a sample result for some of its output.
Friends! Please feel free to correct me by comments. If you like the article, do Like and share. Happy Coding 🙂
Credit:- The inspiration and some piece of the code of this procedure belong to my friends in United Health Group named Chandu & Anil.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Good,Pls share more….Thanks
Thanks Venkata. Try Space-Capacity-Automation as well.
Also, in case you find any bug/issue/enhancement to share, kindly log issue on github repository.
https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/issues
Hi Ajay,
Thanks for the SP it’s really good and will try in our environment.
Do u have automatic script for Db refresh from one server to another server.
Hi Sainath,
If required Db refresh is just one time activity, then you can use below powershell script to generate TSQL code for restore activity. You just need to provide SqlServer/Database & DestinationServer/Database details. Nothing else. It does not modify anything on your servers, so totally safe to try.
https://github.com/imajaydwivedi/SQLDBATools/blob/master/Cmdlets/__RefreshLogShipping__.ps1
In case you want to refresh dbs from Source Server to Target server on scheduled basis through a SQL Agent job, then yes, I did create some similar scripts for my Organization. But, I’ll have to check and script out those. That is not present on Github. So will share those if required.
Thanks & Regards,
Ajay Dwivedi
Hi Ajay,
Is there any way to get checklist output in html format by scheduling in sql agent job…plz help
No Naveen. This proc was meant for On-spot analysis of running session, and other small details.
I think, you should try FirstResponderKit by BrentOzarUnlimited sp_BlitzFirst in case you wish to capture baselines.