• 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