2

Possible Duplicate:
Why Does the SQL Server Transaction Log Keep Growing or Run Out of Space?

how can i reduce the size of Transaction log programatically and automatically, because no one knows when the when it gets full since how can we manage this can any one suggest for this

Improve this question
0

2 Answers 2

Reset to default
6

You need to first understand what type of recovery (from a point in time standpoint) you want to get out of your SQL Server environment.

Do you want to be able to restore to a specific point in time? Then you want to remain in full recovery mode and you need to start taking transaction log backups (the log file will continue to grow in full mode until you take a log backup, as described in the linked blog post below)

If you are fine going to just the last full backup and not a specific point in time, then you can switch to simple as @andomar's answer suggests. (in this model, if you backup at 8PM each night and have a failure at 7:30PM, you have to go back to 8PM last night. This is fine in many applications but you need to be sure your business is okay with this. This blog post talks about customer expectations around recovery.

If you want to reduce the size of your transaction log file after it has grown due to mismanagement you can shrink. But I would NOT shrink the entire database as another answer suggests. That will cause other issues. It is generally speaking not a best practice to ever shrink your database. I would do a SHRINKFILE instead.

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

This command is described here.

If you want to understand the mechanics deeper, I wrote a blog post a few years ago that discusses transaction log growth. Proper management is best because then you won't have to worry about finding an automatic way to shrink it.

Improve this answer
2
  • Apologies for the two links back to my own blog. Just a topic I've covered a lot there and didn't want to go too far in "extra background" here :-)
    – Mike Walsh
    Commented Nov 3, 2011 at 12:48
  • Wonderfully simple. Set the offending database from full recovery to simple recovery (its a development database). performed the normal backup maintenance plan. Used Tasks-> Shink-> File. switch from Data to Log, and released the unused space.
    – BozoJoe
    Commented May 8, 2014 at 22:23
1

If you set the database option Recovery Model to Simple, the transaction log does not grow beyond what is needed. The simple model doesn't require active management.

In the other recovery models, the transaction log grows until it's backed up. If you monitor backups in these models you will be ok.

The only time you should have to reduce the size of the transaction log is if your backups haven't worked for a long time, and the log size has greatly increased. The typical way to do that is:

DBCC SHRINKDATABASE(N'YourDb')
Improve this answer
4
  • I downvoted this because shrinkdatabase isn't necessary and no warning is given about switching recovery mode from full to simple
    – Mike Walsh
    Commented Nov 3, 2011 at 12:33
  • 1
    And actually the correct dbcc command should've been DBCC SHRINKFILE, not SHRINKDATABASE. That for reducing the size of the log file only, not for the entire db.
    – Marian
    Commented Nov 3, 2011 at 12:47
  • That's the point I meant by saying shrinkdatabase isn't necessary.
    – Mike Walsh
    Commented Nov 3, 2011 at 12:51
  • @MikeWalsh: I was completing/detailing a bit you comment, not criticizing it :-).
    – Marian
    Commented Nov 3, 2011 at 16:24