none
Low disk space alert script RRS feed

  • Question

  • Hello

    can you guys suggest me a low disk space alert script using TSQL please, thank you.

    Thursday, December 29, 2016 12:28 AM

Answers

    • Proposed as answer by Times Decides Saturday, December 31, 2016 9:00 AM
    • Marked as answer by DataNerdE Wednesday, January 18, 2017 4:34 PM
    Saturday, December 31, 2016 7:37 AM
  • Hi,

    If you specifically looking for TSQL Script below is the one you could use. You can define a threshold parameter and calculate a condition if threshold has reached and on the basis of that condition, Send an email (may be using DBMail). Let me know if you need help in doing that.

    Please Note: This Script require xp_cmdshell to be set using sp_configure. 

    DECLARE	@Drive TINYINT
    DECLARE	@SQL VARCHAR(100)
    
    SET	@Drive = 97
    
    DECLARE	@Drives TABLE
    (
    Drive CHAR(1),
    Info VARCHAR(80)
    )
    
    WHILE @Drive <= 122
    	BEGIN
    		SET	@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
    		
    		INSERT	@Drives
    			(
    				Info
    			)
    		EXEC	(@SQL)
    
    		UPDATE	@Drives
    		SET	Drive = CHAR(@Drive)
    		WHERE	Drive IS NULL
    
    		SET	@Drive = @Drive + 1
    	END
    
    
    SELECT		UPPER(Drive) As Drive,
    		SUM(CASE WHEN Info LIKE 'Total # of bytes             : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS numeric(20,2)) ELSE CAST(0 AS numeric(20,2)) END)/1073741824.0 AS TotalGB,
    		SUM(CASE WHEN Info LIKE 'Total # of avail free bytes  : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS numeric(20,2)) ELSE CAST(0 AS numeric(20,2)) END)/1073741824.0 AS AvailFreeGB
    FROM		(
    			SELECT	Drive,
    				Info
    			FROM	@Drives
    			WHERE	Info LIKE 'Total # of %'
    		) AS d
    GROUP BY	Drive
    ORDER BY	Drive
    
    
    

    Thanks,


    Kindly mark the reply as answer if they help

    • Marked as answer by DataNerdE Wednesday, January 18, 2017 4:36 PM
    Tuesday, January 3, 2017 6:21 AM

All replies

  • Hello,

    Choose one of the following:



    http://www.sqlservercentral.com/articles/alerts/89885/ (Steps 3 and 4)

    http://sqlmag.com/blog/disk-space-monitoring-how



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, December 29, 2016 12:55 AM
    Moderator
  • Hi ,

    following link using XP_fixeddrive command kept in table send email.

    http://stackoverflow.com/questions/29215341/low-disk-space-alert-by-percentage

    http://sqlmag.com/blog/disk-space-monitoring-how


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Saturday, December 31, 2016 7:34 AM
    • Proposed as answer by Times Decides Saturday, December 31, 2016 9:00 AM
    • Marked as answer by DataNerdE Wednesday, January 18, 2017 4:34 PM
    Saturday, December 31, 2016 7:37 AM
  • you monitor script using powershell.

    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Saturday, December 31, 2016 8:59 AM
  • You can also try using this free SQL storage manager tool which should be an ideal solution to work around your situation.
    Tuesday, January 3, 2017 5:57 AM
  • Hi,

    If you specifically looking for TSQL Script below is the one you could use. You can define a threshold parameter and calculate a condition if threshold has reached and on the basis of that condition, Send an email (may be using DBMail). Let me know if you need help in doing that.

    Please Note: This Script require xp_cmdshell to be set using sp_configure. 

    DECLARE	@Drive TINYINT
    DECLARE	@SQL VARCHAR(100)
    
    SET	@Drive = 97
    
    DECLARE	@Drives TABLE
    (
    Drive CHAR(1),
    Info VARCHAR(80)
    )
    
    WHILE @Drive <= 122
    	BEGIN
    		SET	@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
    		
    		INSERT	@Drives
    			(
    				Info
    			)
    		EXEC	(@SQL)
    
    		UPDATE	@Drives
    		SET	Drive = CHAR(@Drive)
    		WHERE	Drive IS NULL
    
    		SET	@Drive = @Drive + 1
    	END
    
    
    SELECT		UPPER(Drive) As Drive,
    		SUM(CASE WHEN Info LIKE 'Total # of bytes             : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS numeric(20,2)) ELSE CAST(0 AS numeric(20,2)) END)/1073741824.0 AS TotalGB,
    		SUM(CASE WHEN Info LIKE 'Total # of avail free bytes  : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS numeric(20,2)) ELSE CAST(0 AS numeric(20,2)) END)/1073741824.0 AS AvailFreeGB
    FROM		(
    			SELECT	Drive,
    				Info
    			FROM	@Drives
    			WHERE	Info LIKE 'Total # of %'
    		) AS d
    GROUP BY	Drive
    ORDER BY	Drive
    
    
    

    Thanks,


    Kindly mark the reply as answer if they help

    • Marked as answer by DataNerdE Wednesday, January 18, 2017 4:36 PM
    Tuesday, January 3, 2017 6:21 AM