Post reply

Check for Server harddrive space

  • November 25, 2002 at 1:03 pm

    #80410

    Is there a stored procedure that will check for Server harddrive space and notify the DBA or stop the server when a certain threshold is reached by the server hardrive?

  • November 25, 2002 at 1:13 pm

    #442342

    Yes there is. Here is a some code I built and put in a job step, which is scheduled hourly. If this job notices less than 1 gig of free space the dba's get notified

    set nocount on

    declare @MBSIZE int

    CREATE TABLE #DriveSpace (

    driveletter CHAR(1) NOT NULL,

    freespace VARCHAR (10) NOT NULL)

    INSERT INTO #DriveSpace EXEC master.dbo.xp_fixeddrives

    select @MBSIZE=freespace from #DriveSpace where driveletter = 'F'

    if @MBSIZE < 1024

    EXEC master.dbo.xp_sendmail '<DBA EMAIL ADDRESS GOES HERE>',

    @subject = 'freespace has dropped below 1 GB threshold',

    @message = 'Review space used on "F" drive. Current space usage is below the 1024 MB threshold'

    select @MBSIZE=freespace from #DriveSpace where driveletter = 'H'

    if @MBSIZE < 1024

    EXEC master.dbo.xp_sendmail '<DBA EMAIL ADDRESS GOES HERE>',

    @subject = 'freespace has dropped below 1 GB threshold',

    @message = 'Review space used on "H" drive. Current space usage is below the 1024 MB threshold'

    drop table #DriveSpace

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply