The stored procedure used to monitor the size of log files. For more information, see.
The stored procedure used to monitor the size of log files. For more information, see.
1. Monitor servers and databases whose log files are larger than 10 GB
The 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 =''+ Convert (varchar (5), YEAR (getdate () + 'Year' + convert (varchar (2), month (getdate ())) + 'month' + convert (varchar (2), day (getdate () + 'sqllog running result
Server ip Address |
Database |
Log File Name |
Log File size (mb) |
Status |
Physical path |
Growth type |
Growth status |
Date | 'If @ xml is not null SET @ body = @ body + @ xml +'
'
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