Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Customized SSRS report to monitor server and database status


By:   |   Read Comments (5)   |   Related Tips: More > Monitoring

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

sys config

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.

Report Part 1

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).

Report v 1

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.

Backup Report Template

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.

Backup Report v1

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.

Database stats

The results portion of the status report is shown below.

Databases Files

Database Stats v2

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.

Objects Changed

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.

filter temp db

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

object changed report

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 15, 2016 - 9:33:21 AM - Scott Murray Back To Top

Andy.. What error are you getting.. depending on your network, you may have to put in the full instance or the ip address.


Wednesday, June 15, 2016 - 8:49:29 AM - andy Back To Top

 what am I missing?  the servername parm doesnt seem to work for me.

 


Wednesday, June 15, 2016 - 6:28:09 AM - Greg Robidoux Back To Top

The download has been fixed.

-Greg


Wednesday, June 15, 2016 - 4:01:54 AM - Ulven Back To Top

Im having problems trying to download the SSRS RDL File. Just getting the "401 - Unauthorized: Access is denied due to invalid credentials." error.

Any suggestions?


Wednesday, June 15, 2016 - 3:18:34 AM - Arie Koster Back To Top

Dear Sir,

I want to download the SSRS Report RDL file but get the error 

401 - Unauthorized: Access is denied due to invalid credentials.

Can you help?

 

Kind regards,

Arie Koster


Learn more about SQL Server tools