SQL Script to get Disk Storage details

Leave a comment (2) Go to comments

At time, we wanted to make a report for Disk Space information, where we need to login every server and make a note. TO get rid of this, here is a script that will give you the following infomation

  1. All Drive Letters added on the Server
  2. Total Drive Storage Capacity
  3. Free Storage on all availbale drive letters
  4. % of Free Space on all drive.

SQL Script

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT,1
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN

OUTPUT

Drive Free(MB) Total(MB) Free(%)
C 39507 96052 41
D 57979 142216 40
E 76339 83887 91
F 99753 107301 93
J 57979 142216 40
SQL Script to get Disk Storage details storage report SQL Script to get Disk Storage details SQL Script to get Disk space details SQL Script to get disk space report disk space from sql script disk space info

Screen shot for Script OUTPUT of Find out Disk space information using SQL Service Script

EOF - SQL Script to get Disk Storage details, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

2 Comments.

  1. Hi All,

    It’s a great script to know space available on Installed SQL server physical disk drives from client connection

    Regards

    Jayant dass

    9650336531
    9313406257

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.