SQL Server does not reclaim space used when inserts fail

Problem

SQL Server has an issue when an INSERT fails that includes LOB data, SQL Server allocates space for the insert but does not reclaim the space on the failure therefore taking up more space than necessary.  In this tip I walk through an example of this issue and ways to resolve it.

Solution

In this tip I will reproduce the error and show how this can be resolved.  I have tested and verified the issue on a SQL 2008 SP2 server.

This query below shows the version of SQL Server I am testing this issue on.

SELECT

SERVERPROPERTY(‘productversion’),

SERVERPROPERTY (‘productlevel’),

SERVERPROPERTY (‘productlevel’),

SERVERPROPERTY (‘edition’)

— output from above query

10.0.4000.0    SP2    SP2    Enterprise Edition (64-bit)

For the test, let’s create a sample test table in my TEST database.

CREATE TABLE MYSQLTEST(
MYPKKEY CHAR(10),
TESTVALUE NVARCHAR(MAX)
CONSTRAINT

[PK_MYPKKEY] PRIMARY KEY CLUSTERED 
(
    [MYPKKEY] ASC


)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY

= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


)

Before we do any inserts, let’s check the space used by the table by issuing the following command:

sp_spaceused ‘MYSQLTEST’, true

Let’s insert the first record.

And then check the space used again.  Here we can see that reserved is 40KB, data is 32KB for 1 row.

Now, let’s try to insert the same row again which should fail because of the PK constraint.

As expected, the insert fails because we tried to insert a duplicate primary key value. Let’s check the space used again.  We can now see that reserved is 56KB, data is 48KB for 1 row.

Now, let’s run the same insert statement 100 times.

Lets check the space used again.  Now we can see reserved is 6232KB, data is 848KB for 1 row.

If we want to reclaim the allocated space we can run the following CLEANTABLE command where TEST is the name of my database and MYSQLTEST is my table.

Fixing the Issue

In order to fix this issue, Microsoft has published the following KB article http://support.microsoft.com/kb/2295878.

I downloaded the cumulative hot-fix and installed it on my server.  Then I rebooted my server and checked the version again. Here we can see my version is now 10.0.4272.0.

To verify that the problem was fixed, I tried to insert the 100 rows again and verified that the disk space used did not increase.

Next Steps

• Read this KB article for more information: http://support.microsoft.com/kb/2295878
• Be aware of this issue and check your database tables to see if you have any unnecessary space used for your data.
• The solution to the above issue has been addressed by KB2295878 (http://support.microsoft.com/kb/2295878 or by upgrading to SP3. 
• For other space used issues, refer to this related tip: Clean unused space when a SQL Server table with a variable length column is dropped.