[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2008 R2 Transaction Log Management

Posted on 2013-05-13
4
Medium Priority
?
376 Views
Last Modified: 2013-05-27
We have a number of SQL 2008 R2 servers in which I have to manually shrink the log file periodically.  Every now and then the log file gets up to 50GB.  

When this happens, we manually do this:

ALTER DATABASE [DBFilename] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DBFilename_Log, 100)
ALTER DATABASE [DBFilename] SET RECOVERY FULL WITH NO_WAIT

We were led to believe that when a backup of the database is done, it also truncates and shrinks the log file.  This does not appear to be the case.

So, with all that said, can you please offer some advice on how we should be maintaining the database nightly or periodically so that the transaction log does not grow to ridiculous sizes?

Thank you

Paul
0
Comment
Question by:TheMetalicOne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Assisted Solution

by:Psihawk99
Psihawk99 earned 750 total points
ID: 39161789
It does truncate the file but does not shrink it.  So if you have a 50gb log file, it may be only 5% full after a backup.

What's your database recovery method set to?  Do you perform regular transaction log backups?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39161854
seems like you are not taking the transaction log backups, once the TL backup is done it will free up the space used by committed transactions.
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 750 total points
ID: 39171079
"seems like you are not taking the transaction log backups, once the TL backup is done it will free up the space used by committed transactions. "

---------------------------------------------------------------------------
free up the space used by committed transactions.
0
 
LVL 5

Author Closing Comment

by:TheMetalicOne
ID: 39199618
The issue was not backing up the TL, thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question