Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Looking for the inverse of xp_fixeddrives Expand / Collapse
Author
Message
Posted Saturday, March 3, 2012 10:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:59 AM
Points: 8, Visits: 85
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.
Post #1261159
Posted Thursday, May 10, 2012 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 7:21 AM
Points: 1, Visits: 23
This is a great piece of script..

Thanks
Post #1297859
Posted Thursday, May 10, 2012 7:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1297886
Posted Tuesday, July 3, 2012 7:23 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:14 AM
Points: 3, Visits: 106
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...
goaler@comcast.net
Post #1324380
Posted Tuesday, July 3, 2012 7:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1324399
Posted Tuesday, July 3, 2012 11:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:59 AM
Points: 8, Visits: 85
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.
Post #1324571
Posted Tuesday, July 3, 2012 11:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1324583
Posted Tuesday, July 3, 2012 3:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:59 AM
Points: 8, Visits: 85
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
Post #1324729
Posted Wednesday, July 4, 2012 4:08 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #1325132
Posted Tuesday, June 24, 2014 4:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 258, Visits: 1,093
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.
Post #1585717
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse