SQL Server Specs of Hard Drive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Server Specs of Hard Drive

Author

Topic

ejbatu
Starting Member

21 Posts

Posted - 2010-09-14 : 08:37:35

Hi,

Is there a SQL Server script that gives information about a hard drive, like the size of the Hard Drive, Free space and how much of the hard drive is used?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 09:29:02

Try

EXEC xp_cmdshell 'Dir '

Madhivanan

Failing to plan is Planning to fail

llinares
Starting Member

3 Posts

Posted - 2010-09-14 : 10:32:13

xp_fixeddrives will give you a list if drives with avaialable space but not the size of the drives.

mikgri
Starting Member

39 Posts

Posted - 2010-09-14 : 14:28:18

you can use code below it will give you total size of drives as well and then you can compare free space against total size

--declare variables
DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20)

DECLARE @MB bigint
SET @MB = 1048576
--create temp table
CREATE TABLE #drives (drive char(1), FreeSpace int, TotalSize int);

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives; --extended stored procedure

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
--cursor through drive letter
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 'FreeMB', TotalSize as 'TotalMB',
CAST((FreeSpace/(TotalSize * 1.0))* 100.0 as int) as 'FreeSpace' --%
FROM #drives ORDER BY drive

DROP TABLE #drives

Yo y los mios
Starting Member

1 Post

Posted - 2010-09-15 : 02:36:09

exec sp_configure 'xp_cmdshell',1
reconfigure
go
declare @cmd varchar(30)
declare @tbl_get_fsutil table (spaces varchar(max))
set @cmd='fsutil volume diskfree C:'
insert into @tbl_get_fsutil EXEC xp_cmdshell @cmd
select [Total free bytes]=sum(case when spaces like '%of free bytes%' then convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) else 0 end )
,[Total avail free bytes]=sum(case when spaces like '%of avail free bytes%' then convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) else 0 end)
,[Total of bytes]=sum(case when spaces like '%of bytes%' then convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) else 0 end )
from @tbl_get_fsutil
where spaces is not null
go
exec sp_configure 'xp_cmdshell',0
reconfigure

vinitnri
Starting Member

3 Posts

Posted - 2010-09-15 : 07:55:27

hey dear if y read about the surface area configuration...your all doubt will be clear....as your request..

Thanks
vinit

vinit k.k.

ejbatu
Starting Member

21 Posts

Posted - 2010-09-15 : 10:56:30

I like the script below, but Total free bytes and Total avail free bytes are the same... what is the best way to get the Total Used bytes?

How would I get all the drives? For example exec xp_fixeddrives gives free space available for all drives available, but the script below gives data information only on the C drive.

Also, is there any way to modify the script to show in MB and GB... of example, if the data is below 1024 MB display in MB and above 1024MB in GB?

exec sp_configure 'xp_cmdshell',1
reconfigure
go
declare @cmd varchar(30)
declare @tbl_get_fsutil table (spaces varchar(max))
set @cmd='fsutil volume diskfree C:'
insert into @tbl_get_fsutil EXEC xp_cmdshell @cmd
select [Total free bytes]=sum(case when spaces like '%of free bytes%' then convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) else 0 end )
,[Total avail free bytes]=sum(case when spaces like '%of avail free bytes%' then convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) else 0 end)
,[Total of bytes]=sum(case when spaces like '%of bytes%' then convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) else 0 end )
from @tbl_get_fsutil
where spaces is not null
go
exec sp_configure 'xp_cmdshell',0
reconfigure

mikgri
Starting Member

39 Posts

Posted - 2010-09-15 : 15:30:06

what if you have more then one drive?
convert(float,rtrim(ltrim(SUBSTRING ( spaces,CHARINDEX(':', spaces)+1,10000)))) will will not work it will fail to convert varchar to float.

mikgri
Starting Member

39 Posts

Posted - 2010-09-15 : 15:47:02

on 9/14/2010 i posted code how to get drive info, to run that code first run this:
-- you can replace [public] with [domainname\username]
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
GO
sp_configure 'show advanced options', 1
GO
reconfigure
GO
exec sp_configure 'Ole Automation Procedures', 1
GO
reconfigure
go

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 16:27:10

quote:
Originally posted by madhivanan

Try

EXEC xp_cmdshell 'Dir '

Madhivanan

Failing to plan is Planning to fail

ROFLMAO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources