Centralized SQL Server 2000 Monitoring

Collecting Free Space Data Stored Procedure

The all-important procedure for going and getting the data using xp_fixeddrives is called GetFreeSpace.

CREATE procedure GetFreeSpace @servername sysname

set nocount on

declare @sql varchar(1000),
@serverid int

select @serverid = ServerID
from Server
where Servername = @servername

select @sql = ‘EXEC ‘ + @servername + ‘.master.dbo.xp_fixeddrives’

create table #t1
(drive char(1), freespace int)
insert into #t1 exec(@sql)

insert into ServerFreeSpaceHistory
(ServerID, Drive, Freespace)
select @serverid, Drive, freespace
from #t1

drop table #t1


You now need to create a job on your central server to execute this procedure in turn for each of your target servers, e.g.:

EXEC GetFreeSpace ‘SERVER1’
EXEC GetFreeSpace ‘SERVER2’

and so on. I run this once a day. As in part 1, you need to ensure your intra-server security is set up and works — and don’t assume that if it when you’re running it in Query Analyzer, it’ll work in SQL Server Agent. Test it first!

Creating a View

To check your most recent free space data, and report on it, you create a view and select from this, so you can see where your problems are.

The view is called “FreeSpaceSummary.”

CREATE VIEW FreeSpaceSummary

SELECT os.Servername,
convert(decimal,100 * fs.FreeSpace / dc.Capacity) as PercentFree,
FROM OxfamServerFreeSpaceHistory fs
JOIN OxfamServer os
ON fs.ServerID=os.ServerID
JOIN OxfamServerDriveCapacity dc
ON dc.Drive=fs.Drive
AND dc.ServerId = fs.ServerID
WHERE Checkdate>Dateadd(day,-1,getdate())

If you select from this view like this:

SELECT ServerName, Drive, PercentFree, FreeSpace, Capacity
FROM dbo.FreeSpaceSummary
ORDER BY PercentFree asc

You will see right at the top of the list server drives with the lowest percent of free space. I could have embedded the order by in the view, but I left it out as you may wish to order some other way, e.g., by servername.

As before, I like to embed this SELECT from this view in an active server page for ease of use and ease of sharing. This can be done with ease using the FrontPage wizard in the same way as in the first part of this article, if you are not a confident ASP developer. Sample output is shown here.


Pages: 1 2 3 4 5


No comments yet... Be the first to leave a reply!