Saturday, 16 February 2013

Find storage including mount points Sql and windows utility


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
 ,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

No comments:

Post a Comment