Spring Cleaning with SQL Server Stored Procedures

While browsing through SQL Server’s documentation looking for supplied stored procedures related to one of my current writing projects, I noticed a couple of procedures I had never noticed before: sp_clean_db_free_space and sp_clean_db_file_free_space. Since it looks like spring might be just about here in the Northwest corner of the continental US—which means it’s time to start thinking about spring cleaning—I thought I’d take a look at these procedures.

It turns out that both of these stored procedures remove ghost records, which are deleted rows that haven’t actually been physically removed. For performance reasons, rows sometimes aren’t removed during an actual DELETE operation but are removed by a background process called Ghost Cleanup. These procedures are provided for use in cases in which security is sometimes of equal importance to performance, and you don’t want the data left on the pages in case someone has access to the physical files and can use particular tools to read the bytes that haven’t been physically deleted. The procedure sp_clean_db_free_spacecleans all the ghost records from a database, and sp_clean_db_file_free_space cleans up all the ghost records from a single file.

In most cases, SQL Server is quite efficient at removing ghosts, especially on small test databases with little concurrent activity—which is what I have access to right now on my laptop, and which is why I couldn’t come up with an example to show you some ghost records, then show you that these procedures would remove them. The Ghost Cleanup process is just too efficient on my own SQL Server machines!

I looked around for other commands related to cleaning, and I found DBCC CLEANTABLE, which, according to the documentation, “reclaims space from dropped variable-length columns in tables or indexed views.” I remembered a blog post I had written quite a few years ago that discussed SQL Server not reclaiming space after modifying a column’s length, and I thought perhaps I could rework some of the code in order to come up with an example for how DBCC CLEANTABLE might be useful. The original blog post, titled “Altering the length of a fixed-length column,” discussed increasing the length of a fixed-length column in a table.

I created a table called change_var, in which I was going to create some variable-length columns, INSERT a row, change the maximum length of one of the columns, and then UPDATE the value to use the whole changed length.

Since these are variable-length columns, I actually have to put a value into the column to use the whole length, so I then updated the new column.

UPDATE change_var SET col4 = REPLICATE('c', 4000) WHERE col1 = 1;

But things didn’t work as expected. When I first tested this statement, I received the following very unpleasant error: Msg 682, Level 22, State 214, Line 2 Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

When I tried to reproduce the problem so that I could describe it for this article, the behavior was even stranger. Every time I ran the UPDATE, SQL Server would switch to the master database before trying to perform the UPDATE, and then I would get a 208 error because the change_var table was unknown in master. I found that even though I was in my testdb database when I tried to run the UPDATE, I had to include the USE command in the batch in order to get the 682 message shown above.

I was getting more and more confused by the strange behavior I was seeing, and trying to figure out how to reproduce it, as well as trying to determine how to avoid it. I had completely forgotten the command I had originally started trying to find a use for. But I finally remembered—and it turned out to be just the command I needed.

DBCC CLEANTABLE(TestDB, change_var);

Now the final UPDATE works just fine.

UPDATE change_var SET col4 = REPLICATE('c', 4000) WHERE col1 = 1;

This certainly doesn’t explain why I was getting the strange behavior, and there obviously is more research to be done. However, often the best immediate solution is just to have the problem go away. And I found that a bit of cleanup was just what I needed. I wonder if cleaning up my desk will also help me solve any outstanding troublesome problems?

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More