joeroshan (6/13/2009)
,,the table. It gives me control over which all servers to check among my long list of linked servers.
Not sure how efficient & advisable it is to link many servers.
Being conservative, I run a small sp as part of each nightly backup/maint plan on each SQL server I administer. It writes the free space into a small cummulative table in the Master db, which I can then retrieve with an external application every morning.
Table:
CREATE TABLE [dbo].[tblStatsDisk](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DiskTimeStamp] [datetime] NULL,
[Drive] [char](1) NULL,
[FreeDisk] [bigint] NULL
Maint Plan SP:
USE MASTER
-- Description: Poll free disk space on each drive immediately after the backup
-- =============================================
ALTER PROCEDURE [dbo].[GetDiskFree]
AS
/* TEST HARNESS
exec [dbo].[GetDiskFree]
select * from dbo.tblStatsDisk
*/
BEGIN
SET NOCOUNT ON;
INSERT into dbo.tblStatsDisk (drive, FreeDisk)
EXEC master.dbo.xp_fixeddrives
-- this command creates the rows (one for each drive) with NULL date, srv... fields
-- the columns must allow NULL especially disktimestamp
UPDATE master.dbo.tblStatsDisk
SET DiskTimeStamp = (GETDATE())
where disktimestamp is null
END
----
How you collect and centralise this later is another story ... but you start small with a modular approach then build an application that combines this with some DB size stats collected from the backup DMV routines.
HTH