Posted inExecution Plan Health Check

[sp_HealthCheck] – Get SQL Server Health Details

6 Comments
[sp_HealthCheck] – Get SQL Server Health Details
Spread the love

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

sp_HealthCheckDownload

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.

          
Tags:

6 Comments

  1. venkata
    Reply
    March 31, 2018,

    Good,Pls share more….Thanks

  2. sainath
    Reply
    March 6, 2019,

    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.

    • Ajay Dwivedi
      Reply
      March 6, 2019,

      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

  3. naveen thota
    Reply
    March 8, 2019,

    Hi Ajay,

    Is there any way to get checklist output in html format by scheduling in sql agent job…plz help

    • Ajay Dwivedi
      Reply
      March 8, 2019,

      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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.