Transaction log getting full frequently

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Transaction log getting full frequently

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-15 : 08:14:46

Dear All,

One of my production DB transaction log is getting full frequently
i am truncating & shrinking the log file with below query.

Use Personal
DBCC SHRINKFILE('AU_Dev_log', 1)
BACKUP LOG Empower WITH TRUNCATE_ONLY
DBCC SHRINKFILE('AU_Dev_log', 1)

I am doing this frequently if not i will get an error:

The transaction log for database 'Personal' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.

Shall i schedule the job for Shrink and truncate the log evry week,.,??
If any other needs to be done please help me out.

Thanks,
Gangadhar

NeilG
Aged Yak Warrior

530 Posts

Posted - 2010-09-15 : 08:21:49

How often are you backing up the log, and have you got the log set to autogrowth of set to a specific size

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-15 : 09:12:51

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

You should not be truncating the log on a production server ever. You should not be shrinking it on a regular basis

--
Gail Shaw
SQL Server MVP

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-15 : 23:11:50

quote:
Originally posted by NeilG

How often are you backing up the log, and have you got the log set to autogrowth of set to a specific size

Hi,

We are taking the log backup regularly which is scheduled every one Hour..!!
Yes we have enabled the Auto growth in Percent 5 and maximum file size of "Unrestricted file growth"

Please help how could i can avoid this not to occur in future without truncating the log ?

Thanks,
Gangadhar

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-09-15 : 23:55:35

If you are already backing up the transaction log, perhaps there's another reason why your transaction logs aren't getting truncated. Have you tried the suggestion provided by the error message i.e. check the 'log_reuse_wait_desc' column in sys.databases for the reason why the log isn't getting truncated?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-16 : 00:02:25

quote:
Originally posted by Yeoh Ray Mond

If you are already backing up the transaction log, perhaps there's another reason why your transaction logs aren't getting truncated. Have you tried the suggestion provided by the error message i.e. check the 'log_reuse_wait_desc' column in sys.databases for the reason why the log isn't getting truncated?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

The column shows log_reuse_wait,log_reuse_wait_desc
2 ,LOG_BACKUP

What does it mean can you please help me ?

Kristen
Test

22859 Posts

Posted - 2010-09-16 : 02:52:26

We have come to the conclusion that backing up once an hour is not frequent enough - particularly during "busy transaction periods" - such as index rebuilds.

I think backing up Tlogs every 15 minutes is a good compromise, but we change that to every 2 minutes during index rebuilds.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-16 : 03:28:31

Thanks you kriston for your reply.

I am using the below query to take a backup which was written by left DBA. how can i have a tran log backup for 2 min during the index rebuild.

DECLARE @date datetime
DECLARE @name nvarchar(255)
DECLARE @filename nvarchar(255)
DECLARE @databasename nvarchar(255)
DECLARE @type nvarchar(255)
DECLARE @retention int
DECLARE @path nvarchar(255)
DECLARE @i INT
DECLARE @expiry datetime

--Site dependant variables
SET @retention = 5
SET @databasename = N'empower'
SET @path = N'E:\emPower\Database\Backup\'

SET @date = getdate()
SET @type = N'TransactLogBackup'
SET @name = @databasename + '_' + @type

SET @filename = @path + @name + '.TRN'

select @expiry=max(expiration_date) from msdb..backupset where name = @name and expiration_date is not null
--PRINT @expiry

IF @expiry > @date
BEGIN
--Append & Verify
BACKUP LOG @databasename TO DISK = @filename
WITH NOINIT , NOUNLOAD , NAME = @name, NOSKIP , STATS = 10, NOFORMAT
select @i = position from msdb..backupset where database_name=@databasename and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@databasename)
RESTORE VERIFYONLY FROM DISK = @filename WITH FILE = @i
END
ELSE
BEGIN
--Overwrite & Verify
BACKUP LOG @databasename TO DISK = @filename
WITH INIT , NOUNLOAD , RETAINDAYS = @retention, NAME = @name, NOSKIP , STATS = 10, NOFORMAT
select @i = position from msdb..backupset where database_name=@databasename and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@databasename)
RESTORE VERIFYONLY FROM DISK = @filename WITH FILE = @i
END

Kristen
Test

22859 Posts

Posted - 2010-09-16 : 03:55:43

"how can i have a tran log backup for 2 min during the index rebuild."

We have a scheduled task that runs concurrent with the index rebuild. Presumably you have a scheduled task for your hourly backups? Try changing that tyo run every 15 minutes. Then look at the size of the backup files and see if some are bigger at a particular time during the day - then consider if you will run a more frequent backup during those times

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources