Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

check diskspace Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 3:02 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 1,792, Visits: 3,262
Is 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 ?

Thanks
Post #1382186
Posted Wednesday, November 7, 2012 3:16 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 12:05 PM
Points: 271, Visits: 1,021
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

*************************************************************

The first is always the hardest
Post #1382191
Posted Wednesday, November 7, 2012 3:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 1,792, Visits: 3,262
Thanks, 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? Thanks
Post #1382194
Posted Wednesday, November 7, 2012 3:33 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 12:05 PM
Points: 271, Visits: 1,021
that 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 me

*************************************************************

The first is always the hardest
Post #1382203
Posted Thursday, November 8, 2012 4:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
The best way is to use a simple WMI query as follows

wmic volume get capacity, "free space", name

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)

wmic /output:"c:\temp\mountsizes.txt" /node:sqlnode1,sqlnode2,sqlnode3,sqlnode4
volume get capacity, "free space", name, systemname



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1382417
Posted Thursday, November 8, 2012 7:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
SGT_squeequal (11/7/2012)
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


Any chance of you posting the VBS script? Thanks.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1382480
Posted Thursday, November 8, 2012 7:36 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 11:07 AM
Points: 710, Visits: 1,452
I 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 -wrap


Joie Andrew
"Since 1982"
Post #1382496
Posted Thursday, November 8, 2012 10:50 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 12:05 PM
Points: 271, Visits: 1,021
@jeff here you go, save it then rename it to .vbs



*************************************************************

The first is always the hardest


  Post Attachments 
DriveCheckv1.5.txt (23 views, 3.66 KB)
Post #1382632
Posted Thursday, November 8, 2012 10:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 1,792, Visits: 3,262
Thank you SGT_squeequal.

Do you schedule this as a windows task or a sql agent job?

I will give it a try.
Post #1382636
Posted Thursday, November 8, 2012 11:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 12:05 PM
Points: 271, Visits: 1,021
your welcome, i use windows scheduled task, you have to pass 3 parameter

1 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 file

cscript //b //nologo C:\DriveCheckv1.5.vbs "\\192.0.0.142\data" SERVER_SHARE 15

message me if you have any proplems


*************************************************************

The first is always the hardest
Post #1382644
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse