November 21, 2005 at 12:49 pm
I have an NTEXT column in a table. SP-SPACEUSED shows about 16 gig reserved, 3 gig used and 13 gig unused. There are approximately 3 million rows in the table
When I do a max(datalength) on the NTEXT column, I get 18. The rest of the columns only add about 150 characters.
I have tried DBCC SHRINKFILE to free up this unused space, but it does not work.
Does anyone know why there is so much unused space in the table and how to free this space?
November 21, 2005 at 1:16 pm
This is a known problem since December 1, 2003 and there is no fix, just workarounds.
PRB: DBCC SHRINKFILE and SHRINKDATABASE Commands May Not Work Because of Sparsely Populated Text, Ntext, or Image Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
Also see the forum post thread from last week at http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=237056#bm238284
SQL = Scarcely Qualifies as a Language
November 22, 2005 at 2:48 am
sorry can't leap across to the link .. if you use dbcc chrinkfile(1) for example this attempts to shrink the datafile back to its original creation size. I've found this is pretty good for cleaning up lost text or ntext space.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply