Total disk space and available space on Mount Points

We have most of our servers using mount points and we have weekly disk space reports generated. One of my friends environment has started using mount points and slowly the number of servers using mount points is expected to grow. The traditional ways of getting total space and available space from disk drives do not work for mount points. My friend needed a script to generate a report for total space and available space for each mount point on each server  for monitoring.

Here is the script which will give us that information. Best part is that it gives us the info for Standard drives as well as mount points. So we do not need to have two scripts. It can be run as and when needed or can be run as a job on each server and send the result as an email or can be run from a central server to connect to each server needed and execute this script  and add the results to a report file which can be saved/emailed.

 

— To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
— To update the currently configured value for advanced options.
RECONFIGURE
GO
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO
DECLARE @sqlver sql_variant
DECLARE @sqlver2 varchar(20)
DECLARE @sqlver3 int
SELECT @sqlver = SERVERPROPERTY(‘productversion’)
SELECT @sqlver2 = CAST(@sqlver AS varchar(20))
select @sqlver3 = SUBSTRING(@sqlver2,1,1)
— 1 = 2008 8 = 2000 and 9 = 2005 1 is short for 10
BEGIN
–select @sqlver3 only uncomment to see state of version
IF @sqlver3 = 1 GOTO SERVER2008
IF @sqlver3 = 9 GOTO SERVER2000
IF @sqlver3 = 8 GOTO SERVER2000
GOTO THEEND
END
SERVER2008:
declare @svrName varchar(255)
declare @sql varchar(400)
–by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = ‘powershell.exe -c “Get-WmiObject -Class Win32_Volume -Filter ”DriveType = 3” | select name,capacity,freespace | foreach{$_.name+”|”+$_.capacity/1048576+”%”+$_.freespace/1048576+”*”}”‘
–creating a temporary table
CREATE TABLE #output
(line varchar(255))
–inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
–script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as drive
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0)as ‘freespace’
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0) as ‘totalspace’
,((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0)) * 100) as percentfree
from #output
where line like ‘[A-Z][:]%’
–and ((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
— (CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
–(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0)) * 100) < 5
order by drive
–script to drop the temporary table
drop table #output
GOTO THEEND
SERVER2000:
SET NOCOUNT ON;
DECLARE @v_cmd nvarchar(255)
,@v_drive char(99)
,@v_sql nvarchar(255)
,@i int
SELECT @v_cmd = ‘fsutil volume diskfree %d%’
SET @i = 1
CREATE TABLE #drives(iddrive smallint ,drive char(99))
CREATE TABLE #t(drive char(99),shellCmd nvarchar(500));
CREATE TABLE #total(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));
— Use mountvol command to
INSERT #drives (drive)
EXEC master..xp_cmdshell ‘mountvol’
DELETE #drives WHERE drive not like ‘%:\%’ or drive is null
WHILE (@i <= (SELECT count(drive) FROM #drives))
BEGIN
UPDATE #drives
SET [email protected]
WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)
SELECT @v_sql = REPLACE(@v_cmd,’%d%’,LTRIM(RTRIM(drive))) from #drives where [email protected]
INSERT #t(shellCmd)
EXEC master..xp_cmdshell @v_sql
UPDATE #t
SET #t.drive = d.drive
FROM #drives d
WHERE #t.drive IS NULL and [email protected]
SET @i = @i + 1
END
INSERT INTO #total
SELECT bb.drive
,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(‘:’,shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))
,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace
,tt.titi as total
FROM #t bb
JOIN (SELECT drive
,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(‘:’,shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))
,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi
FROM #t
WHERE drive IS NOT NULL
AND shellCmd NOT LIKE ‘%free bytes%’) tt
ON bb.drive = tt.drive
WHERE bb.drive IS NOT NULL
AND bb.shellCmd NOT LIKE ‘%avail free bytes%’
AND bb.shellCmd LIKE ‘%free bytes%’;
— SET FreespaceTimestamp = (GETDATE())
SELECT RTRIM(LTRIM(drive)) as drive
,freespace
,totalspace
,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]
FROM #total
–WHERE (freespace/totalspace * 100) < 5
ORDER BY drive
DROP TABLE #drives
DROP TABLE #t
DROP TABLE #total
THEEND:

About: Niraj Kumar


Leave a Reply

Your email address will not be published. Required fields are marked *