Transaction Logs fill up rapidly even after steps taken

Question

Transaction Logs fill up rapidly even after steps taken

mngong

SSCertifiable

Points: 6167
More actions
January 27, 2004 at 10:04 am

#108847

Sql server 2000 sp3 Full mode Database size 150 M Transaction Log size 5G
My attention was called to above database I did backup logs with truncate only and created a nightly backup routine and hourly transaction log backups to start immediately.The log file though has grown from the 1M (poor config) it was initially created with to 500M in two hours.
dbcc opentran shows no open transactions
Is the only realistic option going to simple mode?

Mike

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

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

Steve Jones - SSC Editor SSC Guru Points: 735912 More actions · Answer 1

You need to run profiler and see what is actually being run through there. There has to be a bunch of transactions to grow the log like this.

Can you shrink the log?

mngong SSCertifiable Points: 6167 More actions · Answer 2

mngong

SSCertifiable

Points: 6167

January 27, 2004 at 10:54 am

#491799

The log shrinks very little if any

Mike

ryno black Newbie Points: 1 More actions · Answer 3

Try and use dbcc shrinkfile but first make a full backup of your dbase

after the comand complete make a full backup again as well of the transaction log. There after make a backing up your log hourly.

Ryno

TONYMARKS SSC Journeyman Points: 77 More actions · Answer 4

Most likely you have an application that opens a transaction for updates, inserts, deletes, etc. and doesn't close the transaction for a long time. BCP operations can do this. Also, many vendors write code that do this. The vendors test on small sets of data, but in the real world (your world), larger data sets are often used, and that's when the huge Trans logs occur for relatively small databases.

Note, when you backup a trans log, or truncate it with "backup transaction dbname with no_log", only the completed transactions are removed. And a transaction is complete only when it is closed (committed implicitly or explicitly). You can't remove/dump an open transaction unless you kill the spid.

Use profiler as other posts suggest. You can also run this dbcc to tell you the Oldest (not the largest) transaction open in your db: dbcc opentran('nameOfDatabase') . You'll know SPID and how long the transaction is staying open.

Good luck.

TONYMARKS