Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server does not reclaim space used when inserts fail


By:   |   Last Updated: 2011-11-18   |   Comments (3)   |   Related Tips: More > Database Administration

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

sql server sp_spaceused

Let's insert the first record.

DECLARE @TMP NVARCHAR(MAX)

SET @TMP=REPLICATE('THIS IS A TEST',4000)

INSERT MYSQLTEST(MYPKKEY,TESTVALUE) VALUES ('KEY1',@TMP)

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

sql server space used

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

sql primary key failure

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.

sql 2008 sp spaceused

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

sql 2008 primary key constraint failure

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

sql 2008 sp_spaceused

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.

DBCC CLEANTABLE(TEST,MYSQLTEST,0)


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.

sql server version edition information

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


Last Updated: 2011-11-18


get scripts

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 18, 2011 - 11:06:43 AM - Aris Back To Top

Thanks for the update.


Friday, November 18, 2011 - 10:55:43 AM - Matteo Back To Top

I have only tested with INSERT and DELETE. If you are experiencing such issue, please install SP3 and it will be fixed.

 

Thanks


Friday, November 18, 2011 - 8:52:56 AM - Aris Back To Top

Does this happen with deletes and updates?


Learn more about SQL Server tools