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
as
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
GO
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
AS
SELECT os.Servername,
fs.Drive,
convert(decimal,100 * fs.FreeSpace / dc.Capacity) as PercentFree,
fs.FreeSpace,
dc.Capacity
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.
