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