GET MOUNTPOINT DISKSPACE DETAILS

— 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 ‘totalspace’
,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)) / (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:

—————————————————————————————————
—-MOUNTPOINT DISK SPACE With FILE NAMES ——————–

————

 

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name PhysicalFileLocation,
dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0/1024.0) AS FreeSpaceInGB,
CONVERT(INT,dovs.total_bytes/1024.0/1024.0/1024.0) AS TotSpaceGB,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB,
CONVERT(INT,dovs.total_bytes/1024.0/1024.0) AS TotalSpaceInMB,
Convert(INT,((dovs.available_bytes/1048576.0/1024.0)/(dovs.total_bytes/1024.0/1024.0/1024.0)) * 100) AS PercentageFree
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC

About: Niraj Kumar


Leave a Reply

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