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

disk space Report by email Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 7:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:27 AM
Points: 314, Visits: 613
Hi All,

Can any one post a script that will send disk space details in a HTML format by a email in SQL server 2005.


Thanks in advance.


Ajit
Post #1346543
Posted Friday, August 17, 2012 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 12,896, Visits: 32,095
if you have the query, we can help you html-ize it.

what have you put together so far?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1346553
Posted Friday, August 17, 2012 8:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; 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

DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @subject1 NVARCHAR(110)

SET @tableHTML = '<p>Hello All,</p> <p>The drive free space details.</p>'+
'<table border="1" cellspacing="0" cellpadding="0" >' +
'<tr align="center"><td><strong>Server Name</strong></td><td><strong>Date</strong></td>' +
'<td><strong>Drive</strong></td>'+
'<td><strong>Total(GB)</strong></td><td><strong>Free(GB)</strong></td>' +
'<td><strong>Free Space (in %)</strong></td>'+
'</tr><TR align="center">' +
CAST ( ( SELECT td = @@servername, '',
td = convert(varchar,getdate(),106), '',
td = Drive, '',
td = convert(decimal(7,2),TotalSize/1024.0), '',
td = convert(decimal(7,2),FreeSpace/1024.0), '',
td = convert(decimal(7,2),(convert(decimal(7,2),FreeSpace/1024.0)/(TotalSize/1024.0))*100), ''
from #drives
--where convert(decimal(7,2),(convert(decimal(7,2),FreeSpace/1024.0)/(TotalSize/1024.0))*100) < 25.00
FOR XML PATH('TR'), TYPE
) AS NVARCHAR(MAX) ) +
N'</tr></table>'

select @subject1 = @@servername + ' Drive space Details'

if @tableHTML is not NULL and @subject1 is not null
begin
EXEC msdb.dbo.sp_send_dbmail
@body_format='HTML',
@body=@tableHTML,
@recipients=N'EMail id ',
@subject = @subject1,@profile_name ='Profile'

End


DROP TABLE #drives

SET NOCOUNT OFF

this is what i use, change it according to your requirement.



Regards
Durai Nagarajan
Post #1346563
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse