August 11, 2010 at 3:58 pm
Disk space is one of very important issue a DBA concerns.
When a server has low free space, a warning message will pop up. Does anyone know how to set this threshold?
Many thanks for all input in advance.
August 12, 2010 at 8:00 am
There is no direct way of doing this. However, the following modification in the Registry works for you.
Caution: Modifying the registry can have serious impacts. Ensure to have proper backups before proceeding.
Open Registry Editor (Run --> regedit.exe)
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters
Check if a DWORD key exists with the name DiskSpaceThreshold
If exists modify it, if not right click --> New --> DWORD Value and name it as DiskSpaceThreshold
Double click on DiskSpaceThreshold and enter the value for which you want to be alerted (% full).
Restart the box
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 13, 2010 at 1:50 pm
Sure there's way to do it within SQL, but I wouldn't recommend them.
One of them is:
DECLARE @HDDDrivesList TABLE (
DriveLetter char(1)
, FreeSpaceLeft int NULL
)
INSERT @HDDDrivesList(DriveLetter,FreeSpaceLeft)
EXEC master.dbo.xp_fixeddrives
SELECT * FROM @HDDDrivesList
Which get you remaining space for each drives. Then with SQL Agent you could configure a schedule task to check those value and send you a mail using SQL DBMail if something is not right.
You could also use CLR, or xp_cmdshell (bad advice for the latter, still if you really need it you know it exist)
But for managing disk space, my first thought would be doing it at the Windows server level using counters "LogicalDisk" and looking for sub-counter of free space left instead of doing it in SQL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply