T-SQL scripts to generate database health reports

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:

Hostname, database edition, and version of the SQL Server
List of disk drives, total space, used space, and available space
List of the database hosted on the server
List of SQL Jobs
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:

Hostname
SQL Server version
SQL Server edition
SQL Server is clustered or not
SQL Server is in single-user mode

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

/*Variables for server property*/

declare @DatabaseServerInformation nvarchar(max)

declare @Hostname varchar(50) = (select convert(varchar(50),@@SERVERNAME))

declare @Version varchar(max) = (select convert(varchar(max),@@version))

declare @Edition varchar(50) = (select convert(varchar(50),SERVERPROPERTY('edition')))

declare @IsClusteredInstance varchar(50) =

(SELECT CASE SERVERPROPERTY ('IsClustered') WHEN 1 THEN 'Clustered Instance' WHEN 0 THEN 'Non Clustered instance' ELSE 'null' END)

declare @IsInstanceinSingleUserMode varchar(50) =

(SELECT CASE SERVERPROPERTY ('IsSingleUser') WHEN 1 THEN 'Single user' WHEN 0 THEN 'Multi user' ELSE 'null' END)

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

/*HTML Table with variables*/

set @DatabaseServerInformation=

'Server Info

<tr>

Host Name</td>

SQL Server version</td>

SQL Server edition</td>

Failover Clustered Instance</td>

Single User mode</td>

</tr>

<tr>

<td width="27%" height="27">'+@Hostname+'</td>

<td width="39%" height="27">' + @Version +'</td>

<td width="90%" height="27">'+@Edition+'</td>

<td width="90%" height="27">'+@IsClusteredInstance+'</td>

<td width="90%" height="27">'+@IsInstanceinSingleUserMode+'</td>

</tr>

</table>'

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

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:

SELECT DISTINCT volumes.logical_volume_name AS LogicalName,

volumes.volume_mount_point AS Drive,

CONVERT(INT,volumes.available_bytes/1024/1024/1024) AS FreeSpace,

CONVERT(INT,volumes.total_bytes/1024/1024/1024) AS TotalSpace,

CONVERT(INT,volumes.total_bytes/1024/1024/1024) - CONVERT(INT,volumes.available_bytes/1024/1024/1024) AS OccupiedSpace

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) volumes

Output:

The following HTML code is used to generate a table:

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="24%" border="1">

<tr>

Logical Name</td>

Volume </td>

Free Space (GB)</td>

Occupied Space (GB)</td>

Total Space (GB)</td>

</tr>

Disk Stats'

SELECT distinct

@DatabaseServerInformation = @DatabaseServerInformation +

'<tr><td>' + volumes.logical_volume_name +'</td>' +

'<td>' + volumes.volume_mount_point +'</td>' +

'<td>' + Convert(varchar,CONVERT(INT,volumes.available_bytes/1024/1024/1024)) +'</td>' +

'<td>' + Convert(varchar,CONVERT(INT,volumes.total_bytes/1024/1024/1024) - CONVERT(INT,volumes.available_bytes/1024/1024/1024)) +'</td>' +

'<td>' + Convert(varchar,CONVERT(INT,volumes.total_bytes/1024/1024/1024)) +'</td></tr>'

FROM

sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) volumes

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

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.

Database Name
Database Created date and time
Owner of the database
State of the database
Compatibility level
Recovery model
Size of the database

The query to populate the database is the following:

select a.database_id,

a.name,a.create_date,b.name,a.user_access_desc,a.state_desc,compatibility_level, recovery_model_desc, Sum((c.size*8)/1024) as DBSizeInMB

from sys.databases a inner join sys.server_principals b on a.owner_sid=b.sid inner join sys.master_files c on a.database_id=c.database_id

Where a.database_id>5

Group by a.name,a.create_date,b.name,a.user_access_desc,compatibility_level,a.state_desc, recovery_model_desc,a.database_id

Output:

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

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="24%" border="1">

<tr>

Database Name</td>

Created Date and Time </td>

Database created by</td>

Database Status</td>

Database access status</td>

Compatibility Level</td>

Recovary Model</td>

Database Size</td>

</tr>

Disk Stats'

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<tr><td>' + convert(varchar,a.name) +'</td>' +

'<td>' + convert(nvarchar,a.create_date) +'</td>' +

'<td>' + b.name +'</td>' +

'<td>' + a.state_desc +'</td>' +

'<td>' + a.user_access_desc +'</td>' +

'<td>' + convert(nvarchar,a.compatibility_level) +'</td>' +

'<td>' + a.recovery_model_desc +'</td>' +

'<td>' + convert(nvarchar,Sum((c.size*8)/1024)) +'</td></tr>'

FROM

sys.databases a inner join

sys.server_principals b on a.owner_sid =b.sid inner join

sys.master_files c

on a.database_id =c.database_id

Where a.database_id>5

Group by a.name,a.create_date,b.name,a.user_access_desc,compatibility_level, recovery_model_desc,a.database_id,a.state_desc

The HTML table looks like following image:

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.

Database Name
Backup Type
Backup Start Date
Backup end date
Server name
Backup size
Backup is taken by

The query to populate the database is the following:

create table #BackupInformation

(DatabaseName varchar(200), backup_type varchar(50), backupstartdate datetime, backupfinishdate datetime, username varchar(200), backupsize numeric(10,2), BackupUser varchar(250))

;with backup_information as

(

select

database_name,

backup_type =

case type

when 'D' then 'Full backup'

when 'I' then 'Differential backup'

when 'L' then 'Log backup'

else 'Other or copy only backup'

end ,

backup_start_date ,

backup_finish_date ,

user_name ,

server_name ,

compressed_backup_size ,

rownum =

row_number() over

(

partition by database_name, type

order by backup_finish_date desc

)

from msdb.dbo.backupset

)

insert into #BackupInformation

select

database_name [Database Name],

backup_type [Backup Type],

backup_start_date [Backup start date],

backup_finish_date [Backup finish date],

server_name [Server Name],

Convert(varchar,convert(numeric(10,2),compressed_backup_size/ 1024/1024)) [Backup size in MB],

user_name [Backup taken by]

from backup_information

where rownum = 1

order by database_name;

Output:

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

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="54%" border="1">

<tr>

Database Name</td>

Backup Type </td>

Backup Start Date</td>

Backup Finsh Date</td>

Server Name</td>

Backup Size in MB</td>

Backup generated by </td>

</tr>

Backup Details'

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<tr><td>' + convert(varchar,databasename) +'</td>' +

'<td>' + convert(nvarchar,backup_type) +'</td>' +

'<td>' + convert(varchar,backupstartdate,120) +'</td>' +

'<td>' + convert(varchar,backupfinishdate,120) +'</td>' +

'<td>' + servername +'</td>' +

'<td>' + Convert(varchar,convert(numeric(10,2),backupsize)) +'</td>' +

'<td>' + BackupUser +'</td></tr>'

from

#BackupInformation

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

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.

Server Name
SQL Job category
SQL Job Name
SQL job owner
Is Job Enabled
Date and time of next execution
Date and time of last execution
Last run status

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

create table #JobInformation

(Servername varchar(100), categoryname varchar(100),JobName varchar(500),

ownerID varchar(250),Enabled varchar(5),NextRunDate datetime, LastRunDate datetime, status varchar(50)

)

Insert into #JobInformation (Servername,categoryname,JobName,ownerID,Enabled,NextRunDate,LastRunDate,status)

SELECT

convert (varchar, SERVERPROPERTY('Servername')) AS ServerName

,categories.NAME AS CategoryName

,sqljobs.name

,SUSER_SNAME(sqljobs.owner_sid) AS OwnerID

,CASE sqljobs.enabled WHEN 1 THEN 'Yes' ELSE 'No'END AS Enabled

,CASE job_schedule.next_run_date

WHEN 0

THEN CONVERT(DATETIME, '1900/1/1')

ELSE CONVERT(DATETIME, CONVERT(CHAR(8), job_schedule.next_run_date, 112)

+ ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), job_schedule.next_run_time), 6), 5, 0, ':'), 3, 0, ':'))

END NextScheduledRunDate

,lastrunjobhistory.LastRunDate

,ISNULL(lastrunjobhistory.run_status_desc,'Unknown') AS run_status_desc

FROM msdb.dbo.sysjobs AS sqljobs

LEFT JOIN msdb.dbo.sysjobschedules AS job_schedule

ON sqljobs.job_id = job_schedule.job_id

LEFT JOIN msdb.dbo.sysschedules AS schedule

ON job_schedule.schedule_id = schedule.schedule_id

INNER JOIN msdb.dbo.syscategories categories

ON sqljobs.category_id = categories.category_id

LEFT OUTER JOIN (

SELECT Jobhistory.job_id

FROM msdb.dbo.sysjobhistory AS Jobhistory

WHERE Jobhistory.step_id = 0

GROUP BY Jobhistory.job_id

) AS jobhistory

ON jobhistory.job_id = sqljobs.job_id -- to get the average duration

LEFT OUTER JOIN

(

SELECT sysjobhist.job_id

,CASE sysjobhist.run_date

WHEN 0

THEN CONVERT(DATETIME, '1900/1/1')

ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112)

+ ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))

END AS LastRunDate

,sysjobhist.run_status

,CASE sysjobhist.run_status

WHEN 0

THEN 'Failed'

WHEN 1

THEN 'Succeeded'

WHEN 2

THEN 'Retry'

WHEN 3

THEN 'Canceled'

WHEN 4

THEN 'In Progress'

ELSE 'Unknown'

END AS run_status_desc

,sysjobhist.retries_attempted

,sysjobhist.step_id

,sysjobhist.step_name

,sysjobhist.run_duration AS RunTimeInSeconds

,sysjobhist.message

,ROW_NUMBER() OVER (

PARTITION BY sysjobhist.job_id ORDER BY CASE sysjobhist.run_date

WHEN 0

THEN CONVERT(DATETIME, '1900/1/1')

ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112)

+ ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))

END DESC

) AS RowOrder

FROM msdb.dbo.sysjobhistory AS sysjobhist

WHERE sysjobhist.step_id = 0 --to get just the job outcome and not all steps

)AS lastrunjobhistory

ON lastrunjobhistory.job_id = sqljobs.job_id -- to get the last run details

AND

lastrunjobhistory.RowOrder=1

Query Output:

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

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="54%" border="1">

<tr>

Server Name</td>

Job Category </td>

Job Name</td>

Job owner</td>

Enabled</td>

Next Run Date</td>

Last Run Date</td>

Status</td>

</tr>

Backup Details'

SELECT

@DatabaseServerInformation = @DatabaseServerInformation +

'<tr><td>' + ISNULL(convert(varchar,Servername),'-') +'</td>' +

'<td>' + ISNULL(convert(nvarchar,categoryname),'-') +'</td>' +

'<td>' + ISNULL(convert(varchar,JobName),'-') +'</td>' +

'<td>' + ISNULL(convert(varchar,ownerID),'-') +'</td>' +

'<td>' + ISNULL(Enabled,'') +'</td>' +

'<td>' + ISNULL(Convert(varchar,NextRunDate,120),'-') +'</td>' +

'<td>' + ISNULL(Convert(varchar,LastRunDate,120),'-') +'</td>' +

'<td>' + ISNULL(status,'-') +'</td></tr>'

from

#JobInformation

The HTML table looks like the following image:

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.

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.

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

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.

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.

1	exec msdb..sp_start_job [Database Summery Report - Daily]

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

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.

Author
Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022

Server and Instance status

Disk Status

Database information

Database backup information

Status of the SQL Jobs

Schedule the job

Summary

Related posts: