SQL SERVER - Detecting unreclaimed space in database tables with previously dropped columns

Whenever any column is dropped from a table, except for the one most recently added, it leaves behind empty space that isn't reclaimed by SQL Server without the user's intervention. Typically, that's not a problem, unless space is of concern. If it is, then executing ALTER TABLE [TABLE] REBUILD in SQL Server 2008+ or DBCC CLEANTABLE ([DATABASE],"dbo.TABLENAME") should do the trick by shifting columns to fill any voids.

What might not be obvious, though, is that if one keeps dropping and adding/re-adding columns, then eventually they'll get one of the following errors:

  • The specified column set value causes the estimated row size to be at least %d bytes. This exceeds the maximum allowed row size of 8060 bytes. To reduce the row size, reduce the number of columns specified in the column set.
  • Cannot create a row of size %d which is greater than the allowable maximum row size of 8060.
  • Creating or altering table '%.*ls' failed because the minimum row size would be %d, including %d bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
  • Warning: The table "%.*ls" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
  • Altering table '%.*ls' failed because the row size using vardecimal storage format exceeds the maximum allowed table row size of 8060 bytes.
  • Creating or altering compressed table '%.*ls' failed because the uncompressed row size would be %d, including %d bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The problem is - the number of columns that a table can hold is not indefinite. It depends on several factors, including their sizes. SQL Server allocates a page of size 8060 bytes for every row of data. That is, all the fixed-size columns must fit in that bracket. You can test that yourself by creating a new table with, say, a couple of char columns of size 4000, and then try adding another one. Chances are, the alter table command will fail because there is no more space to accomodate extra fields. Note, that variable sized columns (e.g. varchar) behave differently. As the value size can vary from row to row, the data can either be stored in the page itself, if there's enough space, or in an off-row page. So, when adding columns, as long as the combined size limit isn't reached, everything should be fine. However, every once in a blue moon, you may see a message similar to the ones shown below when trying to add a new column to a table, even though the total bytes don't exceed 8060:

  • Creating or altering table X failed because the minimum row size would be X, including X bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
  • Cannot create a row of size N which is greater than the allowable maximum row size of 8060.

So, what's going on here? Well, the cause of this is most likely unreclaimed space from a dropped column. That is, there used to be a column in this table. It was dropped. But, because the bytes reserved for it were somewhere in between other field allocations within the page, SQL Server didn't automatically get rid of the gap by moving data, which could be a fairly costly operation on a significantly large table.

So, to detect where there are gaps in any tables in the current database, I've crafted the following query that looks at column allocation sizes and offsets within the main partition to determine if there are gaps in there:

with table_layout as
(
    SELECT  sp.OBJECT_ID as objectID, sc.name as ColumnName, sipc.leaf_offset as leaf_offset, sp.partition_id as partId, max(sipc.max_inrow_length) as max_inrow_length
    FROM    sys.partitions sp
            JOIN sys.system_internals_partition_columns sipc 
                 ON sp.partition_id = sipc.partition_id
            JOIN sys.columns sc 
                 ON sc.column_id = sipc.partition_column_id AND sc.OBJECT_ID = sp.OBJECT_ID
			join sys.indexes i on i.index_id = sp.index_id AND i.object_id = sp.object_id	
	where i.type_desc <> 'NONCLUSTERED'
    group by sp.OBJECT_ID, sc.name, sipc.leaf_offset, sp.partition_id
)
select s.name + '.' + tb.name as TableName, t1.*
from table_layout t1
join sys.tables tb on t1.objectID = tb.object_id
join sys.schemas s on tb.schema_id = s.schema_id
where leaf_offset > (
    select top(1) t2.leaf_offset + t2.max_inrow_length
    from table_layout t2
    where
    t2.objectID = t1.objectID
    AND t2.partId = t1.partId
    AND leaf_offset > -1
    AND t2.leaf_offset < t1.leaf_offset  
    order by partId asc, leaf_offset desc
)
order by objectID desc, leaf_offset asc

Note, that while this detects gaps at column level, wasted space at row level cannot be detected this way. And, while it's possible to do so by using special (often undocumented DBCC commands), running such queries would take a substantial amount of time. The query above runs quickly, because it's performed at table definition level and doesn't scan the whole set of data. It can help prevent certain errors, such as the one quoted above, when executing alter table commands, expecially in automated scenarios. It won't help with leaks within individual pages. In all cases, if the cause of the problem is unreclaimed space, then either of the following two commands should help fix the problem:

ALTER TABLE [TABLENAME] REBUILD

 OR

DBCC CLEANTABLE ([DATABASE],"dbo.TABLENAME")

So, let's test this. First, create a table like below.

create table [TestTable]
(
	Col1 char(3000),
	Col2 char(3000),
	Col3 char(2000)
)

 Now try to add another column into the table.

alter table TestTable
add Col4 char (1000)

The alter operation should fail with the error:

  • Creating or altering table X failed because the minimum row size would be X, including X bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Now, drop the second column, which should be spaced between the other two:

alter table TestTable
drop column Col2

 This has freed up some space, as we now have only two columns totalling 5000 bytes (3000+2000) in size, leaving us with 3000 spare bytes.
However, the following attempt to create another field should fail, as the available space has not been reclaimed:

TableName ObjectID ColumnName leaf_offset partId max_inrow_length
TestTable 5575058 Col3 6004 72057594039107584 2000

As can be seen from this result, the query shows a table that needs rebuilding, as well as the column before which the gap appears. After rebuilding the table and re-running the query, no results should be produced, meaning that the problem has been fixed and unused row space has been reclaimed.

alter table [TestTable] rebuild

 So, in conclusion, the query can be very useful in quickly analysing any tables with wasted space, where indicated at the column level. Detecting leaks for individual pages is a whole different story and is much costlier from a performance standpoint. However, the proposed solution is a quick way to detect some potential errors without much scanning overhead.






Information Error Confirmation required