Centralized SQL Server 2000 Monitoring

Central Disk Space Utilization Monitoring

This is a similar system to the one outlined in the part of this article, but for a different purpose. Earlier on in my current job, I’d occasionally be embarrassed by finding out too late in the day that a critical system was running low on drive space. People really lose confidence in you if you aren’t continually on top of your disk space management. It also helps greatly if you can warn people that servers are likely to run out of disk space and take preventative measures in advance of critical problems occurring.

If you are going to make predictions as to when drives will fill up, while also being able to assure yourself and your colleagues that your servers aren’t running out of disk, it can help to implement an automated system to collect drive space utilization information from your servers and locate it in a central place for alert generation and trend analysis.

Now, I expect people will write to me after this article is published and say “you can do this in Perfmon, you can do this using 3rd party software”, etc. Well that may be true. But I don’t like spending money on things I can do myself, and I believe my system has advantages over Perfmon’s logging and alerting mechanisms.

Enough of That, How Does It Work?

The system relies on the system extended stored procedure:

xp_fixeddrives

This SP exists in the master database and simply tells you disk free space for each drive in MB. My system runs from a central server. It executes xp_fixeddrives remotely on all the predefined target servers and stores the output in a table. This data can then be queried against a lookup table containing each server’s drive capacities. You need to maintain the capacity information manually, as xp_fixeddrives does not report capacity, just free space. So, for example, if you add disks to an array or change a drive’s capacity in any way, you must remember to manually change the capacity information you store in your lookup table. This is crucial because the key measure used in reporting is % free space, which needs the obviously requires the total capacity of a drive in order to be able to calculate the value.

Once again, I recommend that you locate all your tables and procedures in a central, dba/admin-type database. I use my centraldb database, the same one I used before.

The Tables

The master table is "Server" and is defined as:


CREATE TABLE [dbo].[Server] (
[ServerID] [int] IDENTITY (1, 1) NOT NULL ,
[Servername] [sysname] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Server] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ServerID]
) ON [PRIMARY]
GO


This has a one-to-many relationship to the lookup table "ServerDriveCapacity."


CREATE TABLE [dbo].[ServerDriveCapacity] (
[ServerID] [int] NOT NULL ,
[Drive] [char] (1) NOT NULL ,
[Capacity] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ServerDriveCapacity] WITH NOCHECK ADD
CONSTRAINT [pk_01] PRIMARY KEY CLUSTERED
(
[ServerID],
[Drive]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ServerDriveCapacity] ADD
CONSTRAINT [fk_01] FOREIGN KEY
(
[ServerID]
) REFERENCES [dbo].[Server] (
[ServerID]
)
GO


And this table contains the static lookup data of drive letters and their capacities (all units are MB).

The table for holding the drive usage collected from xp_fixeddrives is called
ServerFreeSpaceHistory."


CREATE TABLE [dbo].[ServerFreeSpaceHistory] (
[ServerID] [int] NOT NULL ,
[Drive] [char] (1) NOT NULL ,
[FreeSpace] [int] NOT NULL ,
[CheckDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [SFSH_01] ON [dbo].[ServerFreeSpaceHistory]([CheckDate]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ServerFreeSpaceHistory] ADD
CONSTRAINT [DF__Serv__Check__59063A47] DEFAULT (getdate()) FOR [CheckDate]
GO

CREATE NONCLUSTERED INDEX [SFSH_02] ON [dbo].[ServerFreeSpaceHistory]([ServerID], [Drive]) ON [PRIMARY]
GO