Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reporting Services Makes Server Support Easier

By Carolyn Richardson,

Using Reporting Services (SQL 2000) to make server support easier

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 
		(DriveLetter	VARCHAR(10), 
		MB_Free		DEC(20,2))

	INSERT	INTO #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.

Total article views: 8751 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

Tutorial: Designing Your First Report in SQL Server Reporting Services

Trying to get past the Microsoft SQL Server Reporting Services learning curve? Well never fret, this...

FORUM

Report Manager's Subscriptions Page column/field view

Can you add additional fields?

FORUM

Concatenate existing fields SQL server

concatenate existing fields SQL server

FORUM

Field Explorar like Crystal Report

Field Explorar like Crystal Report

FORUM

column width in report

column width in report

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones