disk space Report by email

• c.ajitkumar

  SSC Eights!

  Points: 973

  More actions

  August 17, 2012 at 7:33 am

  #260214

  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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 17, 2012 at 7:52 am

  #1527087

  if you have the query, we can help you html-ize it.

  what have you put together so far?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• durai nagarajan

  SSCoach

  Points: 15778

  More actions

  August 17, 2012 at 8:10 am

  #1527096

  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

  WHEREdrive=@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