Table of Contents
Introduction
Pre-requisites
- The FSUTIL utility requires that you have administrative privileges
- sysadmin rights on the sql server
- Enable xp_cmdshell
Data flow
The below diagram depicts the flow of the code
Enable xp_cmdshell
The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the extended stored procedure can be executed on a system also this procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code
sp_configure
'show advanced options'
, 1;
GO
RECONFIGURE;
GO
sp_configure
'xp_cmdShell'
, 1;
GO
RECONFIGURE;
GO
The below sql lists drives attached to the file system
EXEC
[master].[dbo].[xp_cmdshell] N
'FSUTIL FSINFO DRIVES'
The FSUTIL volume diskfree command list the drive usage statistics of the filesystem. The below example gathers the statistics of c drive.
declare
@driveName
varchar
(100)
declare
@query
varchar
(1000)
declare
@fsinfo
varchar
(1000)
set
@driveName =
'C'
set
@query =
'FSUTIL VOLUME DISKFREE '
+@driveName+
':\'
exec
xp_cmdshell @query
The below screen shot shows the output of FSUTIL DRIVES and VOLUME.
In the code, the where clause construct is used to get the non-null value from FSUTIL FSINFO DRIVES output and then manipulated using string function to get only the drive details A\C\D\E\F\G\
/* Inserting the non-
null
values
to
temp
table
*/
SELECT
@workstring = [
output
]
FROM
@xp_cmdshell_output
WHERE
[
output
]
LIKE
'Drives:%'
DELETE
FROM
@xp_cmdshell_output
/*
Prepare
string
for
XML parsing*/
-----------------------------------------------
-- Replace string "Drives",":" and " " by ''
---------------------------------------------
SELECT
@workstring =
REPLACE
(
REPLACE
(
REPLACE
(@workstring,
'Drives'
,
''
),
':'
,
''
),
' '
,
''
)
--PRINT @workstring
Output : A\C\D\E\F\G\
SELECT
@XML =
CAST
(
(
'<X>'
+
REPLACE
(@workstring
,
'\'
,'
</X><X>
')
+ '
</X>
')
AS XML)
INSERT INTO @drives ([Drive])
SELECT LTRIM(N.value('
.
', '
VARCHAR
(4000)
'))
FROM @XML.nodes('
X
') AS T(N)
WHERE ASCII(LTRIM(N.value('
.
', '
VARCHAR
(4000)'))) != 0
select
*
from
@drives
SELECT
@recid = 1
WHILE @recid <= (
SELECT
MAX
([RecID])
FROM
@drives)
BEGIN
SELECT
@workstring =
''
,@vexec_str =
'EXEC [master].[dbo].[xp_cmdshell] '
+ QUOTENAME(
'FSUTIL VOLUME DISKFREE '
+ [Drive]
+
':'
,
CHAR
(39))
FROM
@drives
WHERE
[RecID] = @recid
INSERT
INTO
@xp_cmdshell_output ([
output
])
EXEC
(@vexec_str)
SELECT
@workstring = [
output
]
FROM
@xp_cmdshell_output
WHERE
[
output
]
LIKE
'%Total # of bytes%'
IF @workstring
IS
NOT
NULL
AND
LEN(@workstring) > 0
BEGIN
SELECT
@workstring = LTRIM(
SUBSTRING
(@workstring
,CHARINDEX(
':'
,@workstring
) + 1
,LEN(@workstring)
)
)
SELECT
@workstring =
LEFT
(@workstring, LEN(@workstring))
/*
update
the
free
field
and
convert
its value
to
GB */
UPDATE
@drives
SET
[
Size
] = (
CONVERT
(
numeric
, @workstring))/1024/1024/1024.00
WHERE
[RecID] = @recid
END
ELSE
DELETE
FROM
@drives
WHERE
[RecID] = @recid
The last part of the code fetches the data from the table variable
SELECT
@@ServerName server,Drive,
Size
,
Free
,
cast
(
Free
/
Size
* 100.00
as
decimal
(5,2))
'%Free'
FROM
@drives
Download
The code is uploaded in the gallery TSQL_DiskSpace_FSUTIL
SQL Code
The complete code is given below. The code has broken into many pieces and explained above with few screen shots.
/* Variable declaration*/
DECLARE
@recid
INT
,@workstring
VARCHAR
(8000)
,@XML XML
,@vexec_str
VARCHAR
(8000)
-- Create table variable to hold drive size info
DECLARE
@drives
TABLE
(
[RecID] TINYINT IDENTITY(1,1)
-- Record ID
,[Drive]
VARCHAR
(10)
-- Drive letter
,[
Size
]
NUMERIC
NULL
-- Drive size
,[
Free
]
NUMERIC
NULL
)
-- Create table variable for xp_cmdshell output
DECLARE
@xp_cmdshell_output
TABLE
(
[
output
]
VARCHAR
(8000)
NULL
-- Raw text returned from xp_cmdshell execution
)
INSERT
INTO
@xp_cmdshell_output ([
output
])
EXEC
[master].[dbo].[xp_cmdshell] N
'FSUTIL FSINFO DRIVES'
/* Error handling*/
-----------------------------------------------------------------
--Check for sql server privilge to execute the FSUTIL utility to gather disk status
-----------------------------------------------------------------
IF (
SELECT
COUNT
(1)
FROM
@xp_cmdshell_output
WHERE
[
output
] =
'The FSUTIL utility requires that you have administrative privileges.'
) > 0
RAISERROR (
'SQL Server Service account not an admin on this computer.'
, 11, 1);
ELSE
BEGIN
/* Inserting the non-
null
values
to
temp
table
*/
SELECT
@workstring = [
output
]
FROM
@xp_cmdshell_output
WHERE
[
output
]
LIKE
'Drives:%'
DELETE
FROM
@xp_cmdshell_output
/*
Prepare
string
for
XML parsing*/
-----------------------------------------------
-- Replace string "Drives",":" and " " by ''
---------------------------------------------
SELECT
@workstring =
REPLACE
(
REPLACE
(
REPLACE
(@workstring,
'Drives'
,
''
),
':'
,
''
),
' '
,
''
)
--PRINT @workstring
END
/* XML Parsing - Spilting the delimited string using XML*/
-----------------------------------------------------
-- the string is parsed for the delimiter '\'
-----------------------------------------------------
SELECT
@XML =
CAST
(
('<X>
'
+ REPLACE(@workstring
,'
\
'
,'
</X><X>
')
+ '
</X>
')
AS XML)
/* Store the parsed value into table variable */
INSERT INTO @drives ([Drive])
SELECT LTRIM(N.value('
.
', '
VARCHAR
(4000)
'))
FROM @XML.nodes('
X
') AS T(N)
WHERE ASCII(LTRIM(N.value('
.
', '
VARCHAR
(4000)
'))) != 0
----
--Display the results
---
select * from @drives
-- Get size for each drive
SELECT @recid = 1
WHILE @recid <= (SELECT MAX([RecID]) FROM @drives)
BEGIN
SELECT @workstring = '
'
,@vexec_str = '
EXEC
[master].[dbo].[xp_cmdshell]
'
+ QUOTENAME('
FSUTIL VOLUME DISKFREE
'
+ [Drive]
+ '
:
'
,CHAR(39))
FROM @drives
WHERE [RecID] = @recid
INSERT INTO @xp_cmdshell_output ([output])
EXEC (@vexec_str)
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE '
%Total #
of
bytes%
'
IF @workstring IS NOT NULL AND LEN(@workstring) > 0
BEGIN
SELECT @workstring = LTRIM(
SUBSTRING(@workstring
,CHARINDEX('
:
'
,@workstring
) + 1
,LEN(@workstring)
)
)
SELECT @workstring = LEFT(@workstring, LEN(@workstring))
/* update the free field and convert its value to GB */
UPDATE @drives
SET [Size] = (CONVERT(numeric, @workstring))/1024/1024/1024.00
WHERE [RecID] = @recid
END
ELSE
DELETE
FROM @drives
WHERE [RecID] = @recid
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE '
%Total #
of
free
bytes%
'
IF @workstring IS NOT NULL AND LEN(@workstring) > 0
BEGIN
SELECT @workstring = LTRIM(
SUBSTRING(@workstring
,CHARINDEX('
:
'
,@workstring
) + 1
,LEN(@workstring)
)
)
SELECT @workstring = LEFT(@workstring, LEN(@workstring))
/* update the free field and convert its value to GB */
UPDATE @drives
SET [free] = (convert(numeric, @workstring))/1024/1024/1024.00
WHERE [RecID] = @recid
END
ELSE
DELETE
FROM @drives
WHERE [RecID] = @recid
DELETE FROM @xp_cmdshell_output
SELECT @recid = @recid + 1
END
SELECT @@ServerName server,Drive,Size,Free, cast(Free/Size * 100.00 as decimal(5,2)) '
%
Free
'
FROM
@drives
Output
Conclusion
There are many ways to gather disk space. Its up-to an individual to gather the metrics using available list of tools and utilities. The above steps only briefs about an other way of capturing the details.
References
FSUTIL
See Also
The below article gives an idea to execute sql script over multiple servers using sqlcmd
SQL – Disk Space Monitoring using OLE and WMI
PoSH – DiskSpaceGUITool