SQL Server 2005 Transaction Log

Question

SQL Server 2005 Transaction Log

msalmaan

Mr or Mrs. 500

Points: 571
More actions
June 14, 2009 at 11:07 pm

#155827

Hi friends,
I have a problem with increasing transaction log.
Transaction log of my database use to be normally of 1 or 2GB. Never before transaction log went above 5 GB in rare cases but for the past one week it is going beyond 25 GB and coming to 1 GB after shrink command with truncate.
Interestingly shrink DBCC command with truncate only is scheduled on every day.
Recovery model of database is Full.
We use Backup Exec for backups.
The backup us schedule as weekly full backup and daily differential backups and for every 2 hours log backups.
Before daily differential backup there is a schedule in SQL Server as shrink DBCC command with truncate only for transaction log.
It worked fine for 2 months but for the past one week every thing wnet in vain.
Transaction log backup never exceeded 3 GB but now it is going to 25 GB.
Please Can anyone guide me in solving this issue.
It would be great help to me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

manishankar_83 Old Hand Points: 391 More actions · Answer 1

Hi,

Can you add job step before you run DBCC shrinkfile to dump the LOG_REUSE_WAIT_DESC for the specific DB's to a table or to a text file.

This will help us understand why inspite of having the shrinkfile there is an abnormal growth !!!

Alternatively you increase the frequency of this job and see if that controls the DB growth.

Thanks,

Mani

krayknot SSCertifiable Points: 7998 More actions · Answer 2

msalmaan (6/14/2009)
Hi friends,
I have a problem with increasing transaction log.
Transaction log of my database use to be normally of 1 or 2GB. Never before transaction log went above 5 GB in rare cases but for the past one week it is going beyond 25 GB and coming to 1 GB after shrink command with truncate.
Interestingly shrink DBCC command with truncate only is scheduled on every day.
Recovery model of database is Full.
We use Backup Exec for backups.
The backup us schedule as weekly full backup and daily differential backups and for every 2 hours log backups.
Before daily differential backup there is a schedule in SQL Server as shrink DBCC command with truncate only for transaction log.
It worked fine for 2 months but for the past one week every thing wnet in vain.
Transaction log backup never exceeded 3 GB but now it is going to 25 GB.
Please Can anyone guide me in solving this issue.
It would be great help to me.

You can use the following:

1. Truncate the inactive transactions in your transaction log by taking the backup of the transaction log file.

http://support.microsoft.com/kb/873235

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

ps. One Orange Chip Points: 29422 More actions · Answer 3

msalmaan (6/14/2009)
Recovery model of database is Full
....
....
The backup us schedule as weekly full backup and daily differential backups and for every 2 hours log backups.

try increasing log backup frequency.

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 4

You may also want to go through Gail's article on managing transaction logs.

http://www.sqlservercentral.com/articles/64582/

Pradeep Singh

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 5

You should investigate which task causes this as the problem may escalate.

First step would be to find out what changed in last week. If nothing pops up, decompile transaction log.

The problem might be one time operation or periodic (like table/index reorganization on huge table).