Help on Stored Proc

Question

Help on Stored Proc

CDBA

Hall of Fame

Points: 3266
More actions
December 4, 2006 at 3:37 pm

#175550

Hi
Could some one kindly tell me the SP name which will return all the local drives and the total space of the box?
Thanks in advance.
Regards
Utsab Chattopadhyay

CDBA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

There is no procedure to get the total space of local drives where as you can get free space available info by running master.dbo.xp-fixeddrives procedure.

MohammedU
Microsoft SQL Server MVP

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

Aaron Ingold

SSCrazy Eights

Points: 9070

December 4, 2006 at 5:04 pm

#675674

You're probably thinking about xp_fixeddrives.

CDBA Hall of Fame Points: 3266 More actions · Answer 3

Thanks guys.. but I am not looking for xp_fixeddrives.. As it cannt return the total space. I am designing an alert system which will monitor the server's hard disc from SQL Server's job only. So I need to take the total disc size in variables using TSQL. Any guess how to proceed?

Thanks in advance.

Regards

Utsab Chattopadhyay

CDBA

MohammedU SSCoach Points: 19644 More actions · Answer 4

Here is the script...

Declare @BytesUsed Varchar(1000),

@BytesFree Varchar(1000),

@TotalBytes BIGINT,

@IDENTITY INT,

@Drive Char(1),

@sql Varchar(1000)

SET NOCOUNT ON

Create table ##DiskSpace ( Drive Char(1), TotalSpace Bigint, FreeSpace Bigint,

PercentageFree as (FreeSpace*100 / TotalSpace ) )

Create table #Fixeddrives ( Drive Char(1), FreeSpace Bigint)

create table ##Dir ( ID INT IDENTITY , DriveSize Varchar(2000))

Insert into #Fixeddrives exec xp_fixeddrives

--select * from #Fixeddrives

insert into ##DiskSpace ( Drive , FreeSpace)

select Drive , FreeSpace from #Fixeddrives

-- select * from ##DiskSpace

DECLARE Drive_cursor CURSOR FOR

SELECT Drive from ##DiskSpace

OPEN Drive_cursor

FETCH NEXT FROM Drive_cursor INTO @drive

WHILE @@FETCH_STATUS = 0

BEGIN

select @sql = 'insert into ##Dir exec xp_cmdshell ''dir '+ @drive+':\ /S /-C'''

exec(@sql)

SELECT @IDENTITY = @@IDENTITY

delete from ##Dir where ID < @IDENTITY - 4

select @BytesUsed = substring (drivesize, charIndex ('File(s)', drivesize, 0)+ 9 , 1000)

from ##Dir where drivesize like '%File(s)%'

while patindex('%[^0-9]%', @BytesUsed) > 0

begin

set @BytesUsed = stuff( @BytesUsed, patindex('%[^0-9]%', @BytesUsed), 1, '' )

end

select @BytesFree = substring (drivesize, charIndex ('Dir(s)', drivesize, 0)+ 9 , 1000)

from ##Dir where drivesize like '%Dir(s)%'

while patindex('%[^0-9]%', @BytesFree) > 0

begin

set @BytesFree = stuff( @BytesFree, patindex('%[^0-9]%', @BytesFree), 1, '' )

end

select @TotalBytes = Convert(bigint, @BytesUsed)+ Convert(bigint, @BytesFree)

select @TotalBytes = (@TotalBytes/ 1024)/1024 -- Coverting to MB....

-- select @TotalBytes

Update ##DiskSpace set TotalSpace = @TotalBytes

WHERE Drive = @drive

TRUNCATE TABLE ##Dir

FETCH NEXT FROM Drive_cursor INTO @drive

END

CLOSE Drive_cursor

DEALLOCATE Drive_cursor

Select Drive, TotalSpace as 'TotalSpace(MB)', FreeSpace as 'FreeSpace(MB)',

PercentageFree as '%Free' from ##DiskSpace

DROP TABLE ##Dir

DROP TABLE ##DiskSpace

DROP TABLE #Fixeddrives

MohammedU
Microsoft SQL Server MVP

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

While this can be done in SQL Server, SQL Server and Agent aren't probably the best tools for this. There are tools, some free, which already do this, though. For instance, HP System Insight Manager will do this for HP servers if the Insight Management Agents are installed. That's but one example.

K. Brian Kelley
@kbriankelley

CDBA Hall of Fame Points: 3266 More actions · Answer 6

Thanks a lot.. I used the script and the system is working perfectly now...

Regards

Utsab Chattopadhyay

CDBA