check diskspace

Question

check diskspace

sqlfriend

SSC Guru

Points: 52781
More actions
November 7, 2012 at 3:02 pm

#269969

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

Viewing 13 posts - 1 through 12 (of 12 total)

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

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 step is always the hardest *******

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

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 step is always the hardest *******

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

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" 😉

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 6

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"

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 7

@jeff here you go, save it then rename it to .vbs 🙂

***The first step is always the hardest *******

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

Thank you SGT_squeequal.

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

I will give it a try.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 9

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 step is always the hardest *******

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

SGT_squeequal (11/8/2012)
@jeff here you go, save it then rename it to .vbs 🙂

Heh... I saw some CDO email in there. You're a man after my own heart. Thanks for posting the code.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vikingDBA SSCertifiable Points: 5356 More actions · Answer 11

If 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

(DriveLetterchar(1),

MBFreeint)

INSERT INTO #DriveSpace

EXEC master.dbo.xp_fixeddrives

if 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 ;

END

DROP TABLE #DriveSpace

sqlfriend SSC Guru Points: 52781 More actions · Answer 12

Thanks much, we don't have mount points. I will give it a try