InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:36:37 GMT20http://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxThanks much, we don't have mount points. I will give it a tryFri, 09 Nov 2012 09:27:53 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxIf you don't have any mount points, here is a script for using the xp_fixeddrives function. The threshold is set in the if exists line, and sends an email showing all drives with free space below the set threshold. Hope it helps someone.CREATE TABLE #DriveSpace(DriveLetter char(1),MBFree int)INSERT INTO #DriveSpaceEXEC master.dbo.xp_fixeddrivesif exists (SELECT * FROM #DriveSpace WHERE MBFree < 10000) -- value to set threshold at, in MB BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Server Agent Mail Profile', @recipients = 'someone@mycompany.com', @query = 'CREATE TABLE #DriveSpace2 (DriveLetter Char(1),MBFree int); INSERT INTO #DriveSpace2 EXEC master.dbo.xp_fixeddrives; SELECT DriveLetter, MBFree FROM #DriveSpace2; DROP TABLE #DriveSpace2' , @subject = '<ServerName> - Low Disk Space', @attach_query_result_as_file = 1 ; ENDDROP TABLE #DriveSpaceFri, 09 Nov 2012 08:55:46 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspx[quote][b]SGT_squeequal (11/8/2012)[/b][hr]@jeff here you go, save it then rename it to .vbs :-)[/quote]Heh... I saw some CDO email in there. You're a man after my own heart. Thanks for posting the code.Thu, 08 Nov 2012 11:07:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxyour welcome, i use windows scheduled task, you have to pass 3 parameter1 drive or URL to Drive 2 hostname (not used in processing, only used to write to log file so i know what server its checking)3 Threshold (i use 15% when server has 15% or less email me)Example exec from a batch filecscript //b //nologo C:\DriveCheckv1.5.vbs "\\192.0.0.142\data" SERVER_SHARE 15message me if you have any proplemsThu, 08 Nov 2012 11:07:38 GMTSGT_squeequalhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxThank you SGT_squeequal.Do you schedule this as a windows task or a sql agent job?I will give it a try.Thu, 08 Nov 2012 10:54:44 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspx@jeff here you go, save it then rename it to .vbs :-)Thu, 08 Nov 2012 10:50:17 GMTSGT_squeequalhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxI would avoid xp_fixeddrives. It does not recognize mount points. You will get much better information querying the win32_Volume WMI class. You can do it easy if you are using powershell:get-wmiobject -class win32_volume | select-object -property name, label, capacity, freespace | format-table -wrapThu, 08 Nov 2012 07:36:15 GMTJoie Andrewhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspx[quote][b]SGT_squeequal (11/7/2012)[/b][hr]you could create something from master..xp_fixeddrives however, i created a VBS script that you pass a couple of parameter, disk to check and threshold, if the disk space is less then threshold then an email is sent. drop me a private message with your email address and i will send you a copy[/quote]Any chance of you posting the VBS script? Thanks.Thu, 08 Nov 2012 07:18:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxThe best way is to use a simple WMI query as follows[code]wmic volume get capacity, "free space", name[/code]If xp_cmdshell is disabled you can turn it on first then disable it again afterwards.Or you could run it from a central trusted server where xp_cmdshell is on and use the Node paarameter to read remote servers like so (also has an output file)[code]wmic /output:"c:\temp\mountsizes.txt" /node:sqlnode1,sqlnode2,sqlnode3,sqlnode4volume get capacity, "free space", name, systemname[/code]Thu, 08 Nov 2012 04:54:52 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxthat URL does not resolve for me, like i say i use a VBS script that works perfect for me i can even pass a URL and it works prety well for meWed, 07 Nov 2012 15:33:09 GMTSGT_squeequalhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxThanks, I am currently using a sqlscript from this site http://www.sqldbatips.com/showcode.asp?ID=4 ,which enables sp_OACreate. And I just came back from a SQl in the City session, it says to enable sp_OA is not good for security.So I think I may need to change that. What do you think this script? ThanksWed, 07 Nov 2012 15:23:15 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxyou could create something from master..xp_fixeddrives however, i created a VBS script that you pass a couple of parameter, disk to check and threshold, if the disk space is less then threshold then an email is sent. drop me a private message with your email address and i will send you a copyWed, 07 Nov 2012 15:16:24 GMTSGT_squeequalhttp://www.sqlservercentral.com/Forums/Topic1382186-1550-1.aspxIs there any existing and recommended script that checks sql server drives, and send alerts to DBA, like there is only 10% space or 30 % space free ?ThanksWed, 07 Nov 2012 15:02:02 GMTsqlfriends