In my previous article on “Diskspace Check via SQLServer” will find only physical \ logical drives that is attached to the system i.e the drive which you see under my computer. I’ve used this script in one of my client machine and found that this script is not capable to check disk space for mounted drives. Hence I’ve written an WMI script and SQL Script (like version 1) to fetch both physical disk & mounted disk space details. I hope this would help most of the DBA’s to monitor their mounted voulmes where they have hosted their database files. Read more »
Tag Archives: Diskspace
Disk space (including mounted drive) Check via SQL Server
Posted by Vidhya Sagar
on February 5, 2009
No comments
Diskspace Check via SQLServer
Posted by Vidhya Sagar
on June 13, 2008
No comments
I’m writing this script since most of them are looking a way to find total diskspace available in a drives through sql server. I hope there is no extended procedure for this. I’ve used WMI script to do this, download the vbs script and save it to a location and use this location in the sql script to get the result.
SQL Script
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp’)
DROP TABLE ##tmp
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell ‘cscript C:\diskspace.vbs’ – change the path here
SET ROWCOUNT 3
DELETE ##tmp
SET ROWCOUNT 0
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp2′)
DROP TABLE ##tmp2
CREATE TABLE ##tmp2(Driveletter VARCHAR(2),TotalDiskSpace_in_MB FLOAT, Freespace_in_MB FLOAT)
INSERT ##tmp2
SELECT SUBSTRING(diskspace,1,3) , CONVERT(FLOAT,SUBSTRING(diskspace,4,10)),
CONVERT(FLOAT,SUBSTRING(diskspace,15,10)) FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2
Sample Output
Driveletter |
TotalDiskSpace_in_MB |
Freespace_in_MB |
|
C: |
12644.8789 |
1153.70312 |
|
D: |
45504.3945 |
29603.9921 |
|
E: |
10001.371 |
450.316406 |