Need to clear SQL transaction logs periodically


I have some SQL transaction logs that I would like to clear without taking their respective databases offline.

How do I do that on SQL server 2008?


Chris Schene
Christopher ScheneSystem Engineer/Software EngineerAsked:
Who is Participating?
Sagir87Connect With a Mentor Commented:

you can manually shrink the log files of sql server databases:

eg: dbcc shrinkfile ('My_DB',7) where "My_DB" is the name of the database and 7 is space in MB's.

Also you can refer this link and view some information about shrinking the Transaction Log files:
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:

you can create maintenance plan for shrinking database.
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Make sure you understand why your transaction logs are growing.

I wrote an article covering shrinking of transaction logs, take a look and see if it helps:
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Christopher ScheneSystem Engineer/Software EngineerAuthor Commented:
I created a maintenance plan that does the following automatically:

1) I create daily differential DB backups
2) Weekly I do the following
    a) Do a Full backup of the DB
    b) Backup the transaction logs
    c) shrink the database

Will this also shrink the transaction logs?
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Yes it will shrink both the data files and log files.
Refer to the doco:

However: shrinking the data files is generally considered to be a very bad idea.  It can cause internal database fragmentation and in general just shouldn't be done.  

If your log file managed to get away from you, then some maintenance on it to reclaim that space is unavoidable.  But most people seem to think that regularly shrinking the database is a bad idea.

Do a google search on "Is Shrinking a Database bad" and start reading to make up your own mind on that particular matter.

You'll be much better off, in the long run to backup your log file, shrink it (just the log not the data), regrow it in a controlled manner and then monitor going forward.

The article I linked earlier discusses this (the shrinking portion) and includes links to some excellent articles by Kimberly Tripp and Paul Randall on how to perform the regrowing in a controlled manner (which lessens log file fragmentation and increases overall performance).
Why bother to shrink the log "automatically"? It will grows again to exactly the same size before. Just make sure your growth size is not 10%  of previous (the default). It will make life more difficult.
Steve WalesSenior Database AdministratorCommented:
That is not necessarily true.  If he's never done a log backup current size of the transaction log could be very large.

As long as transaction log backups then become a standard practice, the log file will grow to a size that is usual for their usual transaction load - and at that point any further constant shrinking is just a waste of time because of exactly what you say.

But it shouldn't quite grow to an out of control size again.
Christopher ScheneSystem Engineer/Software EngineerAuthor Commented:
All the comments were helpful so i split points
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.

All Courses

From novice to tech pro — start learning today.