Transaction File growing out of control

[Simian888]

SQL 2000
We have a transaction file that grows by over a GIG every time we do certain procedures or do a Database shrink or other maintenance. It started at 1MB and is now, 12 hours later, 14GB.

Any suggestions would be appreciated,
Al

 

[SQLSister]

You need to backup your log file and shrink it. If it is growing that fast in 12 hours, you probably need to back it up freqently.

If you are getting the data from other sources and don;t ned a transaction log backup you need to change the database to simple recorevry mode. Then truncate the log and shrink it.

See this FAQ for information on this process.
Database and Log Files
Shrinking Databases and Logs - SQL 7 and Higher
faq183-1534


Questions about posting. See faq183-874

 

[Simian888]

The odd thing is that the database is in simple recovery mode.

 

[SQLBill]

Simple mode doesn't mean the log won't grow. It just means that the database will apply checkpoints and automagically shrink the log for you.

With FULL, it means you have to do a log backup for the same thing to happen.

That's a REALLY basic description of what happens. You can find out more from the BOL.

-SQLBill

 

[Simian888]

Is there any reason then why it wouldn't be shrinking itself?

I've been reading a lot and have seen quite a bit on how to truncate and....

Anyways I'm just wondering if there is an option or something I can look at to figure out why it would not be shrinking. We actually crashed it last night because the log filled the directory in a very short amount of time. The server has been running fine for a couple years and then yesterday the log file just explodes in size.

We had to move the database and with the help of Microsoft we created a new database and ran a few checks. It seems that all the info is there but why did we have a 4G log file and then today a 14G log file, is it empty space?

Thanks for the feedback,
Al

 

[SQLSister]

From BOL

Long-Running Transactions
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:

If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.


The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint.

Maybe what is happening is that your transactions are so long running or not getting commited that the log can't truncate.

What process is causing the growth? Is is something that can be broken down into batches to allow the transaction log to not grow as much. Even using simple recorvery, the transaction log is still written to.

Questions about posting. See faq183-874