CODEWITHSUNDEEP
sqlsql-server-2005
Mr. Flibble
Mr. Flibble

Reputation: 27043

SQL 2005 Trying to regain space from database

I have a SQL 2005 database with one large table. I have ran a DELETE to prune some items from the table and I want to free the space back to the OS.

I have tried the following commands 

DBCC SHRINKDATABASE (MyDB, TRUNCATEONLY);
DBCC SHRINKFILE (MyTable, TRUNCATEONLY);

and I have also rebuilt the clustered index with the following command

ALTER INDEX [IX_Clustered] ON [dbo].[MyTable] REBUILD WITH ( PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

The SHRINK commands had no effect and the index rebuild increased the database size by 35GB (the database is 1.5TB).

So, I'm at a loss. What do I need to do to regain unused database space?

Edit: The log file is not the problem. The main database .MDF file it too large.

Edit 2: Here is the results of sp_spaceused MyTable:

rows        reserved           data               index_size         unused
----------- ------------------ ------------------ ------------------ ------------------
1031649352  1543899648 KB      1481718624 KB      54444664 KB        7736360 KB

Upvotes: 2

Views: 454

Answers (3)

Kev Riley
Kev Riley

Reputation: 711

did you delete LOB data, ie TEXT, NTEXT, IMAGE or XML datatypes?

sometimes you need to rebuild tables to reclaim the space if you have deleted LOB data.

Actually, having just looked at your figures - 99% of the space is taken up by data+index - where is the free space you are trying to reclaim?

Upvotes: 0

waterlooalex
waterlooalex

Reputation: 13932

Try re-indexing using DBREINDEX, updating the stats, then shrinking, I think that did the trick for me.

DBCC DBREINDEX ('?', ' ', 80)
GO
EXEC sp_updatestats
GO

Upvotes: 1

Adamski
Adamski

Reputation: 54725

Have you checked your log file size? Have you tried spaceused to see whether the table is still / indices are still occupying a lot of space?

Upvotes: 0

Related Questions