January 8, 2008 at 11:49 pm
anyone know any workaround in TSQL who can give this result set?
Drive letter | Total Disk Space (MB) | Free Disk Space (MB)
-----------------------------------------------------------
C 150000 100000
D 160000 130000
Q 100000 90000
S 300000 200000
T 300000 280000
i can get the drive letters and free disk space in MB from "xp_fixeddrives" but i don't know how to get the total disk space in MB using TSQL...
thanks in advance...
January 9, 2008 at 7:46 am
January 9, 2008 at 9:04 am
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; 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 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, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
SQL DBA.
January 9, 2008 at 9:21 am
You can get this information with the PSINFO utility available on the link below.
http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx
Sample command and output:
psinfo \\MyServerName -D disk
PsInfo v1.73 - Local and remote system information viewer
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - http://www.sysinternals.com
System information for \\MyServerName:
Volume Type Format Label Size Free Free
A: Removable 0.0%
C: Fixed NTFS MyServer_C 19.99 GB 14.92 GB 74.7%
D: Fixed NTFS MyServer_D 12.00 GB 9.41 GB 78.5%
Z: CD-ROM 0.0%
January 9, 2008 at 11:27 pm
Hello Raymond,
Here is the simple command which will give you desired result.
xp_fixeddrives
Hope this is what are you looking for.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 10, 2008 at 12:45 am
Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2008 at 3:42 pm
Jeff Moden (1/10/2008)
Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. 😉
Not to mention that the OP said he used xp_fixeddrives, and it didn't give him all the info he needed.
January 10, 2008 at 7:38 pm
Thanks a bunch guys! Really appreciate this although I have created a workaround... I used FSUTIL command line and did some tweaking to get the result I need and I also compared it with the result from xp_fixedrives..
Below is the code.. I have one problem though... When I tried converting
the Bytes to Mbytes at the last SELECT statement, I got this error.
"Server: Msg 8114, Level 16, State 5, Line 42
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint."
Help please! :):):)
------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @varSQL varchar(1000), @varDrive varchar(10)
CREATE TABLE #tmpDriveSpaceInfo
(drive varchar(10),
xpFixedDrive_FreeSpace_MB bigint,
FSutil_FreeSpace_Bytes integer,
FSutil_Space_Bytes integer,
FSutil_AvailSpace_Bytes integer
)
CREATE TABLE #tmpFSutilDriveSpaceInfo
(drive varchar(10),
info varchar(50)
)
INSERT INTO #tmpDriveSpaceInfo (drive, xpFixedDrive_FreeSpace_MB)
EXEC master..xp_fixeddrives
DECLARE CUR_DriveLooper CURSOR FOR SELECT drive FROM #tmpDriveSpaceInfo
OPEN CUR_DriveLooper
FETCH NEXT FROM CUR_DriveLooper INTO @varDrive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'EXEC master..XP_CMDSHELL ' + ''''+ 'fsutil volume diskfree ' + @varDrive + ':' + ''''
INSERT INTO #tmpFSutilDriveSpaceInfo (info)
EXEC(@varSQL)
UPDATE #tmpFSutilDriveSpaceInfo SET drive = @varDrive WHERE drive IS NULL
FETCH NEXT FROM CUR_DriveLooper INTO @varDrive
END
DELETE FROM #tmpFSutilDriveSpaceInfo WHERE info IS NULL
SELECT drive,
ltrim(rtrim(left(info,29))) as InfoType,
ltrim(rtrim(substring (info, charindex (':',info) + 2, 20))) as Size_Bytes
INTO #tmpFSutilDriveSpaceInfo_Fixed
FROM #tmpFSutilDriveSpaceInfo
SELECT a.drive,
a.xpFixedDrive_FreeSpace_MB,
(SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of free bytes')/1048576 AS FSutil_FreeSpace_MB,
(SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of bytes')/1048576 AS FSutil_TotalSpace_MB,
(SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of avail free bytes')/1048576 AS FSutil_AvailableSpace_MB
FROM #tmpDriveSpaceInfo a
CLOSE CUR_DriveLooper
DEALLOCATE CUR_DriveLooper
DROP TABLE #tmpFSutilDriveSpaceInfo
DROP TABLE #tmpDriveSpaceInfo
DROP TABLE #tmpFSutilDriveSpaceInfo_Fixed
------------------------------------------------------------------
March 10, 2009 at 11:34 am
for SQL 2005 or 08 you need to enable the OLE Automation sps
here is the full query with enable and then disable the OLEs
/*******************************************************/
/* Enabling Ole Automation Procedures */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*******************************************************/
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; 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 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 @@Servername
SELECT
drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
/*******************************************************/
/* Disabling Ole Automation Procedures */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
/*******************************************************/
GO
March 10, 2009 at 12:36 pm
Just as another option if you work at a shop that does not allow Ole Automation to be turned on (some consider it a security risk), you can do the same thing with Powershell without having automation turned on (for that matter you don't need to have SQL on the server at all....if you ever need to keep track of this on non-SQL boxes).
There are lots of really good articles posted by Chad Miller, and with them you can put together all kinds of great information using powershell.
http://www.sqlservercentral.com/Authors/Articles/Chad_Miller/421/
June 17, 2009 at 9:34 am
Nice Query Raymond. Thanks
June 17, 2009 at 1:13 pm
Nice Query Raymond. Thanks
September 13, 2011 at 1:30 pm
Raymond,
the problem is that there are invalid charaters in the string to convert. Just replace the following code :
SELECT drive,
ltrim(rtrim(left(info,29))) as InfoType,
ltrim(rtrim(substring (info, charindex (':',info) + 2, 20))) as Size_Bytes
INTO #tmpFSutilDriveSpaceInfo_Fixed
FROM #tmpFSutilDriveSpaceInfo
with this one :
SELECT drive,
ltrim(rtrim(left(info,29))) as InfoType,
replace(replace(replace(substring (info, charindex (':',info) + 2, 20),' ',''),char(13),''),char(10),'') as Size_Bytes
INTO #tmpFSutilDriveSpaceInfo_Fixed
FROM #tmpFSutilDriveSpaceInfo
and everything should works fine.
February 15, 2012 at 7:15 am
Hi Guys,
I tired to use powershell to get drive space info.
Check this blog http://myharpreet.blogspot.com/2012/01/t-sql-to-get-drive-space-info.html
Harpreet
February 15, 2012 at 12:23 pm
harpreets.singh (2/15/2012)
I tired to use powershell to get drive space info.
Did it work? 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply