SQL Server Performance Forum – Threads Archive

job to check the disk space in that box

Hi everyone
I am looking for the scripts that will check the drive space on my server for all the drive and if it is 80% fill I would like it to email to me. I need to put this script on the job and execute every 1 hour. I am new to SQL server and null in VB scripts
if anyone can provide me the solution than I will be greatful
Thanks in advance

---

You can modify the following script as per your requirement…this is totally tsql no VB… 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 whereID < @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 * FROM #DiskSpace
DROP TABLE ##Dir
DROP TABLE #DiskSpace
DROP TABLE #Fixeddrives MohammedU.
Moderator
SQL-Server-Performance.com

---

You can create a SSIS package and schedule the same thru SQLagent.
Database mail will be handy to email such information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Thanks guys, I will work on it

---

You can also use "execute xp_fixeddrives" This will give you free space on your each hard drive.

---

xp-fixeddrives only gives you free space available but not the drive space details…
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>