Recently I came to know that there is a very small and interesting bug with SQL Sever 2008 RTM release for INSERT statement. Its , when our INSERT fails, its occupy space. For same , below is the explained example.

Let, First create a Table, here its named as ‘VirendraTest’ in my Test Database ‘Virendra’

CREATE TABLE VirendraTest( Code Char(10), DataValue NVARCHAR(MAX)
CONSTRAINT [PK_MYPKKEY] PRIMARY KEY CLUSTERED
(
   Code ASC
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF,
ALLOW_ROW_LOCKS 
ON, ALLOW_PAGE_LOCKS ON) ON [PRIMARY]
)

Now insert a row as

DECLARE @TMP NVARCHAR(MAX)
SET 
@TMP=REPLICATE(‘VIRENDRA’,4000)
INSERT 
VirendraTest(code,DataValueVALUES (‘A-1111’,@TMP)
Lets check used space

sp_spaceused ‘VirendraTest’ true
For me, its showing


Now insert same record again, it will show primary key violation error, but sp_spaceused will give different result.

Let check again used space,

As you seen above, without inserting any records, table space used size increased.

Lets check this query for 1000 loop and check space used.

And now check, used space

To resolve this issue , we can use CLEANTABLE command to release space and to avoid this issue again, there is a KB article http://support.microsoft.com/kb/2295878 about it. Downloaded the cumulative hot-fix and installed it on server and restart server.

CLEANTABLE example,

DBCC CLEANTABLE (DATABASENAME,TABLENAME)
DBCC CLEANTABLE(Virendra,VirendraTest,0) — — 0 for current database

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s