1, monitor log file size of more than 10g server and DB
Copy Code code as follows:
CREATE PROCEDURE Db_sendmail_mssqllogsize
As
DECLARE @sql varchar (max), @servername varchar (m), @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 (' ', ', ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ', ' ' ReportServerTempDB ', ', ' distribution ', ' ', ' ', ' ' model '
and Databaseproperty (db_name (database_id), ' isreadonly ') <>1 ') as B; '
Execute (@sql)
Select 1
Select Top (1) @px =px, @servername the =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)] A s varchar as ' TD ', '
, State_desc as ' TD ', '
, physical_name as ' TD ', '
, case when max_size =0 then ' does not allow growth ' when max_size=-1 then ' file will grow until the disk becomes full ' when max_size=268435456 then ' log file will grow to a maximum size of 2 T B ' end as ' TD ', '
, case when Is_percent_growth =1 then ' in percent ' +cast (growth as varchar (3)) + '% ' when is_percent_growth =0 then ' to Size ' +cast (gr owth*8/1024 as varchar) + ' (MB) growth ' 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 Run Results _ ' +convert (varchar), CONVERT (varchar (5), Year (GETDATE ()-1)) + ' years ' +convert (varchar (2), Month ( GETDATE ()-1)) + ' month ' +convert (varchar (2), Day (GETDATE ()-1)) + ' th '
--print @a
SET @body = ' <tr><th> Server ip</th><th> Database </th><th> log file name </th><th> log file size (MB) < /th><th> State </th><th> physical path </th><th> growth type </th><th> growth status </th>< th> Date </th> '
If @xml is not null SET @body = @body + @xml + ' </table></body>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 Mail
3 with a simple report
