By: Scott Murray | Updated: 2016-06-15 | 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
Article Last Updated: 2016-06-15
