I recently set up a new report in reporting services to monitor the companies SQL Servers, making life a bit simper and gain kudos with the management. The report lists disk space on all drives as well as the last time that the SQL Server instance was started. This uses a rather useful undocumented extended stored procedure that I discovered when searching for how to find the disk space available on a server, when you only have access to the server via ODBC or the client tools. This article discusses how to build my report.

In reporting services create a Shared Data Source to the server you wish to monitor. Connect using an account that has read access to the master database. I set up specific IDs just to use with reporting services but you might not need to do this depending how you have reporting services set up.

Next add a new dataset connecting to the Shared Data Source, for ease name this dataset as the "servername", shown as "Houdini" in the image. Add the following script:

CREATE TABLE #FixedDrives 
(DriveLetterVARCHAR(10), 
MB_FreeDEC(20,2))
INSERTINTO #FixedDrives EXEC Master..XP_FixedDrives
SELECT * FROM #FixedDrives
DROP TABLE #FixedDrives

This is shown in the image below:

Figure 1

I then created another new dataset I named it the "servername"uptime (like "SQL01Uptime") connecting to the same Shared Data Source, but using the following script:

SELECT CONVERT(CHAR(25),login_time,100) as 'Started',
CONVERT(CHAR(25), DATEDIFF(DAY, login_time, GETDATE())) as 'Uptime'
FROM master..sysprocesses 
WHERE spid = 1

This finds when the Server was last started and the number of days it has been running.

Create the report

In the layout window add a new table with the first dataset "servername" as your source. I created 2 new table headers making 3 in total, in the first header row I added the server name in the first column, in the second column I added the label Last Started and then dragged the Started field from my second datasource servernameuptime into the final column as =First(Fields!Started.Value, "ServerNameUptime").

In the second header row, in the second column I added the label Uptime (Days) and then dragged the Uptime field from my second datasource into the final column as =First(Fields!Uptime.Value, "ServerNameUptime"). I then renamed this field from Uptime, to ServerNameUptime so that it distinguishes this field. I then added an expression to the Color properties of this ServerNameUptime field, to highlight in red when the value is less than 2.

 
=iif(ReportItems!servernameuptime.Value<2,"Red","Black") 

This is to indicate when this server has just recently been rebooted as I really want to highlight that to the recipient of the report.

In the third header Row add the Headers Drive in the first column, and MB Free in the second. In the details drag the DriveLetter and MB_Free fields from the first dataset (named as the servername). The designer is shown in Figure 2.

Figure 2

Conclusion

The following report is the result:

Figure 3

I added all my servers to this report and scheduled it to run at 8:00am every morning to go to the inbox of anyone who would need to know this information. If SQL Services have been restarted or the server rebooted overnight you would instantly have the Uptime highlighted in red and you could investigate why this had occurred. One thing you could also do is add a conditional expression to change the colour of the MB Free to red when it was less than a given amount as well, allowing you to determine if any server is running out of space.