script to catch log file over 1 GB in size..

Last Post 01 Jun 2009 07:16 PM by foxwindower. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oshaw
New Member
New Member

--
25 Feb 2008 07:12 AM
Even though we backup transaction log files, a few times log files have grown to threatening sizes. This is a little script I wrote to catch a 1GB+ log file size and send an email message to you:

CREATE PROCEDURE dbo.LOGFileMonitor
AS
declare @RowCnt int
declare @MaxFiles int
declare @Email varchar(60)
declare @accountMsg varchar(130)
declare @Name nchar(128)
declare @size int
declare @PhysicalLogFile varchar(260)
declare @LogicalLogFile varchar(128)
declare @dbName varchar(128)
declare @MsgTitle varchar(50)
declare @dbid smallint
declare @today datetime

select @today = DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

select @MaxFiles=max(DBID)
from
sysaltfiles;

set @MsgTitle = @@Servername + ' Log File Size Warning Message'
set @rowCnt=1

While @RowCnt <= @MaxFiles

Begin

Select
@size=fle.size
,@LogicalLogFile=fle.name
,@PhysicalLogFile=fle.filename
,@dbid=fle.dbid
,@dbname=db.name
from
sysaltfiles fle
,sysdatabases db
where
fle.dbid = db.dbid
and fle.dbid = @RowCnt
and fle.name like '%log%';


If cast(@size*8/1024 as varchar(10)) > 1000

Begin

-- set @Email = 'dba'
-- set @Email='shawo'

If @LogicalLogFile like '%log%' and @LogicalLogFile not like '%log1%'
Begin
select
@accountMsg =
' The (logical) log file = ' + rtrim(fle.Name) +
', for database,' + db.name +', has grown in size to ' + cast(@size*8/1024 as varchar(10)) + 'MB.'
from
sysaltfiles fle,
sysdatabases db
Where
(fle.dbid = db.dbid)
and (fle.name like '%log%' and fle.name not like '%log1%')
and (fle.dbid=@RowCnt)

exec sendMail 'SqlServer', 'DBA@emailAddress.com',@MsgTitle,@accountMsg
end

If @LogicalLogFile like '%log1%'
Begin
select
@accountMsg =
' The (logical) log file = ' + rtrim(fle.Name) +
', for database,' + db.name +', has grown in size to ' + cast(@size*8/1024 as varchar(10)) + 'MB.'
from
sysaltfiles fle,
sysdatabases db
Where
(fle.dbid = db.dbid)
and (fle.name like '%log%' and fle.name not like '%log1%')
and (fle.dbid=@RowCnt)

exec sendMail 'SqlServer', 'DBA@emailAddress.com',@MsgTitle,@accountMsg
end


-- print 'Email= ' print @email
-- print 'rowcnt= ' print @rowCnt
-- print 'maxFiles= ' print @maxFiles
-- print 'Logicallogfile =' print @LogicalLogFile
-- print 'size = ' print @size
-- print '----------------------------------'


-- exec master..xp_sendmail @recipients=@Email,@message=@accountMsg
-- exec sendMail 'SqlServer', 'DBA@emailAddress.com', 'Disk Monitor Warning Message',@accountMsg


-- set @RowCnt = @RowCnt + 1
end --If/Begin

-- print @email
-- print 'rowcnt= ' print @rowCnt
-- print 'DBID= ' print @dbid
-- print 'maxFiles= ' print @maxFiles
-- print 'Logicallogfile =' print @LogicalLogFile
-- print 'size = ' print @size
-- print 'New Size = ' print cast(@size*8/1024 as int)
PRINT @accountMsg
-- print '----------------------------------'


set @RowCnt = @RowCnt + 1



-- set @Email = ' '

end --While/Begin

foxwindower
New Member
New Member

--
01 Jun 2009 07:16 PM
wonderful script, it is very useful to me, just need this, Thanks.
You are not authorized to post a reply.

Acceptable Use Policy