Simple Recovery Mode And Transaction Logs

Kenneth Fisher discusses changing your database’s recovery model to Simple in order to clean out a transaction log:

Clearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why you shouldn’t use the following method.

And Kenneth also hits the one legitimate use:  dire emergency.  If this is a normal part of some process (e.g., warehouse loading), bite the bullet and either live in Simple recovery mode (understanding the risks) or get the disk space to keep it in Full mode.  Switching back and forth—especially if you aren’t taking full backups immediately after switching back—is a good way to get yourself burned.

Related Posts

Dealing With Noisy Neighbors

Kevin Kline explains what Resource Governor does: There are lots of ways to manage noisy neighbors. For example, you could spin up additional instances of SQL Server on a single physical or virtual machine (VM), and then segregate the applications to a distinct instance. You could also follow the old adage of “one application, one […]

Read More

Creating Database Snapshots

David Fowler has a script which lets you create database snapshots easily: The procedure takes two parameters, @DatabaseList – a comma delimited string of database names, allows wildcards @ListOnly – 1- a list of affected databases will be displayed but snapshots aren’t created. 0- Snapshots are created automatically DEFAULT I’m a big fan of database […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29