• 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