Greetings!
Happy New Year! To kick off your year smoothly, we'd like to help you ensure you have the disk space needed to perform your work. Check out the routine below, and also make sure you monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks. |
 SQL Server Administration: Monitor disk space free with a simple routine. Make sure that you have room on your drives to hold the work being performed. If you run out of disk space, your SQL Server will stop accepting requests.
In the code below, the @Threshold variable is designed to hold a value in MB that is the amount of space that should be available on any drive connected to your SQL Server. Change the @Threshold value to find drives that do not have enough space free.
The routine below will return a list of drives that have less than 500MB free.
SET NOCOUNT ON
DECLARE @Threshold NUMERIC(5,0)
SET @Threshold=500 -- 500MB
CREATE TABLE #DiskSpace(
Drive VARCHAR(2),
SpaceFreeMB NUMERIC(10, 2))
INSERT INTO #DiskSpace(Drive,SpaceFreeMB)
EXEC('master..xp_fixeddrives')
SELECT Drive,SpaceFreeMB
FROM #DiskSpace
WHERE SpaceFreeMB < @Threshold
DROP TABLE #DiskSpace
SET NOCOUNT OFF
__________________________________________________________________ |
