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?
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.

Bhavesh ShahLead AnalysistCommented:

you can create maintenance plan for shrinking database.

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:

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
Steve WalesSenior 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:
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 WalesSenior 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
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 2008

From novice to tech pro — start learning today.