Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Nov 3 '11 at 11:54

This question came from our site for professional and enthusiast programmers.

marked as duplicate by jcolebrand Dec 5 '12 at 4:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

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.

share|improve this answer
    
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 Nov 3 '11 at 12:48
add comment

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')
share|improve this answer
    
I downvoted this because shrinkdatabase isn't necessary and no warning is given about switching recovery mode from full to simple –  Mike Walsh Nov 3 '11 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 Nov 3 '11 at 12:47
    
That's the point I meant by saying shrinkdatabase isn't necessary. –  Mike Walsh Nov 3 '11 at 12:51
    
@MikeWalsh: I was completing/detailing a bit you comment, not criticizing it :-). –  Marian Nov 3 '11 at 16:24
add comment