Deleting Data Quickly in SQL Server, Part 4

Taking care of the transaction log (or not).

Introduction

It’s great to see that the response from the first three in this series of blogs indicates that they have been help to some folks. I continue getting searches and questions of this simple yet effective technique; there are many SQL Server installations in need of removing old records, and I’m glad I have been of assistance. At some point, an OLTP system will eventually need to be purged – maybe the data is now stored in a data warehouse, or maybe it’s simply past the point of requirements for an online system or online reporting goals.

Purging old data

I suggest once again that you go back and read the first three blogs for deleting data quickly to get an idea of what we’re doing here, and why. In a busy online system, simply hitting F5 to delete rows is a big no-no, and the method that I suggest avoids the problems associated with doing such. By now, your system probably has records ten years of age or older. Is this data being used? Are the indexes representing this data (that you rebuild every week) being used? How much smaller would your database backups become by removing these old records? How fast would your queries be by not having to spend the I/O or logical/physical reads traversing this data? Finally, how much disk space is being consumed by data that is not being used all of the time? These are the types of questions that should be answered when deleting old data in your system.

Deleting data and the transaction log

The TSQL DELETE statement is a fully logged operation, and when you delete data you will automatically get two unavoidable operations that coincide with the removal of rows; you delete data in SQL Server, and the data will get written to the transaction log. And, if you delete data, you’ll get the statement wrapped by an implicit transaction, unless you specify a BEGIN TRANSACTION with your delete. There is no way around this, regardless of the recovery model that is set for the database. That is, there is no “turning off” this action in any version of SQL Server.

So, you would think that if you delete millions of rows you would need lots of space to handle the records until a checkpoint has occurred and a SHRINKFILE has been run. Wrong. Using the technique that I present in this series, there is no need to fiddle with the transaction log, and certainly, no need to include some elaborate and unnecessary code within your delete statement logic to back up the log with x number of deletes to y number of files as the operation progresses. Why? Because the statement that I have presented is wrapped within the aforementioned implicit transaction; simply deleting will not. Let’s take a look at what I’m describing here.

In the following (Figure 1), I delete three million rows with a delete statement. While the phone rings that users are blocked, timeouts are occurring, and the boss wants someone’s head, you’ll see that the transaction log has gotten big – much bigger than when we started. We start off with 256MB but end up with almost 3GB worth of transaction log. Figure 2 is a SQL Profiler shot of what occurred when this statement was issued.

Figure 1. Simple delete on 3 million rows

Figure 2. SQL Profiler view of Figure 1.

Notice that there is an implicit transaction that was mentioned that is wrapped around this delete; using this statement the log will grow depending on the number of rows deleted and the size of the dataset that is removed.

Now, let’s look at my suggested delete statement using in the same light as above:

Figure 3. Our delete on 3 million rows

Figure 4. SQL Profiler view of Figure 3.

Notice how each small chunk is wrapped in an implicit transaction – these nice, small chunks to delete serve several purposes, as we have discussed previously, including the one described here, which helps keep the transaction log from growing in some fashion that could cause problems when the delete occurs.

Next Up

I’ll have another or two additions in this series, including how to monitor the chunk size to gauge the relative pain or lack thereof that the delete is causing to your online users. Please let me know how this is working for you in your production system.

Lee

------------------------

"Look, when I was a kid, I inhaled frequently. That was the point."  President Obama