Failure to insert in Table costs Space Loss and Decrease Performance
Regarding the space problem of SQL server, I have collected some fact that you might be interested. It's again one things related to performance issue.
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.
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
)
)
GO
Before we do any inserts, let's check the space used by the table by issuing the following command:
sp_spaceused 'MYSQLTEST', true
name rows reserved data index_size unused
MYSQLTEST 0 0 KB 0 KB 0 KB 0 KB
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.
sp_spaceused 'MYSQLTEST', true
name rows reserved data index_size unused
MYSQLTEST 1 40 KB 32 KB 8 KB 0 KB
Now, let's try to insert the same row again which should fail because of the PK constraint.
DECLARE @TMP NVARCHAR(MAX)
SET @TMP=REPLICATE('THIS IS A TEST',4000)
INSERT MYSQLTEST(MYPKKEY,TESTVALUE) VALUES ('KEY1',@TMP)
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.
sp_spaceused 'MYSQLTEST', true
name rows reserved data index_size unused
MYSQLTEST 1 56 KB 48 KB 8 KB 0 KB
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
sp_spaceused 'MYSQLTEST', true
name rows reserved data index_size unused
MYSQLTEST 1 6232 KB 848 KB 8 KB 5376 KB
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)
Please refer to the Microsoft Article
To Fix the Issue
