Solved

Reducing size of MS SQL transaction log file.

Posted on 2013-10-23
6
779 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
  • 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

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

Assisted Solution

by:ScottPletcher
ScottPletcher 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to eliminate duplicates in a string variable in t-sql? 30 42
Report Builder 9 30
Sql query 34 19
Azure SQL DB? 3 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now