Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to control growing size of an MSSQL database transaction log file

Avatar of garychu
garychu asked on
Microsoft SQL ServerWindows Server 2003
11 Comments1 Solution793 ViewsLast Modified:
Recovery mode of a MSSQL 2000 SP3 database is set to full.
Size of database is about 80GB.
Backup of the transaction log takes place every 2 hours daily.
Transaction log file is set to grow by 500MB at a time, with no restriction on size.

The transaction log file seems to be growing out of control. Presently at about 45GB! and growing (or possibly stabilising at this size). I once shrank it, using the shrink-file option in the data maintenance wizard, to 1GB. But within a fortnight, it re-generated to its present size.

I had always thought that by regular backups of the transaction log, it will keep the physical file small.Without the need to manually shrink it. And if I do, how would I know what size would be appropriate? I have tried arbitrarily setting a maximum size to allow it to grow to (1GB). But within hours the application crash, complaining of inadequate log space.

Would really appreciate some expert advice, for which my thanks in advance.
Avatar of Anthony Perkins
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answers