<

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Large Transaction Log files in SQL Server 2008

Published on
10,549 Points
4,449 Views
1 Endorsement
Last Modified:
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 much smaller transaction log files.

Here is a recommended MS document showing proper procedures for truncating log size. I recommend you review this article before proceeding: http://msdn.microsoft.com/en-us/library/ms189085(v=SQL.100).aspx

The recovery model in SQL server is set by default for all databases to "FULL". In the full recovery model all transactions from the beginning of the database creation are kept in a file that grows along with the database file until it reaches outrages sizes. There are methods of shrinking these transaction log files but that doesn't always provide a long term solution. Full recovery is recommended so that in the event of corruption or failure the database can be recovered to the very minute the database went down. Simple recovery will only allow you to recover the database to the last backup copy of the database.

If you are okay with losing a small amount of transaction log you can switch to the simple recovery model and free up a large amount of space on your server, by following these simple steps as it worked for me:

Perform a Full Backup of the Database

Open SQL Management Studio

Browse to the database with the large transaction log file and right click the database, go to "Properties" Click on "Options" and change the recovery model to "Simple" then click "OK". You have now changed the logging from "FULL" to "SIMPLE".

In order for the log size to decrease you have to perform a checkpoint event, such as another full backup (another recommended step would be to run dbcc shrinkfile at this time). Transactions can hang and SIMPLE logging my not take place so if the database log size does not shrink as a last resort you can take the database offline and then bring it back online to enact the change as explained below:

Browse to the Database, right click, "Tasks" => "Take Offline". Then repeat the step but choose "Bring Online". You will now notice the transaction log file is significantly smaller.

Done, you have accomplished the goal of reducing the transaction log size.

In the event that you simply wanted to reduce the size of the file, but still want full recovery you can re-enable "FULL" recovery at this time.  Also worth checking the initial size allocation for your Transaction Log as explained in the Microsoft Article above.

That's all for today folks, let me know if this helps.
1
Comment
[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
6 Comments
 
LVL 22

Expert Comment

by:dportas
This article is misleading and doesn't sufficiently explain the assumptions or, more importantly, the caveats for the advice it gives.

Quote:

"In the full recovery model all transactions from the beginning of the database creation are kept in a file that grows along with the database file until it reaches outrages [sic] sizes"

This is wrong. The log file records only transactions since the last log backup / log truncation. The most common reason the size might be "outrageous" would be if it wasn't being backed up often enough. The appropriate solution for most people would be to take log backups more frequently, not to select simple recovery. This is not even mentioned in the article. The article continues:

"If you are okay with losing a small amount of transaction log..."

but the suggested course of action is to invalidate or truncate the current log no matter how much data has been or is being logged. For many people this won't be a "small" issue at all. I expect the author meant to say "if you don't need the protection of transaction log backups..." but using the word "small" suggests that this is not a very important concern.

Recommending taking the database offline is also a pretty drastic suggestion - although I guess most people will realise the implications of that.

Finally, the author says if you "still want full recovery you can re-enable FULL recovery at this time." but fails to mention that the log backup sequence is now invalid and so you are NOT protected by full recovery at this point. Until you do a full backup again you might as well still be running in Simple recovery, which is a bad place to be if you thought you were in full recovery mode.

I suppose any DBA ought to know that these things, but if they do then they wouldn't find this article at all useful.
0
 
LVL 8

Author Comment

by:spiderwilk007
Thanks for your comments, The line "If you are okay with losing a small amount of transaction log..." was actually modified by the editor prior to posting. I can see why that would be misleading since you do run the risk of losing all data back to the last full backup. It is also correct that after switching back to FULL recovery you will need to run another full backup before FULL recovery goes back into effect.

As the article state taking the database offline then bringing it back online is a last resort, sometimes transactions get stuck and a checkpoint is not sufficient to make the change. Overall the article is still useful, and the MSDN article clears up alot of these questions.
0
 
LVL 8

Author Comment

by:spiderwilk007
http://msdn.microsoft.com/en-us/library/ms190440.aspx

This article explains a little more about transaction log backups in the FULL recovery model if anyone is interested.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 22

Expert Comment

by:dportas
Book Online is a good place to start reading about t-log backups.
http://msdn.microsoft.com/en-us/library/ms345583.aspx

Log truncation is automatic when you back up the log. You don't need to do anything special to set it up.
0
 
LVL 60

Expert Comment

by:chapmandew
I would never recommend this route to shrink a transaction log.
0
 
LVL 51

Expert Comment

by:Mark Wills
The article makes more sense if you were to follow the recommendations of the Author in the second paragraph :

I recommend you review this article before proceeding: http://msdn.microsoft.com/en-us/library/ms189085(v=SQL.100).aspx

It covers some of the concerns mentioned above and provides far greater / more in depth account of what the Transaction log is and does along with the links on how to manage in various conditions.

I agree by and large with some of the concerns, and the Article should be considered as a last resort, and there are other methods as described in the MSDN article.

Truncating the log and reducing the physical disk consumption are quite different. Going from FULL recovery to SIMPLE would be "if all else fails" and life is fairly critical (like transaction log has filled the disk).

Still, the first approach if you must shrink the physical size of the transaction log would be DBCC SHRINKFILE as per : http://msdn.microsoft.com/en-us/library/ms178037(v=sql.100).aspx
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Join & Write a Comment

This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month