May 15, 2009 at 7:15 am
Hi there,
I have this sql to get the total free space on fixed drive's on my sql server
EXECUTE master..xp_fixeddrives
But that only returns drive letter and free space.. is there another function I can use to get total space on the drive as well? I'd like to calculate the percentage used and percentage free spaces..
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
May 15, 2009 at 7:22 am
What I've done for that was create a table in my DBA database and list the drives and their total space in that table.
I'm sure you can retrieve the data with a CLR function/proc, even if there isn't something already built into SQL Server for it. But, I haven't seen drive letters or total space change often enough to make that necessary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2009 at 7:31 am
I was hoping to avoid manually entering these values in.. lots of sql instances to monitor, and I was hoping to create a standard job I could run (even on sql instances I've never used) and have it pull this data in for me..
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
May 15, 2009 at 7:37 am
If they're all SQL 2005 or 2008, CLR should be able to do that for you pretty efficiently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2009 at 7:40 am
GSquared (5/15/2009)
If they're all SQL 2005 or 2008, CLR should be able to do that for you pretty efficiently.
in a perfect world.. yep.. they'd be 2005/2008 - but.. there are still a few 2000 databases hanging out there.. :
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
May 15, 2009 at 7:45 am
I'd look at VBScript, or perhaps adding Powershell to the servers, and using those to get the space. You can even insert the data into SQL Server from VBScript.
The FileSystemObject will let you manipulate the drives and gather information.
May 15, 2009 at 7:59 am
I think I have a solution, it just requires that OLE Automation be turned on..
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; 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,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
I found the solution here
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply