March 2, 2012 at 5:04 am
I am with Grasshopper, since I get strange results from some servers. The Total Space is not correct for those servers HDDs. I am wondering why this is so?
March 2, 2012 at 7:47 am
Keep in mind this thread was started almost 8 YEARS ago with the most current responses about 3 years ago.
None of these methods work when using mount-points.
Although I'm not sure I wonder if the datatypes should be bigint and not int.. This should probably be started as a new thread if you want to continue the discussion.
CEWII
March 2, 2012 at 1:19 pm
That is correct and I have been using WMI which proves to be intermittent in it's functionality in large environments with security permissions. Thanks for the reply
Goaler...:hehe:
goaler@comcast.net
March 3, 2012 at 9:22 am
Does anyone have a proper tsql or other script to obtain Total Drive Space in GB that works against remote servers e.g. Linked Servers? Will you share it? To my knowledge none of the 26 server uses mount points.
March 3, 2012 at 9:50 am
cchitanu (4/28/2004)
Send me an email at cchitanu@csc.com
How'z'bout you just post your solution here, instead? 😉
{EDIT}... heh... 8 year old post. I'd still like to see what was going to be offered here. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2012 at 10:36 am
I see you guys are adament that I must start a new post for the same topic. I find it interesting that we are in 2012 and from 2000 to this day MS still hasn't added the Total Drive Space to xp_fixeddrives through a SP. Maybe I'll start a new thread, hopefully someone has an actual working solution... Anyone want to punch me another blow? Go right ahead! It still does not resolve the Total Drive Space issue.
May 10, 2012 at 7:22 am
This is a great piece of script..
Thanks
May 10, 2012 at 7:46 am
Paul Els (3/3/2012)
I see you guys are adament that I must start a new post for the same topic. I find it interesting that we are in 2012 and from 2000 to this day MS still hasn't added the Total Drive Space to xp_fixeddrives through a SP. Maybe I'll start a new thread, hopefully someone has an actual working solution... Anyone want to punch me another blow? Go right ahead! It still does not resolve the Total Drive Space issue.
Not by me. I'd like to see the answer right here... no new post required. I think its interesting that a problem could go on for so long without a good answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 7:23 am
I agree that this is a perfect topic to continue, love to look at the history without having to search related stuff up.
But here is another twist to this for everyone to think about. What about those situations that you cannot use any OLE automation or xp_cmdshell solutions? My company locks everything down due to strict security regulations and audit compliancy, so using something in the SQL Server TSQL scope is not necessarily a solution.
We really need to potentially think outside the box on solutions and look to expand our thoughts on other opportunities within SQL Server, maybe calling CLR, SSIS or something on those lines. I am really attempting to get creative here and not just stick with TSQL so any other thoughts are definitely welcome.
Thanks and love the posts
Ron...
Goaler...:hehe:
goaler@comcast.net
July 3, 2012 at 7:41 am
goaler (7/3/2012)
What about those situations that you cannot use any OLE automation or xp_cmdshell solutions? My company locks everything down due to strict security regulations and audit compliancy,...
Heh... I love the irony of it all at companies like that because if they had actually set things up to correctly and securely use things like xp_CmdShell, their security would be bullet proof.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 11:37 am
Hi Guys.
I have found a solution to this age old problem. Now I will just explain the cocept, in my next post I will give the code. I use it at a large bank. It requires that 1 DLL file (Basically you can compile the code yourself, if you like me you trust no one) and then put the DLL in a common folder on each server. I copy it to C:\Windows . Then this must be hooked into SQL. Then my tsql code runs against each instance in a loop. If you cycle through approx 2000 instances, some are bound not to have the .Net FW it requires. For that I handle it with a TRY...CATCH and just display the Free Space. Hence the Total Drive Space is just set to 0 for such servers. I could get creative and create a lookup table to replace the 0 with the size, but I did not want to do that, because some LUN Admin might change the size without me knowing. I would rather want to see 0 GB Total for servers with the .Net FW problem, so that I can address the .Net FW issue on such servers. I still use the xp_fixeddrives so I could not get away from this limitation yet.
Anyway, I have to run, but I will come back and what code I use to do the trick. I must say, I didn't write the CSharp code, but it works pretty good.
July 3, 2012 at 11:57 am
Paul Els (7/3/2012)
Hi Guys.I have found a solution to this age old problem.
BWAAAA-HAAAA!!! Me too but most people don't have a system secure enough to use it because I use xp_CmdShell to call PowerShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 3:51 pm
For those interested I will list the steps. It might be a bit over the top, but here goes...
If you run into trouble, let me know where you're stuck and I will try to help you to get it going, but please try it on some Dev Servers first... My time is very limited, so only if you're really stuck, let me know. I advise you do this piece-meal (e.g. chunk by chunk on 4 dev servers to start with):
-- First you will want to ensure the 3 settings
-- (show advanced options, xp_cmdshell, clr enabled) are enabled:
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
-- Next, you will need to copy the TotalDriveSpace.dll file to
-- a common folder on each Server, e.g. C:\Windows
-- So either compile the CSharp code and drop it somewhere
-- in a shared folder (e.g. \\HostName\ShareName\FolderName\TotalDriveSize.dll)
-- Or pop me an email (paul_els@hotmail.com) to send you the compiled TotalDriveSpace.dll file
-- Then run this and with a bit of luck (and access rights) your SQL server
-- will copy the file to it's C:\Windows folder.
-- If this fails (e.g. Access Denied), contact your SysAdmin to copy it there for you
exec master..xp_cmdshell
'copy \\HostName\ShareName\FolderName\TotalDriveSize.dll C:\Windows'
-- To verify that the file is in place you can execute a simple DOS Dir:
exec master..xp_cmdshell 'dir C:\Windows\TotalDriveSize.dll'
The CSharp dll code I got from:
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
On each each of my Servers I have a database in which I store my library code.
This can contain Stored Procs, Functions, etc. I call it SQLMaintenance.
So create a tiny blank database called SQLMaintenance on each of the 4 Dev Servers.
-- THEN SET IT TRUSTWORTHY
ALTER DATABASE SQLMaintenance SET TRUSTWORTHY ON
GO
-- Now we create an assemly from the .dll
--drop ASSEMBLY DiskSpace
CREATE ASSEMBLY DiskSpace
FROM 'C:\WINDOWS\TotalDriveSize.dll'
WITH PERMISSION_SET = UNSAFE
GO
-- Make some sql account dbo
sp_changedbowner 'SomeSQLLoginPutYourOwnSQLLoginHere'
Go
-- drop PROC dbo.isp_DiskSpace
CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace
GO
-- At this stage manually create a linked server to each of your 4 Dev Servers:
-- Once the Linked Servers are created CONFIGURE THE LINKED SERVER OPTIONS
-- for each respective instance
USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'YourDevInstanceName', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'YourDevInstanceName', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'YourDevInstanceName', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
-- After all this "Hard" work, now it is time for the fun to start
-- Below the first line shows for a named instance an example
-- Below the second line shows for a default instance an example
EXEC [DevServer1\InstanceName].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer1'
EXEC [DevServer2].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer2'
EXEC [DevServer3\InstanceName].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer3'
EXEC [DevServer4\InstanceName].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer4'
Another option would be to try and do this whole thing via SSIS. The advantage there is that you don't need to create linked servers. You can e.g. create a table with all the Instance Names. Then cycle through the list and connect to each server in a Loop and execute code against it on the fly.
On the Central Server:
USE [SQLMaintenance]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Create a procedure to retrieve error information.
CREATE PROCEDURE [dbo].[usp_GetErrorInfo]
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
It is my hope that this, although a lot of work, will help you guys to pull better reports.
From a Central Server, which must also have a SQLMaitenance database you can now do a bit of magic and cycle through each server, to collect the Total Drive Space and Free Space and then build and send 1 email to a number of people to address it. Notice in the method below, to send an email I have to put the built message in a queue... you may have to handle email differently.
USE [SQLMaintenance]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Report_TotalDriveSpaceAndFreeSpace_via_email]
AS
BEGIN
-- THE PURPOSE OF THIS SCRIPT IS TO POPULATE A TABLE WITH THE FREE SPACE PER DRIVE/LUN
-- THEN TO BUILD A HTML EMAIL AND SEND IT
-- TEST: EXEC SQLMaintenance.dbo.sp_Report_TotalDriveSpaceAndFreeSpace_via_email
set nocount on --create table @Tmp (DriveLetter char(1), MBFree int)
declare @ServerNameAndErrorDetails table (SvrName varchar(128),
fErrorNumber INT,
fErrorSeverity INT,
fErrorState INT,
fErrorProcedure VARCHAR(1024),
fErrorLine INT,
fErrorMessage VARCHAR(1024))
DECLARE @DynSQL varchar(max) = ''
DECLARE @DynSQL2 varchar(max) = ''
DECLARE @InstanceName VARCHAR(128) = ''
DECLARE @ServerName VARCHAR(128) = ''
IF NOT EXISTS (SELECT NAME FROM sys.objects where TYPE = 'U' and name = 'TotalDriveSpaceMain')
BEGIN
CREATE TABLE TotalDriveSpaceMain (DT Date, ServerName varchar(128), Drive varchar(255), TotalGB decimal(18,2), UsedGB decimal(18,2), FreeGB decimal(18,2), PercentFreeSpace decimal(18,2))
END
declare @Tmp table (DriveLetter varchar(255), MBFree int)
declare @svr_drv_mbfree table (SvrName varchar(128), DriveLetter varchar(255), MBFree int)
IF EXISTS (SELECT NAME FROM sys.objects where TYPE = 'U' and name = 'tmpTotalDriveSpace')
BEGIN
DROP TABLE tmpTotalDriveSpace
END
CREATE TABLE tmpTotalDriveSpace (Drive varchar(255), TotalMB decimal(18,2), UsedMB decimal(18,2), FreeMB decimal(18,2), PercentFreeSpace decimal(18,2))
DELETE FROM TotalDriveSpaceMain --WHERE DT = CONVERT(VARCHAR(10), GETDATE(), 121)
DECLARE InstancesCursor CURSOR
LOCAL FORWARD_ONLY READ_ONLY -- MAKE THE CURSOR LIGHT WIGHT
FOR -- Please pre-populate the Instances Table with a list of the instances you want to monitor
select InstanceName from SQLMaintenance.dbo.Instances
OPEN InstancesCursor
FETCH NEXT FROM InstancesCursor
INTO @InstanceName
WHILE @@FETCH_STATUS = 0
BEGIN
--------------------------------------------------------------------------------------
-- POPULATE NEXT SERVER
--------------------------------------------------------------------------------------
BEGIN TRY
select @ServerName = '' --RESET VARIABLE
select @DynSQL = 'EXEC [InstanceNme].master.dbo.xp_fixeddrives'
select @DynSQL = REPLACE(@DynSQL, 'InstanceNme', @InstanceName)
delete from @Tmp
insert into @Tmp
EXEC (@DynSQL)
insert into @svr_drv_mbfree
select @InstanceName, * from @Tmp
select @DynSQL = 'EXEC [InstanceNme].SQLMaintenance.dbo.isp_DiskSpace @ServerName = #ServerNme#'
select @DynSQL = REPLACE(@DynSQL, 'InstanceNme', @InstanceName)
if CHARINDEX('\', @InstanceName, 1) > 0 -- IF A BACKSLASH IS FOUND, SET @ServerName = part before \
BEGIN
select @ServerName = LEFT(@InstanceName, CHARINDEX('\', @InstanceName, 1) )
select @ServerName = REPLACE(@ServerName, '\', '') -- REMOVE THE \
END
else
BEGIN
select @ServerName = @InstanceName
END
select @DynSQL = REPLACE(@DynSQL, 'ServerNme', @ServerName)
select @DynSQL = REPLACE(@DynSQL, '#', CHAR(39))
-- FLUSH AND REPOPULATE TEMP TABLE
DELETE FROM tmpTotalDriveSpace
-- TRY CATCH
BEGIN TRY
INSERT INTO tmpTotalDriveSpace
EXEC (@DynSQL)
END TRY
BEGIN CATCH
--*** ERROR HANDLING ***'
select @DynSQL2 = 'EXEC [InstanceName].master.dbo.xp_fixeddrives'
select @DynSQL2 = REPLACE(@DynSQL2, 'InstanceName', @InstanceName)
create table #xp_fixeddrives (Drive CHAR(2), MBFree int)
-- GATHER THE FREE SPACE
insert into #xp_fixeddrives
EXEC (@DynSQL2)
-- ADD THE COLON
UPDATE #xp_fixeddrives set Drive = LEFT(Drive,1) + ':'
-- RECORD THE DATA
INSERT INTO tmpTotalDriveSpace
select Drive, 0 as [Capacity (MB)], 0 as [Used Space (MB)], MBFree as [Free Space (MB)], 0 as [Percent Free Space] from #xp_fixeddrives
drop table #xp_fixeddrives
END CATCH
-- APPEND TEMP TABLE ROWS FOR INSTANCE @InstanceName TO TotalDriveSpaceMain
INSERT INTO TotalDriveSpaceMain (DT, ServerName, Drive, TotalGB, UsedGB, FreeGB, PercentFreeSpace)
select CONVERT(VARCHAR(10), GETDATE(), 121),
@InstanceName,
Drive,
TotalMB / 1024, -- CONVERT TO GB
UsedMB / 1024, -- CONVERT TO GB
FreeMB / 1024, -- CONVERT TO GB
PercentFreeSpace
from tmpTotalDriveSpace
order by Drive
END TRY
BEGIN CATCH
-- RETRIEVE THE ERROR AND ADD IT TO THE MEMORY TABLE @ServerNameAndErrorDetails
INSERT INTO @ServerNameAndErrorDetails
SELECT @InstanceName,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage
END CATCH
-- PROCESS THE NEXT RECORD
FETCH NEXT FROM InstancesCursor INTO @InstanceName
END
CLOSE InstancesCursor
DEALLOCATE InstancesCursor
-- ONLY SHOW ERROR IF THERE WERE ANY
if (select COUNT(*) from @ServerNameAndErrorDetails) > 0
BEGIN
select * from @ServerNameAndErrorDetails
END
----------------------------------------------------
-- POPULATE #tmp_status which can later be used to manipulate STATUS
----------------------------------------------------
--select DT, ServerName, Drive, TotalGB, UsedGB, FreeGB, PercentFreeSpace from
-- TotalDriveSpaceMain
BEGIN TRY
select [ServerName], Drive as [DriveLetter], FreeGB,
'STATUS' =
CASE
WHEN FreeGB > 10 THEN 'GREEN'
WHEN FreeGB <= 5 THEN 'RED'
WHEN FreeGB <= 10 AND FreeGB > 5 THEN 'ORANGE'
END
into #tmp_status
from TotalDriveSpaceMain
order by 1,2
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE SQLMaintenance.dbo.usp_GetErrorInfo;
END CATCH
---- BUILD HTML AND EMAIL RESULT SET
BEGIN TRY
DECLARE @STATEMENT varchar(max)
SELECT @STATEMENT = ''
DECLARE @DriveLetter char(1)
DECLARE @GB_Free int
DECLARE @TotalGB int
DECLARE @status varchar(50)
--select DT, ServerName, Drive, TotalGB, UsedGB, FreeGB, PercentFreeSpace from TotalDriveSpaceMain
DECLARE Cursor1 CURSOR
LOCAL FORWARD_ONLY READ_ONLY -- MAKE THE CURSOR LIGHT WIGHT
FOR
SELECT a.[ServerName], a.DriveLetter,
CONVERT(INT, a.FreeGB) as [FreeGB],
CONVERT(INT, b.TotalGB) as [TotalGB],
a.[STATUS] -- Status moved 1 to the right to make space for TotalGB
FROM #tmp_status a left join dbo.TotalDriveSpaceMain b
on a.ServerName = b.ServerName
and ltrim(rtrim(left(a.DriveLetter,1))) = ltrim(rtrim(left(b.Drive,1)))
ORDER BY 3,1,2
OPEN Cursor1
FETCH NEXT FROM Cursor1
INTO @ServerName, @DriveLetter, @GB_Free, @TotalGB, @status
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build HTML records with colour using a cursor
IF @status = 'RED'
BEGIN
SELECT @STATEMENT = @STATEMENT +
'<tr BGCOLOR="#FF4848">
<td>' + @ServerName + '</td><td>' + @DriveLetter + '</td><td>' + str(@GB_Free) + '</td><td>' + str(CONVERT(VARCHAR(20), ISNULL(@TotalGB, 0))) + '</td><td>RED</td>
</tr>'
END
ELSE
IF @status = 'ORANGE'
BEGIN
SELECT @STATEMENT = @STATEMENT +
'<tr BGCOLOR="#CC9933">
<td>' + @ServerName + '</td><td>' + @DriveLetter + '</td><td>' + str(@GB_Free) + '</td><td>' + str(CONVERT(VARCHAR(20), ISNULL(@TotalGB, 0))) + '</td><td>ORANGE</td>
</tr>'
END
ELSE
IF @status = 'GREEN'
BEGIN
SELECT @STATEMENT = @STATEMENT +
'<tr BGCOLOR="#1FCB4A">
<td>' + @ServerName + '</td><td>' + @DriveLetter + '</td><td>' + str(@GB_Free) + '</td><td>' +str(CONVERT(VARCHAR(20), ISNULL(@TotalGB, 0))) + '</td><td>GREEN</td>
</tr>'
END
FETCH NEXT FROM Cursor1 INTO @ServerName, @DriveLetter, @GB_Free, @TotalGB, @status
END
-- <td>' + @ServerName + '</td><td>' + @DriveLetter + '</td><td>' + str(@GB_Free) + '</td><td>' + str(ISNULL(@TotalGB,'?')) + '</td><td>RED</td>
CLOSE Cursor1
DEALLOCATE Cursor1
END TRY
BEGIN CATCH
-- EXECUTE THE ERROR RETRIEVAL ROUTINE.
EXECUTE SQLMaintenance.dbo.usp_GetErrorInfo;
END CATCH
----------------------------------------------------
--select * from #tmp_status
--select * from TotalDriveSpaceMain
---- BUILD HTML RE ERRORS
DECLARE @ConnectionErros TinyInt
BEGIN TRY
DECLARE @STATEMENT2 varchar(max)
SELECT @STATEMENT2 = ''
DECLARE @SvrName varchar(128)
DECLARE @ErrorMessage VARCHAR(255)
DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorProcedure VARCHAR(255)
DECLARE @ErrorLine INT
DECLARE Cursor2 CURSOR
LOCAL FORWARD_ONLY READ_ONLY -- MAKE THE CURSOR LIGHT WIGHT
FOR
SELECT SvrName, fErrorMessage, fErrorNumber, fErrorSeverity, fErrorState, fErrorProcedure, fErrorLine
FROM @ServerNameAndErrorDetails
ORDER BY 1,2
OPEN Cursor2
FETCH NEXT FROM Cursor2
INTO @SvrName, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
WHILE @@FETCH_STATUS = 0
BEGIN
-- build html records with colour using a cursor
SELECT @STATEMENT2 = @STATEMENT2 +
'<tr BGCOLOR="#FF4848">
<td>' + @SvrName + '</td><td>' + @ErrorMessage + '</td><td>' + STR(@ErrorNumber) + '</td><td>' + STR(@ErrorSeverity) + '</td><td>' + STR(@ErrorState) + '</td><td>' + LEFT(CONVERT(VARCHAR(255),ISNULL(@ErrorProcedure,'')),50) + '</td><td>' + STR(@ErrorLine) + '</td>
</tr>'
SELECT @ConnectionErros = @ConnectionErros + 1
FETCH NEXT FROM Cursor2 INTO @SvrName, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
END
CLOSE Cursor2
DEALLOCATE Cursor2
END TRY
BEGIN CATCH
-- EXECUTE THE ERROR RETRIEVAL ROUTINE.
EXECUTE SQLMaintenance.dbo.usp_GetErrorInfo;
END CATCH
----------------------------------------------------
---- CONCAT Final HTML message
BEGIN TRY
-- SvrName, fErrorMessage, fErrorNumber, fErrorSeverity, fErrorState, fErrorProcedure, fErrorLine FROM @ServerNameAndErrorDetails
declare @html varchar(max)
if @ConnectionErros > 0
BEGIN
select @html = '<html>
<head>
<title> FREE SPACE BY VOLUME </title>
<body>
<body bgcolor="#FFFFFF">
<table border="1" cellspacing="3" cellpadding="10">
<tr>
<th BGCOLOR="#3300CC">Server Name</th>
<th BGCOLOR="#3300CC">Error Message</th>
<th BGCOLOR="#3300CC">Error Number</th>
<th BGCOLOR="#3300CC">Error Severity</th>
<th BGCOLOR="#3300CC">Error State</th>
<th BGCOLOR="#3300CC">Error Procedure</th>
<th BGCOLOR="#3300CC">Error Line</th>
</tr>
' + @STATEMENT2 + '
</table>
<tr>
Take Note: Servers where "GB Total" cannot be determined via C#.Net the "GB Total" column will be 0.
</tr>
<tr>
On a positive note, the "GB Free" is still determined for such Servers.
</tr>
<table border="1" cellspacing="3" cellpadding="10">
<tr>
<th BGCOLOR="#3300CC">Server Name</th>
<th BGCOLOR="#3300CC">Drive Letter</th>
<th BGCOLOR="#3300CC">GB Free</th>
<th BGCOLOR="#3300CC">GB Total</th>
<th BGCOLOR="#3300CC">Status</th>
</tr>
' + @STATEMENT + '
</table>
</body>
</html>'
END
ELSE
BEGIN
select @html = '<html>
<head>
<title> FREE SPACE BY VOLUME </title>
<body>
<body bgcolor="#FFFFFF">
<tr>
Take Note: Servers where "GB Total" cannot be determined via C#.Net the "GB Total" column will be 0.
</tr>
<tr>
On a positive note, the "GB Free" is still determined for such Servers.
</tr>
<table border="1" cellspacing="3" cellpadding="10">
<tr>
<th BGCOLOR="#3300CC">Server Name</th>
<th BGCOLOR="#3300CC">Drive Letter</th>
<th BGCOLOR="#3300CC">GB Free</th>
<th BGCOLOR="#3300CC">GB Total</th>
<th BGCOLOR="#3300CC">Status</th>
</tr>
' + @STATEMENT + '
</table>
</body>
</html>'
END
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE SQLMaintenance.dbo.usp_GetErrorInfo;
END CATCH
----------------------------------------------------
DECLARE @FromAddress VARCHAR(50)
SELECT @FromAddress = 'YourFromAddress@YourCompany.com'
---- Send email in HTML format
BEGIN TRY
INSERT INTO [YourEmailQueueServer].[EnterpriseEmailing].[dbo].[Emails]
([FromAddress],[ToAddress],[CCAddress],[BCCAddress],[MailBody],
[MailSubject],[DateRecieved], [SentStatus])
--VALUES (@FromAddress,'Paul_Els@hotmail.com','','',
VALUES (@FromAddress,'Paul_Els@hotmail.com;Joe.Bloggs@YourCompany.com;Harry.Potter@YourCompany.com','','',
@html, 'SQL Maintenance RE: Free Space',GetDate(),0)
PRINT 'e-mail sent'
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE SQLMaintenance.dbo.usp_GetErrorInfo;
END CATCH
-- CLEANUP
BEGIN TRY
drop table #tmp_status
END TRY
BEGIN CATCH
-- EXECUTE THE ERROR RETRIEVAL ROUTINE.
EXECUTE SQLMaintenance.dbo.usp_GetErrorInfo;
END CATCH
END
July 4, 2012 at 4:08 pm
Like Jeff I use xp_cmdshell with PowerShell to query the Win32_Volume WMI class. You wouldn't need xp_cmdshell or PowerShell at all if SQL could issue WQL command, but alas. This gets both total capacity, label and free space information on both fixed drives and mount points:
xp_cmdshell 'powershell -command "get-wmiobject -class win32_volume | select-object -property name, label, capacity, freespace | sort-object -property name | format-table -wrap"'
Joie Andrew
"Since 1982"
June 24, 2014 at 4:53 pm
Wow! What a script!
I took the time to carefully research the OLE object creation, method, and property get calls. You did a LOT of research to get this under the hood data.
I genuinely appreciate your work and willingness to share it. I hope to be able to return the favor some day.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply