Nisarg Upadhyay
Job schedule

T-SQL scripts to generate database health reports

October 21, 2020 by

In this article, I am going to show how we can automate a database daily health report using T-SQL scripts. For a senior database administrator, it is always important to review the health of the database server. When a DBA manages many database servers, it becomes difficult to visit every server and review the status of databases, SQL jobs, and backups. To ease the monitoring, I have developed a T-SQL script that populates the following information:

  1. Hostname, database edition, and version of the SQL Server
  2. List of disk drives, total space, used space, and available space
  3. List of the database hosted on the server
  4. List of SQL Jobs
  5. List of most recent backups of all databases

Once the T-SQL script is explained, I will demonstrate how we can schedule it using the SQL Server Agent.

In the script, I have declared a variable named @DatabaseServerInformation. The data type of the variable is varchar(max). In the variable, we will store the output of the query in the HTML formatted table.

Server and Instance status

In the first HTML table, we will display the following server information:

  1. Hostname
  2. SQL Server version
  3. SQL Server edition
  4. SQL Server is clustered or not
  5. SQL Server is in single-user mode

The T-SQL code to populate the server and instance information is the following:

The HTML code to create the table is as shown below:

The HTML table generated by above code looks like the following image:

Email Server details

Disk Status

The second HTML table contains the information of the disk is placed. The query to populate the disk volume information is the following:

Output:

Server storage details

The following HTML code is used to generate a table:

The table generated by the code looks like the following image:

Disk state

Database information

The third section of the code shows the list of the databases hosted on the server. The following information of the database is included.

  1. Database Name
  2. Database Created date and time
  3. Owner of the database
  4. State of the database
  5. Compatibility level
  6. Recovery model
  7. Size of the database

The query to populate the database is the following:

Output:

Database details query

Following is the code of the HTML table to display the query output:

The HTML table looks like following image:

Database Status

Database backup information

The third section of the code shows the most recent full backup, differential backup, and Log backup of the databases hosted on the server. The following information of the backup is included.

  1. Database Name
  2. Backup Type
  3. Backup Start Date
  4. Backup end date
  5. Server name
  6. Backup size
  7. Backup is taken by

The query to populate the database is the following:

Output:

Database backup query

Following is the code of the HTML table to display the query output:

The table generated by HTML code looks like the following image:

Backup Details

Status of the SQL Jobs

The third section of the code shows the list of the SQL Server jobs created on the database instance. The T-SQL code includes the following set of information.

  1. Server Name
  2. SQL Job category
  3. SQL Job Name
  4. SQL job owner
  5. Is Job Enabled
  6. Date and time of next execution
  7. Date and time of last execution
  8. Last run status

The query to populate the status of the SQL Jobs is the following:

Query Output:

SQL Job detail query

Following is the code of the HTML table to display the status of the SQL Jobs.

The HTML table looks like the following image:

Job status

  • Note: The code is too lengthy, so you can download the script for the article here

Schedule the job

I have created a stored procedure named getdailydummeryreport. The script contains the above code, and the output will be sent to the DBA Support team. To send the output, we are using SQL Server Database Mail. If you are using the SQL Server Express edition, then you can use the Windows task Scheduler to send the output of the procedure. To create the SQL job, Open SQL Server Management Studio Expand SQL Server Agent Right-click on Jobs Select New Job. On the New Job dialog box, provide the desired name of the Job and click ok Steps Click on New.

New Job

On the New job step dialog box, provide the desired name of the job step in Step Name text box, select Transact-SQL Script from the Type dialog box, and enter the following code in the command textbox. Click OK to save and close the job step.

New Job Step

To schedule the job, click on Schedule and click New.

New Job Schedule

On the New Schedule dialog box, configure the frequency and the date-time according to the business requirement. I want to run this report at 7 AM, so the schedule is configured accordingly.

Job schedule

Click OK to save and close the schedule. Click OK to save the SQL Job and close the dialog box. To test the SQL job, you can run the following T-SQL command.

Once the job is executed successfully, you will receive the email, as shown below.

Database report

You can tweak the HTML code to change the format of the HTML tables.

Summary

In this article, I have shown a T-SQL Script that is used to generate a database health report and how it can be scheduled using the SQL Server agent job. In my future articles, I will show more scripts that can help perform daily DBA tasks to increase productivity.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
5,575 Views