%FreeSpace Alert in SQL 2005

  • Hi All,

    Im looking for a way to create an alert to email an operator when, for arguments sake, a physical disk becomes 70% full. I know this can be done within system monitor but all that can do is perform a net send, what we need is an email. Is it possible for an SQL Server alert to do this?

    I cannot find anything under SQL Server performance condition alert so im assuming that the only way this is possible would be a WMI query?

    After much researching through books online and lots of googling I am only finding creating stored procedures which will do this. Thats all well and good but we want this set to fire an alert as soon as the threshold is breached.

    If anyone could provide any help on this it will be greatly appreciated.

  • Hi Jamie

    You can use the XP to get drive and space information and the put that into a job on SQL server, this miget alert you via a mail, we have a application that runs as a alerting service, which will be usefull, if you create one, it looks into servers space every 5 minutes and then updates it into a table, then an application looks into the predefined space and throws error when there is a space breach

    Cheers

  • You can use xp_fixeddrives

  • Cheers for your thoughts guys. I guess I will just have to stick to SP's and scheduled jobs.

  • Is there a way to get this as Total Size and Free and in GIG and MG.

  • Create a vbs file in C; Drive named as disk.vbs

    content are given below

    Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")

    for each Disk in DiskSet

    If (Disk.FreeSpace/Disk.Size) < 0.30 Then

    WScript.Echo "Drive " + Disk.Name + " is low on space."

    End If

    Next

    Then Create a Job which will run on your requirement to check sapce and in Step 1 put the below code.

    create table ##diskspace (details varchar(1000))

    insert into ##diskspace

    exec xp_cmdshell 'cscript c:\disk.vbs'

    if (select count(*) from ##diskspace where cmd like '%low on space%') > 0

    begin

    declare @sql varchar(200)

    SELECT @sql ='select * from ##diskspace where cmd like ''%low on space%'''

    PRINT @SQL

    exec master.dbo.xp_sendmail @recipients='Your ID',

    @query = @sql,

    @subject = 'Disk Space Full'

    end

    drop table ##DiskSpace

    Hope So It is HelpFul 🙂

    Regards,

    Raj

  • Thats a good script did not know you could call vbs scripts in SQL 2005.

    Nice - thanks for sharing

  • TRACEY

    Thanks 🙂

    For Size in MB and GB you can divide by 1024.

    Disk.Size and Disk.FreeSpace.

    Regards,

    Raj

  • Hey Rajdba, that's a great script, thanks for that. Do you have any other scripts that can help to monitor the databases. Thanks in advance.

  • Welcome,

    Please give me a list, I will send you.

    Regards,

    raj

  • Hmmmm wish list ---- Wow i have many that i need to write.

    Here is just one.........

    SQL NT Accounts

    Show database access, role security, tables access, select , etc.

    Then go back into LDAP and get all the users who are assigned to this GROUP

    This way i can determine if the little help desk users added someone without my knowledge

    then viola they get all the data hmmm.

  • The script - could it be able to run on one server and then pick up all servers in network and then i could report on every server in the network what space is ...wow that be awesome....Hmm one minute isn't that the network teams job 🙂

  • I will prepare the list and send it to you next week. Thanks in advance.

  • Tracey

    I have started working on my free time . I will Post When Ever I will Complete.

    Regards,

    Raj

  • I was on leave and today I tried to write a script on Network Discovery .

    I tried by OSQL or SQLCMD to get the SQL Server List but it had given me the all the Instances including Developer's Machine Instance Name.

    So I am using a VBS script to resolve it.

    Create a file named a SQL.VBS in C: Drive on the computer from where you are going to execute the Script. Fill the file with below contents.

    Set objAdRootDSE = GetObject("LDAP://RootDSE")

    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

    Set objRS = CreateObject("adodb.recordset")

    Const HKEY_LOCAL_MACHINE = &H80000002

    Dim StdIn: Set StdIn = WScript.StdIn

    Dim StdOut: Set StdOut = WScript

    dim osql:

    const SQLDMODep_Children = 262144

    dim objArgs: Set objArgs = WScript.Arguments

    varConfigNC = objAdRootDSE.Get("defaultNamingContext")

    strConnstring = "Provider=ADsDSOObject"

    strWQL = "SELECT * FROM 'LDAP://" & varConfigNC & "' WHERE objectCategory= 'Computer' and OperatingSystem = 'Windows*Server*'"

    objRS.Open strWQL, strConnstring

    Do until objRS.eof

    Set objServer = GetObject(objRS.Fields.Item(0))

    strServerName = objServer.CN

    Set colItems = objWMIService.ExecQuery("Select * from Win32_PingStatus Where Address = '" & objServer.DNSHostName & "'")

    For Each objItem in colItems

    If objItem.StatusCode = 0 Then 'The Computer is Pingable

    Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strServerName & "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"

    strValueName = "InstalledInstances"

    objRegistry.GetMultiStringValue HKEY_LOCAL_MACHINE,strKeyPath, strValueName,arrValues

    If IsNull(arrValues) = 0 Then

    strMsg = strServerName

    StdOut.Echo strmsg

    Set oSql = WScript.CreateObject("SQLDMO.SQLServer2")

    StdOut.Echo "windows Authentication - Connection to database"

    StdOut.Echo strmsg

    oSql.LoginSecure = True 'windows authentication

    oSql.Connect strmsg

    on error resume next

    if err.Description <> "" then

    StdOut.Echo err.Description

    end if

    End If

    Set objRegistry = Nothing

    End If

    Set objServer = Nothing

    Next

    objRS.movenext

    Loop

    objRS.close

    set oSql = nothing

    Set objWMIService = Nothing

    Set objRS = Nothing

    Set objAdRootDSE = Nothing

    After That, run the below sql script >> SQLSERVER must be running on Domain Admin Privs. >> Not tried on other Privs .

    SET QUOTED_IDENTIFIER OFF

    if exists (select * from tempdb.dbo.sysobjects where name like '#SERVERS%')

    drop table #SERVERS

    if exists (select * from tempdb.dbo.sysobjects where name like '#REPORT%')

    drop table #REPORT

    -- CREATING TEMP TABLE FOR STORING SERVER NAME AND DATABASE USAGE REPORTS

    CREATE TABLE #REPORT (ID INT IDENTITY(1,1) ,SERVER_NM VARCHAR(200), DETAILS TEXT)

    CREATE TABLE #SERVERS (I INT IDENTITY(1,1),SERVERNAME VARCHAR(200))

    -- INSERTIONS OF SERVER NAME

    INSERT INTO #SERVERS

    EXEC XP_CMDSHELL 'CSCRIPT C:\SQL.VBS'

    DECLARE @I INT

    DECLARE @CUR INT

    DECLARE @SERVER_NM VARCHAR(200)

    DECLARE @XP_S VARCHAR(8000)

    DECLARE @DISKVBS VARCHAR(8000)

    DECLARE @FILENAME VARCHAR(20)

    DECLARE @ID INT

    SET @ID=1

    SET @I = 5 --TILL 3 IT IS NOT REQUIRED

    SET @CUR =IDENT_CURRENT ('#SERVERS')

    SET @FILENAME = 'C:\DISK.VBS'

    WHILE (@I < @CUR)

    BEGIN

    DECLARE @CMD VARCHAR(200)

    SELECT @SERVER_NM = (SELECT LTRIM(RTRIM(SERVERNAME)) FROM #SERVERS WHERE I = @I)

    EXEC SP_ADDLINKEDSERVER @SERVER_NM

    SET @SERVER_NM = "[" + @SERVER_NM + "]"

    BEGIN

    SET @DISKVBS = 'EXEC '+ @SERVER_NM + '.MASTER.DBO.XP_CMDSHELL '''

    SET @XP_S = @DISKVBS + 'ECHO ' + 'Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")' + ' > ' + @FILENAME + ''''

    EXEC(@XP_S)

    -- IF ERROR THEN BUT THE PROBLEM IS THAT I CANNOT HANDLE FATAL ERROR LIKE IF INSTANCE IS NOT RUNNING

    IF @@ERROR <> 0

    BEGIN

    SET @I = @I+1

    PRINT 'ERROR DROP SERVER STARTED'

    SET @CMD = 'EXEC MASTER.DBO.SP_DROPSERVER ' + @SERVER_NM

    PRINT @CMD

    EXEC(@CMD)

    PRINT 'ERROR DROP SERVER COMPLETETD'

    CONTINUE

    END

    -- NOT ABLE TO CREATE FILES IN REMOTE SERVER WITH NEW LINES SO I HAD TO DO ECHO EVERYTIME

    SET @XP_S = @DISKVBS + 'ECHO ' + ' for each Disk in DiskSet ' + ' >> ' + @FILENAME + ''''

    EXEC(@XP_S)

    SET @XP_S = @DISKVBS + 'ECHO ' + 'If (Disk.FreeSpace/Disk.Size) ^ ' + @FILENAME + ''''

    EXEC(@XP_S)

    SET @XP_S = @DISKVBS + 'ECHO ' + ' WScript.Echo "Drive " + Disk.Name + " Total Size = " + Disk.Size + " The Free Space = " + Disk.FreeSpace + " MB"'+ ' >> ' + @FILENAME + ''''

    EXEC(@XP_S)

    SET @XP_S = @DISKVBS + 'ECHO ' + ' End If ' + ' >> ' + @FILENAME + ''''

    EXEC(@XP_S)

    SET @XP_S = @DISKVBS + 'ECHO ' + ' Next' + ' >> ' + @FILENAME + ''''

    EXEC(@XP_S)

    BEGIN TRANSACTION

    SET @CMD = 'INSERT INTO #REPORT (DETAILS) EXEC ' + @SERVER_NM +'.MASTER.DBO.XP_CMDSHELL '+ "'CSCRIPT C:\DISK.VBS'"

    EXEC(@CMD)

    COMMIT

    WHILE (@ID <= IDENT_CURRENT('#REPORT'))

    BEGIN

    UPDATE #REPORT

    SET SERVER_NM = @SERVER_NM

    WHERE ID = @ID

    SET @ID=@ID+1

    END

    SET @I = @I+1

    SET @CMD = 'EXEC MASTER.DBO.SP_DROPSERVER ' + @SERVER_NM

    PRINT @CMD

    EXEC(@CMD)

    END

    END

    I required some help from you friends, I cannot able to handle the fatal errors like if server doesn't exist or access denied. At the time the script is terminating.

    In the VBS script (Thanks to Google) it getting the list of Computers which have Windows Server as OS after that it is Pinging it and after that in registry of that server it checking is SQL Server Instance is there or not.

    But if MSSQL Service is off then What to do?

    The workaround is that in VBS Script we have to create a database connection and have to query

    SELECT COUNT(*) FROM MASTER.DBO.SYSDATABASES

    IF IT IS GREATER THAN 0 THEN IT WILL CONSIDER THE SERVER NAME WHICH WILL ADDED LATER ON.

    As the SQL 2000 Server is still there in production so I am not using TRY .... CATCH of SQL 2005.

    Monday I will work on this if any body can help me out then it will be completed.

    I think better if we can make another post for it.

    Hope to hear from you friends.:)

    Regards,

    Raj

Viewing 15 posts - 1 through 15 (of 18 total)

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