A frequent transaction log backup misunderstanding

Some SQL Server students or new DBAs seem to ignore the fact that you have to take transaction log backups, if you do not use the SIMPLE recovery model. In other words, if you set FULL or BULK LOGGED as the recovery model for a database, you have to take transaction log backups. Otherwise the transaction log is not truncated. So, in these two recovery models, not only do you have to backup the database, you also have to take transaction log backups. This is a fact. This is what you must do. SQL Server neophytes that do not seem to grasp this concept are sometimes contemned by some SQL Server backup experts.

From what I understand, these experts cannot seem to pinpoint why this misunderstanding takes place amongst new SQL Server DBAs. So they might at times get frustrated about this mishap. And because this seems to occur over and over again, they have started talking, teaching and blogging about it. And this is a good thing. Because whenever a mistake or misunderstanding occurs, especially when it occurs repeatedly, we should inform others about it. We have all read or even compiled frequently asked questions (FAQs) about different subjects. We should also have lists like Frequently Made Mistakes, Frequent Misunderstandings and so on.

But it is not obvious to some experts why the transaction log backup misunderstanding would ever occur, yet it seems to pop up constantly.

So why does it occur? Some misunderstandings take place because things are not explained properly, some others because of particular thought patterns (take for example optical illusions), some because of stereotypes, and so on. But this particular misunderstanding, in my opinion, has to do with a notion that comes from Exchange Server.

Let us begin from… well… the beginning. Exchange Server does not use SQL Server; it uses a special version of the Jet database engine (the Extensible Storage Engine, or ESE for short. By the way, Active Directory also uses ESE). The fact that Exchange uses ESE is unfortunate, because Exchange could benefit from the robustness of SQL Server, and also, administrators charged with both SQL servers and Exchange servers now have to master two different sets of procedures for the management of the databases. On the other hand, Microsoft tunes Exchange’s ESE to fit Exchange’s needs exactly, so there are reasons for this approach, too. This is an involved subject, so I will leave it here.

Not only are the database systems different, the backup philosophy also differs. And here lies the reason which creates confusion for some people. You see, in Exchange Server there is an equivalent to the SIMPLE recovery model and an equivalent to the FULL recovery model. They do not go by these names, but it does not matter here. In Exchange’s equivalent SIMPLE recovery model, the transaction log is automatically purged and you need not worry about it. The same behavior occurs for SQL server. But in Exchange’s equivalent FULL recovery model, the transaction log is purged when you perform a full backup. Did you read this carefully? I will write it again. In Exchange, the transaction log is purged when you perform a full backup. This is not true when it comes to SQL Server. In SQL Server, the transaction log is not cleared when you perform a full backup. You have to backup the transaction log explicitly for it to be cleared. If you do not back up the transaction log, then it will continue to grow.

So, people that have an Exchange background will take for granted that SQL Server full backups will truncate the transaction log, even though this is never the case. Also, in Exchange Server’s courses, books, articles, etc. the effect Exchange’s full backup has to the transaction log is denoted. Contrary to that, in SQL Server’s educational materials the effect SQL Server’s full backup has to the transaction log is not denoted, because there is nothing to denote: SQL Server full database backups have no effect on the transaction log. And when you have an Exchange education and experience, you can easily misunderstand this fact about SQL Server, even if it is presented to you.

The only way for this misunderstanding to stop, is to bring the two philosophies in contrast. When the FULL or BULKED LOGGED SQL Server recovery models are discussed, there should be a paragraph stating this frequent misunderstanding and denote the difference between Exchange Server and SQL Server.

About Dimitrios Kalemis

I am a systems engineer specializing in Microsoft products and technologies. I am also an author. Please visit my blog to see the blog posts I have written, the books I have written and the applications I have created. I definitely recommend my blog posts under the category "Management", all my books and all my applications. I believe that you will find them interesting and useful. I am in the process of writing more blog posts and books, so please visit my blog from time to time to see what I come up with next. I am also active on other sites; links to those you can find in the "About me" page of my blog.
This entry was posted in SQL Server. Bookmark the permalink.