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,DataValue) VALUES (‘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
WikiDBA 