October 23, 2008 at 11:50 am
From T-SQL, I can use xp_FixedDrives to get all drives and MB free on each. But how can I get the total size of the drive?
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 23, 2008 at 12:38 pm
October 23, 2008 at 1:02 pm
WMI would be my preference
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2008 at 1:30 pm
Good thoughts. Thanks for your replies.
What I'm trying to accomplish is to:
a. get a list of drives, free space, and available space. (xp_FixedDrives gets me the drive letter and mb free)
b. get a list of database files for each database, and space used. (sys.database_files has this info for a db, combine w/ sp_MSForEachDB for all db's)
c. put all of this together into an html-formatted email, and email it. (using sp_send_dbmail)
I've also accomplished getting the info from an ActiveX script and putting it into an html file, but sp_send_dbmail can only send the file as an attachment, not as the body of the email.
Ahh, I just figured out how to do this.
1. Create table #temp (FileData varchar(max))
2. bulk insert #temp FROM "FilePath"
3. Select @body = FileData from #temp
4. send email with this variable.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 23, 2008 at 1:49 pm
i just call this on all the servers
DECLARE @hr int
DECLARE @fso int
DECLARE @drive varchar(30)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @freespace varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (drive varchar(30) 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
October 23, 2008 at 1:50 pm
sorry here is the whole procedure
CREATE PROCEDURE sp_diskspace
AS
/*
Displays the free space,free space percentage
plus total drive size for a server
*/
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 @@servername,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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply