%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