Stored Procedure for monitoring log File Size

1. Monitor servers and databases whose log files are larger than 10 GB
Copy codeThe Code is as follows:
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 (converter (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 'The 'when max_size =-1 then' file cannot grow until the disk is full. 'When max_size = 268435456 then' log file will grow to the maximum size 2 TB 'end as 'td ',''
, Case when is_percent_growth = 1 then 'in percentage + cast (growth AS varchar (3 )) + '%' when is_percent_growth = 0 then 'By size' + cast (growth * 8/1024 AS varchar (50) + '(mb) Increase '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 = 'sqllog running result _ '+ convert (varchar (50), convert (varchar (5), YEAR (getdate ()-1 )) + 'Year' + convert (varchar (2), month (getdate ()-1) + 'month' + convert (varchar (2), day (getdate () -1) + 'day'
-- Print @
SET @ body = '<Tr> <th> Server ip address </th> <th> database </th> <th> log file name </th> <th> log file size (mb) </th> <th> Status </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 = @,
@ Profile_name = 'profile1'
Execute db_sendmail_mssqllogsize

2 emails

 

3. Simple reports