SQL Script to find disk space on the server including mount point space

DBAs are frequently confronted with the problem of needing to obtain storage capacity data on a server, if the server has mount points configured then xp_fixeddrives does not provide complete information.

In such circumstances, I use the script below to retrieve free disc space on the servers, as well as mount point space. This script has been updated to output in the format below.

This script examines the value of xp_cmdshell on the server and, if it is enabled, keeps it enabled after the script is finished, and, if it is disabled, disables it after the script is finished.

/* Enable xp_cmdshell */
Declare @enabled sql_variant
SELECT @enabled = value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell';

IF @enabled = 0
Begin

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;

End

/*********** Main Codes **********/
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 drop the temporary table */

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 iddrive=@i
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 iddrive=@i

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 iddrive=@i

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
,totalspace as 'TotalSpace(GB)'
,freespace as 'FreeSpace(GB)'
,CAST((freespace/totalspace * 100) AS DECIMAL(5,1)) as [%Free]
, case
        when     CAST((freespace/totalspace * 100) AS DECIMAL(5,1)) <= 10  or freespace <= 10 then '<============ Critical !!!'
        else '' 
  end as [!!! Alert !!!]
FROM #total
/*--WHERE (freespace/totalspace * 100) < 5*/
ORDER BY drive

THEEND:

/* Disable xp_cmdshell */
IF @enabled = 0
Begin

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;

End

select db_name() DB,ceiling((sum(size * (8.0/1024))*1.2)/8000)* 8 MP_size_GB  , groupid, sum(size * (8.0/1024)) dbsize, sum((size - FILEPROPERTY (name,'spaceused')) * (8.0/1024)) db_free_space
into #size_estimator
from sysfiles  
where 1=2
group by groupid

exec sp_MSforeachdb 'use [?];insert #size_estimator
select db_name() DB,ceiling((sum(size * (8.0/1024))*1.2)/8000)* 8 MP_size_GB  , groupid, sum(size * (8.0/1024)) dbsize, sum((size - FILEPROPERTY (name,''spaceused'')) * (8.0/1024)) db_free_space
from sysfiles  
group by groupid'

select @@servername ServerNm, isnull(f.db, '<Unused>') db,  coalesce(f.mp,rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1)))) as MP, type_desc
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,1) as 'TotalSpace_GB'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,1) as 'FreeSpace_GB', s.dbsize DBSize_MP, s.db_free_space DB_Free_Space_MP, getdate() Statistic_dt
from #output o
full outer join (select distinct db_name(database_id) db, substring(physical_name, 1, charindex('\',replace(physical_name,':\MP','xxxx')) ) mp, type_desc from sys.master_files) f
on rtrim(ltrim(SUBSTRING(o.line,1,CHARINDEX('|',o.line) -1))) = f.mp
full outer join #size_estimator s
on s.DB = f.db and type_desc = case groupid when 0 then 'LOG' when 1 then 'ROWS' end
where line is not null
and substring(line,1,1) <> '\'
order by 2,3 

drop table #output
drop table #size_estimator

Output is shown as

Advertisement