SQL Sever ldf file is huge compared to mdf file

I have a customer using my software that is experiencing timeout issues when executing certain queries.  I expanded the command timeout and the connection timeout properties, but they're still having issues.  When I looked a little closer at their system, I found that the log file for their system was about 50 GB and their database only about 5 mb.  They have another computer that is running the same software, but has not experienced the same growth in the log file.  

I tried to shrink the log file using SSMS, but it didn't affect the size of the file in any way I could tell.   Any suggestions on how to shrink the log file and any possible reasons this exponential growth of the log file on this computer is occurring would be appreciated.  Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
You recovery model is surely set to full and you never took any backup!

Set it to simple.
Steve WalesSenior Database AdministratorCommented:
Eric's answer is an incomplete.  Setting the recovery model to simple won't solve the issue in and of itself.

First, have a read of this article (it gives background and explains what has happened and offers up some solutions): http://www.experts-exchange.com/articles/11077/How-to-shrink-a-bloated-log-file.html

In short:

If you're in full recovery mode and not taking regular log file backups, the transaction log will grow forever.

For shrinking purposes you can:

a)  Set recovery mode to simple - shrink log file using DBCC SHRINKFILE - restore to Full recovery mode - perform immediate full backup

Be aware that setting the recovery mode to Simple immediately breaks your point in time recovery chain.


b) Take log backup (I usually take two, right after one another) - perform DBCC SHRINKFILE (again, usually twice, because first one may not work as expected if you have in flight transactions) and then implement regular log file backups.

You then need to re-evaluate your backups and recovery model.

If you are running in full recovery mode, you need to make sure that you are taking transaction log backups or this problem will happen again in the future.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
l2BravoAuthor Commented:
Steve had the more complete answer, but I had acted on Eric's answer before seeing Steve's and with a few additions (performing DBCC SHRINKFILE as I saw Steve suggested) after setting the recovery mode to simple, the solution worked.  Eric should get some points for pointing me in the right direction, while Eric's more thorough answer will help me prevent this from happening again and helped me understand what actually did happen.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.