April 13, 2005 at 1:23 am
In scanarios where database log file space getting full with following error
Error: 9002, Severity: 17, State: 6
The log file for database 'DB1' is full. Back up the transaction log for the database to free up some log space..
how can we avoid these error?
In the below metioned steps which one is advised and why?
1. Database Log backup at frequent interval.
2. Backup log <database> with truncateonly
3. Change the database recovery model to "Simple" when the "bulk transaction processing" to happen.
Is there any other way?
Also how can we keep monitor on database log file growth?
April 13, 2005 at 3:25 am
I'm assuming that this is a production database so you probably want to discount options 2 and 3 straight away. If you do either of them then you can only ever restore the database back to the last full backup taken (everything since is lost because the log file is being truncated)
The best way to keep the log under control is to perform frequent transaction log backups.
As for monitoring the log file utilisation, I use the following table and SP (can't remember if I wrote it myself or aquired it from elsewhere so please accept my appologies if you actually created it)
CREATE TABLE [dbo].[LOG_SPACE] (
[Database_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Log Size] [float] NULL ,
[Log Space Used] [float] NULL ,
[Status] [int] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE Log_Space_Monitoring
AS
set nocount on
truncate table Log_Space
insert Log_Space exec ('dbcc sqlperf(LOGSPACE)')
delete from Log_Space where Database_Name != '<insert your DB Name Here>'
IF (select [Log Space Used] from [Log_Space]) between 60 and 80
begin
exec sp_SQLSMTPMail @vcTo = 'your email address' ,
@vcsubject = 'Database Transaction Log',
@vcBody = 'The Transaction log for the <DBNAME> database is now over 60% full'
end
IF (select [Log Space Used] from [Log_Space]) > 80
begin
exec sp_SQLSMTPMail @vcTo = '<your email address>',
@vcsubject = 'Database Transaction Log',
@vcBody = 'The Transaction log for the <DBNAME> database is now over 80% full, sort it out PDQ'
end
GO
As you can see I use a custom SMTP mail stored procedure but it's not going to be too difficult for you to change it to use whatever notification method you prefer SQLMAIL or otherwise.
All you need to do then is create a scheduled job to run the SP ever five minutes or so (and test that it works).
April 14, 2005 at 8:13 am
I followed the advice of someone else on this site and set up an alert to backup the transaction log if it gets to more than 85% used. It seems to work well.
Peter
April 14, 2005 at 9:10 am
Out of curiosity what is the database data and log sizes ? What is your recovery mode ? Are you using SQL maintenance plans ? What is the interval of time between transaction log backups ? The reason I ask these questions is try to arrive at the potential 'root' cause of the issue you are encountering. You've implemented a work-around as opposed to finding the 'root' cause of the issue ... which may come back to haunt you later ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 14, 2005 at 11:33 am
dbcc sqlperf(logspace) tells you the log szie and percentage of used.
April 18, 2005 at 2:17 am
April 18, 2005 at 8:07 am
Yes. Now, is your recovery model 'SImple' or 'Bulk-Logged' if it is you have large transactions running that may be causing the issue (you do not need transaction log backups). If your recovery model is 'Full' then your issue is that tou are not backing up the transaction log (start at 30 minute intervals and monitor from there to change the frequency). Also if you are rebuilduing indexes or changing freespace in your maintenance plan this may also cause transaction log issues. Hope that this gets you a bit farther now.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply