监控 log文件大小的存储过程

1、监控log文件大小超过10g的server 和db
复制代码代码如下:
create procedure db_sendmail_mssqllogsize
as
declare @sql varchar（max）,@servername varchar（50）,@px int;
DECLARE @xml NVARCHAR（MAX）
DECLARE @body NVARCHAR（MAX）
declare @a varchar（200）
declare @c datetime
select @c=getdate（）;
select
hostname
,px=ROW_NUMBER（）over（order by （select 1））
into #temp
from DB_ALLHostInfo as x1
inner join sys.servers as x2
on x1.hostname=x2.name
where isactive=1;
select @px=px,@servername=hostname from #temp where px=1;
while @@ROWCOUNT>0
begin
set @sql=
"insert into db_alldb_logsize
select *,cast（convert（char（8）,GETDATE（）,112） as datetime） as gdate ,"""+@servername+""" from openquery（"+QUOTENAME（@servername）+",""select DB_NAME（database_id） as dbname,name as logname,round（cast（size as float）*8/1024,3） as [logsize（mb）],state_desc,physi
cal_name, is_percent_growth,growth,max_size
from
master.sys.master_files
where type_desc=""""log""""
and DB_NAME（database_id） not in（""""master"""",""""tempdb"""",""""msdb"""",""""reportserver"""",""""reportservertempdb"""",""""distribution"""",""""model""""）
and DATABASEPROPERTY（DB_NAME（database_id）,""""IsReadOnly""""）<>1""） as b;"
execute（@sql）
select 1
select top（1） @px=px,@servername=hostname from #temp where px>@px
end;
set @xml=cast（（select J.servername as "td","", isnull（J.dbname,""） as "td","", J.logname as "td","" ,cast（[logsize（mb）] as varchar（20））as "td",""
,state_desc as "td",""
,physical_name as "td",""
,case when max_size =0 then "不允许增长" when max_size=-1 then "文件将一直增长到磁盘变满为止" when max_size=268435456 then " 日志文件将增长到最大大小 2 TB" end as "td",""
,case when is_percent_growth =1 then "以按百分比"+cast（growth AS varchar（3））+"%" when is_percent_growth =0 then " 以按大小"+cast（growth*8/1024 AS varchar（50））+"（mb）增长" end as "td",""
,convert（char（8）,gdate,112） as "td",""
from db_alldb_logsize as j where gdate=cast（CONVERT（char（8）,getdate（）,112） as datetime） and [logsize（mb）]>=10240
FOR XML PATH（"tr"）, ELEMENTS） AS NVARCHAR（MAX）） ;
set @a="Mssqllog运行结果_"+convert （varchar（50）,convert（varchar（5）,YEAR（getdate（）-1））+"年"+convert（varchar（2）,month（getdate（）-1））+"月"+convert（varchar（2）,day（getdate（）-1）））+"日"
--print @a
SET @body ="<html><H1>"+convert（varchar（5）,YEAR（getdate（）））+"年"+convert（varchar（2）,month（getdate（）））+"月"+convert（varchar（2）,day（getdate（）））+"Mssqllog运行结果</H1><body bgcolor=#E3F6CE><table border =1>
<tr><th>服务器ip</th><th>数据库</th><th>日志文件名</th><th>日志文件大小（mb）</th><th>状态</th><th>物理路径</th><th>增长类型</th><th>增长状态</th><th>日期</th>"
if @xml is not null SET @body = @body + @xml +"</table></body></html>"
EXEC msdb.dbo.sp_send_dbmail
@recipients =N"xuwj@5173.com",
@body = @body,
@body_format ="HTML",
@subject =@a,
@profile_name ="profile1"
execute db_sendmail_mssqllogsize

2 邮件

3配上简单的报表