Customized SSRS report to monitor server and database status

By: Scott Murray | Comments (6) | Related: > Monitoring

Problem

How can I generate a Server and Database Status / Properties report using SQL Server Reporting Services (SSRS)?

Solution

SSRS is a great tool for providing administrative reporting needs. One way we can use SSRS is to generate a report that displays various important server and database properties status report. Items would include: server/instance version, level, minimum memory, maximum memory, database size, and objects which have recently been changed. This tip is a continuance to our Administrative Intelligence theme and includes tips such as SSRS report for SQL Server Database Backup Status and SQL Server Analysis Services Cube Processing Status Report. Koen Verbeeck's tip, Custom SSMS report to show SQL Server tables per filegroup, is an excellent additional example of the power of SSRS in your reporting and administration toolbox.

To help generate our report data, we will use the 2014 versions of the AdventureWorks regular and data warehouse databases which are available on Codeplex: https://www.microsoft.com/en-us/download/details.aspx?id=49502. Once these sample databases are downloaded and installed, we will query the various needed properties. Finally, to generate our report, we will use the SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

SSRS Server and Database Properties Report

Our first step is to retrieve the needed data at the server level. Fortunately, several system table / views exist to provide us all the data needed to produce a report. These tables include: sys.dm_os_sys_info and sys.configurations.


---Get Server Properties
 
DECLARE @ServiceStartDateTime AS DATETIME

SELECT @ServiceStartDateTime=sqlserver_start_time  -- for last restart data 
FROM 
sys.dm_os_sys_info

SELECT 
@@SERVERNAME AS ServerName
,@ServiceStartDateTime AS SQLServerServiceLastRestartDateTime
,REPLACE (CONVERT (NVARCHAR (128), SERVERPROPERTY ('Edition')),' Edition','') AS SQLServerEdition
,SERVERPROPERTY ('ProductVersion') AS SQLServerVersion
,SERVERPROPERTY ('ProductLevel') AS SQLServerProductLevel
,SYSCONF.configuration_id
,SYSCONF.name AS PropertyName
,SYSCONF.description AS PropertyDescription
,CASE 
WHEN SYSCONF.configuration_id IN ( '1543', '1544') THEN CAST(SYSCONF.value_in_use AS decimal (18,4))/1024/1024
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.value_in_use = 1 THEN 'Y'
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.value_in_use = 0 THEN 'N'
ELSE SYSCONF.value_in_use END AS CurrentValue
,CASE 
WHEN SYSCONF.configuration_id IN ( '1543', '1544') THEN CAST(SYSCONF.minimum AS decimal (18,4))/1024/1024 
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.minimum = 1 THEN 'Y'
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.minimum = 0 THEN 'N'
ELSE SYSCONF.minimum END AS MinValueforProperty
,CASE WHEN SYSCONF.configuration_id IN ( '1543', '1544') THEN CAST(SYSCONF.maximum AS decimal (18,4))/1024/1024 
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.maximum = 1 THEN 'Y'
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.maximum = 0 THEN 'N'
ELSE SYSCONF.maximum END AS MaxValueforProperty
FROM 
sys.configurations AS SYSCONF
WHERE 
SYSCONF.configuration_id IN
(
'117'--remote access
,'505' --network packet size (B)
,'1543' --min server memory (MB)
,'1544' --max server memory (MB)
,'1581' --optimize for ad hoc workloads
,'16391' --xp_cmdshell
);

The above query is mostly self-explanatory, but I would like to highlight a few points. The sys.dm_os_sys_info view retrieves our last SQL Server service start time; this value provides a valuable point in time as many items are truncated or cleared (and thus "start over" ) when the service is restarted or, of course, if the server is rebooted which in turn forces a restart. For instance, when the service is restarted, most query and index performance stats in the various data management views and tables are cleared and reset. We also retrieve the Edition, Version, and Level; certainly it is nice to know if we are working with SQL Server 2008R2 Standard Edition SP3 or SQL 2014 Enterprise base. Finally we finish off the query with several key configuration properties including:

Allow Remote Access
Network Packet Size
Minimum Server Memory
Maximum Server Memory
Allow XP_cmdshell
Optimize for ad hoc workloads

Certainly you can explore sys.configuration to see if there are other property settings that you would like added to this list. Adding them to the query would allow them to show up on the report automatically.

Next we need to begin to create our Status Report by opening SQL Server Data Tools-BI edition (SSDT-BI). If you need help creating a SSRS report, I would suggest starting with this tutorial: https://www.mssqltips.com/sqlservertutorial/222/sql-server-reporting-services-ssrs-tutorial/. The initial report includes a title and then two sections; one section for the high level server details and then a second section for the property values selected.

Additionally, we define one parameter. This parameter is a free form field where you will enter the server name for which you would like the backup status report. This parameter could easily be modified, for instance, to retrieve a list of instance names from a table that contains a list of all your servers. For this tip, I was trying to make the report somewhat flexible and easy to run against any server.

Now we have a working report with some very basic, but informative set of traits about our server. Additionally, with the server parameter, the report can be direct to most any server (note I tested on SQL 2014 and SQL 2008R2).

Our next set of data to retrieve focuses on database settings and backup details for the server in question.

--Get List of Last Full Backup or No Backup
;WITH LAST_FULL_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='D' OR BUSETS.[type] IS NULL
GROUP BY 
SYSDBLIST.name
)

,
---Get List of Last Differential Backup
LAST_DIFFERENTIAL_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='I' 
GROUP BY 
SYSDBLIST.name
)

,
---Get List of Last Log Backup

LOG_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
BUSETS.backup_finish_date AS Backup_Finish_DateTime,
ROUND(((BUSETS.backup_size/1024)/1024),2) AS Backup_Size_MB,
ROW_NUMBER() OVER(Partition by SYSDBLIST.name ORDER BY BUSETS.backup_finish_date DESC) AS RevOrderBuDate
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='L' 
)
,
LAST_LOG_BACKUP_LIST
AS
(SELECT
Name,
Backup_Finish_DateTime AS Last_Backup_Finish_DateTime,
Backup_Size_MB AS Log_Backup_Size_MB
FROM
LOG_BACKUP_LIST
WHERE
RevOrderBuDate=1
)

SELECT SERVERPROPERTY('Servername') AS ServerName,
SYSDBLIST.name AS Database_Name,
SYSDBLIST.Compatibility_level,
SYSDBLIST.Recovery_model,
SYSDBLIST.Recovery_model_desc,
BUSETS.database_creation_date AS Database_Create_Date,
CASE WHEN BUSETS.backup_set_id IS NULL THEN 'NO Backup' ELSE 'Backup Complete' END AS Backup_Completed,
BUSETS.backup_start_date AS Backup_Start_DateTime,
BUSETS.backup_finish_date AS Backup_Finish_DateTime,
DATEDIFF(MINUTE, BUSETS.backup_start_date, BUSETS.backup_finish_date) AS Duration_Min,
(DATEDIFF(DAY,BUSETS.backup_finish_date,GETDATE())) AS Days_Since_Last_Backup,

LASTDIFFBACKUP.Last_Backup_Finish_DateTime AS Last_Differential_Finish_DateTime,
(DATEDIFF(DAY,LASTDIFFBACKUP.Last_Backup_Finish_DateTime ,GETDATE())) AS Days_Since_Last_Differential_Backup,
LASTLOGBACKUP.Last_Backup_Finish_DateTime AS Last_Log_Finish_DateTime,
(DATEDIFF(DAY,LASTLOGBACKUP.Last_Backup_Finish_DateTime,GETDATE()))AS Days_Since_Last_Log_Backup,
LASTLOGBACKUP.Log_Backup_Size_MB AS LOG_Backup_Size_MB,
CASE 
WHEN BUSETS.[type] = 'D' THEN 'Full Backup' 
WHEN BUSETS.[type] = 'I' THEN 'Differential Database' 
WHEN BUSETS.[type] = 'L' THEN 'Log' 
WHEN BUSETS.[type] = 'F' THEN 'File/Filegroup'
WHEN BUSETS.[type] = 'G' THEN 'Differential File'
WHEN BUSETS.[type] = 'P' THEN 'Partial'  
WHEN BUSETS.[type] = 'Q'THEN 'Differential partial' 
END AS Backup_Type,
ROUND(((BUSETS.backup_size/1024)/1024),2) AS Backup_Size_MB,
ROUND(((BUSETS.compressed_backup_size/1024)/1024),2) AS Backup_Size_Compressed_MB,
BUMEDFAM.Device_type,
BUMEDFAM.Physical_device_name,
BUMEDFAM.Logical_device_name,

FROM 
MASTER.sys.databases AS SYSDBLIST
INNER JOIN
sys.master_files AS MASTFILE
ON
SYSDBLIST.database_id=MASTFILE.database_id
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
LEFT OUTER JOIN
msdb.dbo.backupmediafamily  AS BUMEDFAM
ON
BUSETS.media_set_id = BUMEDFAM.media_set_id

INNER JOIN
LAST_FULL_BACKUP_LIST AS LASTBACKUP
ON
SYSDBLIST.Name=LASTBACKUP.Name
AND
ISNULL(BUSETS.backup_finish_date,'01/01/1900') = ISNULL(LASTBACKUP. Last_Backup_Finish_DateTime,'01/01/1900')

LEFT OUTER JOIN
LAST_DIFFERENTIAL_BACKUP_LIST AS LASTDIFFBACKUP
ON
SYSDBLIST.Name=LASTDIFFBACKUP.Name

LEFT OUTER JOIN
LAST_LOG_BACKUP_LIST AS LASTLOGBACKUP
ON
SYSDBLIST.Name=LASTLOGBACKUP.Name
WHERE
SYSDBLIST.name<>'TempDB'

The above query purposely gets more data than was needed for the backup portion of the report. The query requests details about when the last backup was completed, how big the backup was, how long the backup took to complete, what type of backup was completed, and where the physical location of the database backup was stored. We are also differentiating between data file backups and log file backups.

The second page of Server Status Report now provides us with a breakout of all the database backups for our server along with the date, size, duration, and location (device) for our last backup for all databases on the server.

Now drilling into details at the database level, our next query explores the size and location of our database files along with several key properties about the databases. The property list include:

Compatibility Level
Current State
Auto Close On
Auto Create Stats On
Auto Shrink On
Auto Update Stats On
Read Only

Of course, the report template can be modified to include other metrics as you see fit.

SELECT 
SERVERPROPERTY('Servername') AS ServerName,
SYSDBLIST.name AS Database_Name,
SYSDBLIST.Compatibility_level,
CASE WHEN SYSDBLIST.is_auto_close_on = '0' THEN 'N' ELSE 'Y' END AS is_auto_close_on,
CASE WHEN SYSDBLIST.is_auto_create_stats_on = '0' THEN 'N' ELSE 'Y' END AS is_auto_create_stats_on,
CASE WHEN SYSDBLIST.is_auto_shrink_on = '0' THEN 'N' ELSE 'Y' END AS is_auto_shrink_on,
CASE WHEN SYSDBLIST.is_auto_update_stats_on = '0' THEN 'N' ELSE 'Y' END AS is_auto_update_stats_on,
CASE WHEN SYSDBLIST.is_read_only = '0' THEN 'N' ELSE 'Y' END AS is_read_only ,
SYSDBLIST.state,
SYSDBLIST.state_desc,
CASE WHEN MASTFILE.type_desc='ROWS' THEN 'DATABASE' ELSE MASTFILE.type_desc END AS File_Type,
MASTFILE.growth,
MASTFILE.physical_name,
MASTFILE.size * 8192.00 / (1024.00 * 1024.00) AS Size_in_MB  --8192 Page size,  1024*1024 convert to MB 

FROM 
MASTER.sys.databases AS SYSDBLIST
INNER JOIN
sys.master_files AS MASTFILE
ON
SYSDBLIST.database_id=MASTFILE.database_id

This part of the report is broken into two tables. The first table list out the database name, file type, size, and file location while the second table enumerates the properties assigned to each database.

The results portion of the status report is shown below.

The final section of the database status report will list out all the procedures and tables that have changed in the past 7 days. Although you would want to initiate some type of formal database change auditing to get full details on the changes made to a database, this method uses the objects' modified date to determine if an object has changed within the past 7 days. You will notice in the below query, we use the sp_MSforeachDB stored procedure to cycle through all the databases looking for changes. Only procedure and table object types are returned by the query; you could expand this list by adding additional object types if desired.


CREATE TABLE #Objectlist 
( DBName      VARCHAR(1000)
, ObjectName  VARCHAR(1000)
, Modify_Date DATETIME
, Object_Type VARCHAR(1000) 
)

DECLARE @command varchar (1000)
SELECT @command = 'use ? INSERT INTO #ObjectList
SELECT ''?'' DBname
,name
,modify_date
,CASE 
WHEN TYPE=''p'' THEN ''Procedure''
WHEN TYPE=''u'' THEN ''Table''
ELSE ''OTHER'' END AS Object_Type
FROM sys.objects
WHERE type IN (''p'',''U'')
AND DATEDIFF(D,modify_date, GETDATE()) < 7'
EXEC sp_MSforeachdb @command

SELECT
 DBName  
,  ObjectName 
,  Modify_Date 
,  Object_Type  
FROM #Objectlist

ORDER 
BY Modify_Date


DROP TABLE #Objectlist

The last table on our report will list out the procedures and tables that have changed and when that change occurred. Note this query will only tell us the "last change" and again is not a full audit of DDL modifications.

For this particular query, changes to tempdb are included, and as you can imagine, that list could grow quite large. Thus we add a filter to the table to exclude any changes sourced from tempdb.

Finally, we finish off the actual report with a list of objects that have been modified in the last 7 days.

A copy of the SSRS RDL file used in this tip can be download from the link at the end of this tip.

Conclusion

This tip covers generating a server and database status report for your use in the administrative tasks that you encounter every day. This report includes details about SQL Server instance level properties, about the backups completed on the server, about database file location and size statistics, and database level properties. Certainly, additional properties are in each of the system views and can be added to the SSRS report queries and template that is included with this tip. These additions provide a way for you to customize the report for your own organization's need.

Next Steps

Download SSRS Report RDL file
Adding Custom Reports to SQL Server Management Studio - https://www.mssqltips.com/sqlservertip/3291/adding-custom-reports-to-sql-server-management-studio/
SQL Agent Job History Schedule - https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

About the author

Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips