Solved

Reducing size of MS SQL transaction log file.

Posted on 2013-10-23
6
797 Views
Last Modified: 2013-10-24
I need to reduce the size of the transaction log file for MS SQL 2008 R2 database. Tech from app vendor directed me to shrink log file, as well as switch to Simple Recovery mode. Shrinking the log file freed up some space, though not a lot. Current size is 192GB. After reading MS docs on MS SQL 2008, I understand the correct way to go about the whole process. Backups were never setup for the transaction log, so it never was cleared. I guess there are 3 plus years worth of transactions.

Unfortunately, I switched Recovery mode to Simple, before verifying info. Is it possible to switch back to Full mode, and then perform Full backup of transaction log? Will the backup clear out the unneeded data? Is there another way to accomplish clearing out the transaction log? Does SQL still use the transaction log in Simple Recovery mode? Can I just delete it without suffering any harm to the database operation?

All Help is greatly appreciated.

Thanks!
0
Comment
Question by:rojiru
[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
  • 3
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 350 total points
ID: 39594791
Switching to Simple log mode has broken your recovery chain (assuming that this is a production database that you want backed up).

You certainly can switch back to Full Recovery Mode however you'll need to get your recovery chain back in place.

I suggest you IMMEDIATELY take a full backup.

After that, take a transaction log backup and you will be able to shrink the log file back to something more reasonable in size.  The transaction log backup doesn't shrink the log file, but it will mark the space as able to be reused and you will manually be able to shrink it.  Probably best to shrink it during a low volume time (or better yet, during a maintenance window).

Going forward, regularly take transaction log backups.  This is the only way to make sure your transaction log doesn't run away from you again.

Have a read of this article I wrote on this exact topic:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

This is the only way to keep your transaction log from getting large.

You must run TLog backups regularly in order to keep  your log from growing forever.
If you remain in Simple recovery mode (not recommended for a prod database), the transaction log is used pretty much only for the duration of the current transaction and then is immediately marked for reuse.

Have a read of the article and the links therein and I think you should have a better understanding of how it all works.
0
 

Author Comment

by:rojiru
ID: 39595237
The order to fix will be to perform a full backup of the db (there is one scheduled tonight). Next is to switch the db back to Full recovery mode. Then run a TLog Full backup. Correct?

I'll setup the TLog backup, once this has finished. Someone setup the db backup in Mgmt Studio, but did not setup backup of the TLog. Same thing for another db, though the backup is performed by backup exec.

Thanks for the link and assistance, I'll check back tomorrow.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39595245
Set back to Full Recovery Mode before the full backup.

Otherwise there's still a gap in your recovery chain if you do it after.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:rojiru
ID: 39595252
Great! Thanks again, will do!
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 39595449
You want to explicitly shrink and re-grow the log yourself in rather large chunks to the max size you'll need between log backups (which you will implement only after switching to FULL recovery mode, of course).

For example:

DBCC SHRINKFILE ( dbname_log )
-- assumes ultimately a 6GB log, adjust as needed to match your actual log size
ALTER DATABASE dbname
    MODIFY FILE ( NAME = dbname_log, SIZE = 2GB, FILEGROWTH = 50MB )
ALTER DATABASE dbname
    MODIFY FILE ( NAME = dbname_log, SIZE = 6GB )


That method reduces the number of VLFs and the fragmentation of the log file on disk.
0
 

Author Closing Comment

by:rojiru
ID: 39597808
Thanks for the help guys. It all worked out well. File size down to less than 300MB. Some reading to do, and then setup backup of Transaction log.

G'Day Gents!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

737 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