declare @tablehtml nvarchar(max);
declare @sub varchar(200)
select @sub = 'sql server disk space report'
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
set @tablehtml =
n'<html><body bgcolor=white><table width=100% border=1 bordercolor=#66ff99 align=center>'
+ n'<tr bgcolor=#006633 align=left><th colspan=5><font color=white size=+2 face=arial><b>sql agent physical space report -'+space(1)+ltrim(upper(@@servername))+space(1)+'-'+space(1)+convert(varchar(11),
getdate(), 103)+'</th></tr>'
+ n'<tr bgcolor=#33cc66 align=left>'+ n'<th><font color=white size =+1 face=arial><b>server name</th>'+ n'<th><font color=white size =+1 face=arial><b>partition</b> '+ n'<th><font color=white size =+1 face=arial><b>free space</th> '
+ n'<th><font color=white size =+1 face=arial><b>total space</th> ' + n'<th><font color=white size =+1 face=arial><b>%age free space</b></th></tr> '
+ n'<tr><td bgcolor=yellow font face=arial size=+1>'
+ cast((
select td =
ltrim(upper(@@servername)), '',
td = ltrim(upper(drive))+'\:', '',
td = convert(varchar(20), convert(decimal(18,2), freespace/1024))+space(1)+'gb', '', td = convert(varchar(20), convert(decimal(18,2), totalsize/1024))+space(1)+'gb', '', td = case when cast((freespace/(totalsize*1.0))*100.0 as int) < 50 then
convert(varchar(20), cast((freespace/(totalsize*1.0))*100.0 as int))+space(1)+'%'
else convert(varchar(20), cast((freespace/(totalsize*1.0))*100.0 as
int))+space(1)+'%'
end , ''
from #drives
order by drive
for xml path('tr'), type) as nvarchar(max) ) +
n' ' + n'</table><br></br>'+ n'
<center><font color=gray size =-1><a href="http://uk-sql.blogspot.com/" target="_new">blog</a>'
+ n'</body></html>' ;
exec msdb.dbo.sp_send_dbmail
@profile_name = 'someprofilename',
@recipients = 'someone@somewhere.com;someoneelse@somewhereelse.com',
@subject = @sub,
@body = @tablehtml,
@body_format = 'html';
drop table #drives
Posts
No comments:
Post a Comment