• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1337
  • Last Modified:

Very Large LDF

We have a SQL database whose MDF is 791MB and LDF is 194GB.  I cannot shrink this file beyond a couple GB.  I believe the size is due to it being a production database that is queried all day by the majority of our organization.  Is this a normal file size for an LDF?  I noticed that the file is set to unrestricted growth.  I am afraid to restrict the growth because I don't want the users to take a performance hit.  What to do?  Thanks!
0
rjander77
Asked:
rjander77
  • 5
  • 3
  • 2
  • +2
2 Solutions
 
rickchildCommented:
If you don't need log backups you can set the relevant database to "Simple" recovery model.

If you do need log backups and are in Full recovery model then you should be backing up the Transaction Logs, then the file will be shrinkable.
0
 
chapmandewCommented:
Don't restrict the growth...and I wouldn't consider switching to simple mode.  Make sure in your db maint plans that regular backups of the tlog are made.  Backing up the log removes inactive transactions.  Then, if needed, you can shrink the file using DBCC SHRINKFILE.
0
 
rjander77Author Commented:
Thanks to you both for your suggestions.  I have edited the Maintenance Plan to include the transaction logs and will report back tomorrow (backups run nightly) with the results.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
chapmandewCommented:
You may want to include a step that shrinks the files as well...not necessary, but will help with your big file at first.
0
 
rjander77Author Commented:
Thanks, that step was already included - which is why I was so perplexed to begin with.  I had it set to Full recovery mode with reindex and shrink operations following.
0
 
chapmandewCommented:
It wasn't shrinking because the log files weren't being backed up...once you do that, you're all good.  :)
0
 
dportasCommented:
Don't put the shrink task into the maintenance plan. If you do log backups regularly then shrinking regularly is a very bad idea.

You may want to do a one-off shrink if the log has grown excessively due to not being backed up but that doesn't mean you should modify the maintenance plan.
0
 
rjander77Author Commented:
Thanks.  I'll see what it looks like tomorrow, and if it's all under control, I'll take the shrink operation out of the daily maintenance plan and leave it on the weekend maintenance plan.
0
 
dportasCommented:
0
 
roshkmCommented:
You might have already tried this but still followin these steps:

DBCC SHRINKFILE(<File Name>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Or:
you can detach the database and attach it, but you will be lossing any replication configurations done.

Or: (i followed this method, which gave me a permanent soln)
You can rectify it by Properties->Transaction Log-> Check Automatically grow file.

If you are not taking a back up of Transaction log, Properties->Options->Check AutoShrink, Model: Simple, for decreasing the log file size. Log file in Simple Model will be cleared off after every successful transaction.
It will take some time for the log file to shrink.

When it is time that u take back up, u can reset it and restart the job that takes the back up..

Cheers,
Rosh K. M.
0
 
rjander77Author Commented:
Thanks!
0
 
rjander77Author Commented:
Ran into a few space issues when backing up the 200GB trxn file, but after I got that cleared, it worked just fine!  The trxn file is now 70 MB, and I have restricted growth to 2 GB.  I figure that's more than enough for a 700MB database.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now