Time and Space: How to Monitor Drive Space in SQL Server
-
If you use mount points, then the approach outlined here isn't going to do do much for you.
Also, if you are using clusters, then it's often useful to screen out any drives that don't apply to your instance.
I use the following which does a pretty decent job of giving you the info you need, even if you're using mount points
;WITH FileIds
AS
( SELECT DISTINCT database_id, file_id
FROM master.sys.master_files AS mf
)
SELECT DISTINCT
volume_mount_point AS VolumeName ,
logical_volume_name,
(total_bytes / 1073741824) SizeGB ,
(available_bytes / 1073741824) FreeSpaceGB,
CAST((available_bytes / 1073741824.0)/ (total_bytes / 1073741824.0)*100 AS NUMERIC( 5, 2)) PercentFree
FROM FileIds f
CROSS APPLY sys.dm_os_volume_stats (f.database_id, f.file_id)
-
mhickin 11975 (1/21/2016)
If you use mount points, then the approach outlined here isn't going to do do much for you.Also, if you are using clusters, then it's often useful to screen out any drives that don't apply to your instance.
I use the following which does a pretty decent job of giving you the info you need, even if you're using mount points
;WITH FileIds
AS
( SELECT DISTINCT database_id, file_id
FROM master.sys.master_files AS mf
)
SELECT DISTINCT
volume_mount_point AS VolumeName ,
logical_volume_name,
(total_bytes / 1073741824) SizeGB ,
(available_bytes / 1073741824) FreeSpaceGB,
CAST((available_bytes / 1073741824.0)/ (total_bytes / 1073741824.0)*100 AS NUMERIC( 5, 2)) PercentFree
FROM FileIds f
CROSS APPLY sys.dm_os_volume_stats (f.database_id, f.file_id)
See my previous post about the use of this DMF. The query you have written is susceptible to duplicate rows for the same volume despite the use of distinct.
Also, the use of the CTE to get distinct fileid and databaseid is unnecessary. The combination of fileid and databaseid is unique in sys.master_files already.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
For more info on the duplication from the dmf, this article outlines what is happening:
[/b]
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (1/21/2016)
For more info on the duplication from the dmf, this article outlines what is happening:[/b]
Very nice article, Jason. Great explanation of why not to use the DMF.
-
Ed Wagner (1/21/2016)
SQLRNNR (1/21/2016)
For more info on the duplication from the dmf, this article outlines what is happening:[/b]
Very nice article, Jason. Great explanation of why not to use the DMF.
Thanks Ed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Well written, thanks.
-
I had to change the CTE used to query the data out of the table. The PCT Free calc was not correct.
replace
CONVERT(Numeric(18, 1), 100 - (r.Free * 100.0 / r.Total)) FreePercent
with
CONVERT(Numeric(18, 1), 100.0 * (r.Free * 1.0) / (r.Total * 1.0)) FreePercent
to get the correct FreePercent
Thanks for the great article. I am using it to forecast space growth.
-
That's so nice to hear. Thanks very much for the feedback.
-
If you don't want to enable xp_cmdshell, you can put the WMIC command in a job step that can capture the output to a file that you can bulk insert into a table.
-
I recently implemented this procedure in our test environment, and I don't see any data in Drivespace. Not sure why it is not populating the data in Drive space table. I am using 2008 R2 version. If i query #incoming table i see the data attached in below picture. Can someone help here?
- bonagiris9 wrote:
I recently implemented this procedure in our test environment, and I don't see any data in Drivespace. Not sure why it is not populating the data in Drive space table. I am using 2008 R2 version. If i query #incoming table i see the data attached in below picture. Can someone help here?
I'm thinking that Drive A: is a floppy drive and drive D: is a CD and neither one of them have a disk inserted.
--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) -
I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.
CREATE PROCEDURE dbo.TrackDriveSpace
AS
DECLARE @dtmNow Datetime = GETDATE();
BEGIN
-----------------------------------------------------------------------------
--initialization
-----------------------------------------------------------------------------
--SET NOCOUNT ON;
--create a temp table to capture our incoming rows from the dos command
IF OBJECT_ID('tempdb.dbo.#incoming', 'u') IS NOT NULL DROP TABLE #incoming;
CREATE TABLE #incoming (
RawLine Varchar(255),
N integer not null identity(1, 1));
-----------------------------------------------------------------------------
--shell out to dos and call wmic to get the disk space data needed
-----------------------------------------------------------------------------
INSERT INTO #incoming(rawline)
EXECUTE xp_cmdshell 'wmic logicaldisk get deviceid,freespace,size';
-----------------------------------------------------------------------------
--extract the data and write the rows to the permanent table
-----------------------------------------------------------------------------
WITH cteHeader AS (
--read our header row
SELECT RawLine
FROM #incoming
WHERE N = 1
),
cteStart AS (
--determine the starting positions of each column to extract
SELECT DriveStart = 1,
FreeStart = CHARINDEX('FreeSpace', h.RawLine),
TotalStart = CHARINDEX('Size', h.RawLine)
FROM cteHeader h
),
cteDriveInfo AS (
--isolate each "column" of data, allowing for the return at the end of the last column
SELECT Drive = SUBSTRING(i.RawLine, p.DriveStart, 1),
FreeBytes = LTRIM(RTRIM(SUBSTRING(i.RawLine, p.FreeStart, p.TotalStart - p.FreeStart))),
TotalBytes = RTRIM(LTRIM(RTRIM(REPLACE(SUBSTRING(i.Rawline, p.TotalStart, 99), CHAR(13), ''))))
FROM #incoming i
CROSS APPLY cteStart p
WHERE i.N > 1
AND RawLine IS NOT NULL
AND NOT SUBSTRING(RawLine, 3, 99) = REPLICATE(SUBSTRING(RawLine, 3, 99), ' ')
)
INSERT INTO dbo.DriveSpace(Drive, Total, Free, EntryDate)
SELECT LOWER(Drive), CONVERT(Bigint, TotalBytes), CONVERT(Bigint, FreeBytes), @dtmNow
FROM cteDriveInfo
WHERE NOT TotalBytes = ''
ORDER BY Drive;
END
-----------------------------------------------------------------------------
--termination
-----------------------------------------------------------------------------
DROP TABLE #incoming;
- Jeff Moden wrote:bonagiris9 wrote:
I recently implemented this procedure in our test environment, and I don't see any data in Drivespace. Not sure why it is not populating the data in Drive space table. I am using 2008 R2 version. If i query #incoming table i see the data attached in below picture. Can someone help here?
I'm thinking that Drive A: is a floppy drive and drive D: is a CD and neither one of them have a disk inserted.
I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.
CREATE PROCEDURE dbo.TrackDriveSpace
AS
DECLARE @dtmNow Datetime = GETDATE();
BEGIN
-----------------------------------------------------------------------------
--initialization
-----------------------------------------------------------------------------
--SET NOCOUNT ON;
--create a temp table to capture our incoming rows from the dos command
IF OBJECT_ID('tempdb.dbo.#incoming', 'u') IS NOT NULL DROP TABLE #incoming;
CREATE TABLE #incoming (
RawLine Varchar(255),
N integer not null identity(1, 1));
-----------------------------------------------------------------------------
--shell out to dos and call wmic to get the disk space data needed
-----------------------------------------------------------------------------
INSERT INTO #incoming(rawline)
EXECUTE xp_cmdshell 'wmic logicaldisk get deviceid,freespace,size';
-----------------------------------------------------------------------------
--extract the data and write the rows to the permanent table
-----------------------------------------------------------------------------
WITH cteHeader AS (
--read our header row
SELECT RawLine
FROM #incoming
WHERE N = 1
),
cteStart AS (
--determine the starting positions of each column to extract
SELECT DriveStart = 1,
FreeStart = CHARINDEX('FreeSpace', h.RawLine),
TotalStart = CHARINDEX('Size', h.RawLine)
FROM cteHeader h
),
cteDriveInfo AS (
--isolate each "column" of data, allowing for the return at the end of the last column
SELECT Drive = SUBSTRING(i.RawLine, p.DriveStart, 1),
FreeBytes = LTRIM(RTRIM(SUBSTRING(i.RawLine, p.FreeStart, p.TotalStart - p.FreeStart))),
TotalBytes = RTRIM(LTRIM(RTRIM(REPLACE(SUBSTRING(i.Rawline, p.TotalStart, 99), CHAR(13), ''))))
FROM #incoming i
CROSS APPLY cteStart p
WHERE i.N > 1
AND RawLine IS NOT NULL
AND NOT SUBSTRING(RawLine, 3, 99) = REPLICATE(SUBSTRING(RawLine, 3, 99), ' ')
)
INSERT INTO dbo.DriveSpace(Drive, Total, Free, EntryDate)
SELECT LOWER(Drive), CONVERT(Bigint, TotalBytes), CONVERT(Bigint, FreeBytes), @dtmNow
FROM cteDriveInfo
WHERE NOT TotalBytes = ''
ORDER BY Drive;
END
-----------------------------------------------------------------------------
--termination
-----------------------------------------------------------------------------
DROP TABLE #incoming;
- bonagiris9 wrote:
I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.
This tells me that you've probably created the procedure, but haven't run it. Once you create it, you have to run it to collect the data and write it to your table. I do this with a database job that just fires the procedure and is scheduled to run once a day.
- Ed Wagner wrote:bonagiris9 wrote:
I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.
This tells me that you've probably created the procedure, but haven't run it. Once you create it, you have to run it to collect the data and write it to your table. I do this with a database job that just fires the procedure and is scheduled to run once a day.
Thanks much, It works fine now. how ever coming to the next part . I was just testing following snippet to see the data that gets inserted into drive info table and i see only one row . Attached serverDrives and drivespace output as well.
WITH cteDrives AS (
--start with the list of drives to check for this server
SELECT DriveLetter, Description
FROM svr.ServerDrives
WHERE ServerName = @@SERVERNAME
),
WITH cteDrives AS (
--start with the list of drives to check for this server
SELECT DriveLetter, Description
FROM svr.ServerDrives
WHERE ServerName = @@SERVERNAME
),
cteDriveInfo AS (
--query a list of dates going back @Days days and use a crosstab to pivot the rows into columns for each date.
--this gives a table with date and free space on the data, log and backup drives, along with an ascending
--integer for each row.
SELECT ROW_NUMBER() OVER(ORDER BY ds.EntryDate) RowNum, ds.EntryDate,
DataFree = CONVERT(Numeric(18, 3), MAX(CASE WHEN d.Description = 'Data' THEN ds.Free * 1.0 / POWER(1024, 3) END)),
LogFree = CONVERT(Numeric(18, 3), MAX(CASE WHEN d.Description = 'Logs' THEN ds.Free * 1.0 / POWER(1024, 3) END)),
BackupFree = CONVERT(Numeric(18, 3), MAX(CASE WHEN d.Description = 'Backup' THEN ds.Free * 1.0 / POWER(1024, 3) END))
FROM cteDrives d
INNER JOIN dbo.DriveSpace ds ON ds.Drive = d.DriveLetter
WHERE EntryDate >= DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()) - 0)
GROUP BY ds.EntryDate
),
cteDailyGrowth AS (
--using the row number, join the drive free table to itself, offset by one day. this gets the free
-- space for each drive on that day and also on the next day.
SELECT di1.RowNum, di1.EntryDate, di1.DataFree CurrentData, di1.LogFree CurrentLog, di1.BackupFree CurrentBackup,
di2.DataFree NextData, di2.LogFree NextLog, di2.BackupFree NextBackup
FROM cteDriveInfo di1
INNER JOIN cteDriveInfo di2 ON di2.RowNum = di1.RowNum + 1
)
--populate the temp table with the drive, date and free space for each drive on the date and the following date.
--the growth of each drive is the current minus the next.
INSERT INTO #DriveInfo(ID, EntryDate, CurrentData, CurrentLog, CurrentBackup,
NextData, NextLog, NextBackup, DataGrowth, LogGrowth, BackupGrowth)
SELECT RowNum, CAST(EntryDate AS Date), CurrentData, CurrentLog, CurrentBackup, NextData, NextLog, NextBackup,
DataGrowth = CurrentData - NextData,
LogGrowth = CurrentLog - NextLog,
BackupGrowth = CurrentBackup - NextBackup
FROM cteDailyGrowth
ORDER BY EntryDate;
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply