Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot reclaim unused space in table. Expand / Collapse
Author
Message
Posted Monday, November 14, 2005 10:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:03 AM
Points: 166, Visits: 277
Hi All,

Long time listener, first time caller. I have a problem with one of our tables that looks like:

CREATE TABLE NotMyTable (
KeyID varchar (20) NOT NULL ,
An1ID char (20) NOT NULL ,
An2ID varchar(20) NULL ,
An3ID varchar (20) NULL ,
Txt text NULL ,
TC varchar(20) NULL ,
UpdBy varchar (20) NOT NULL ,
UpdWhen datetime NOT NULL ,
UpdAction char (1) NOT NULL
) ON PRIMARY TEXTIMAGE_ON PRIMARY
GO

When I first saw this table it was about 3Gb and has stayed that way until the past few weeks when it's ballooned to 9Gb. The last time this happened I tried everything and in the end had to select into a new table, drop this table, recreate it and repopulate it. Now the same problem is happening again. Here's what I've tried.

Ran [sp_spaceused NotMyTable, true] and got these results:

name--------rows-----reserved---data------index_size--unused
NotMyTable 2613777 6112800KB 2072416KB 2008 KB 4038376 KB

I've tried shrinking the database using both DBCC SHRINKDATABASE and DBCC SHRINKFILE but that only dropped the size to about 8Gb (as above). I tried backing up the database and the log and then shrinking but that didn't help any (well, the log files shrank fine but they're not the problem). Neither did repeatedly running Shrinkdatabase or Shrinkfile (about 40 times each).

I've tried DBCC CLEANTABLE ('DatabaseName','NotMyTable') and shrinking but no change.
I've tried sp_updatestats and shrinking but no change.
I've tried DBCC INDEXDEFRAG (DatabaseName, NotMyTable, IX_NotMyTable)and shrinking but no change (the index is clustered and on KeyID, An1ID and An2ID).
I've tried DBCC REINDEX (on the same index with a fill factor of 90) and shrinking but no change.
I've tried DBCC updateusage to update sp_spaceused but no change.
I've tried everything mentioned in numerous different combinations but still no shrinking.

I don't understand why there is so much space reserverd and unused but I can't shrink the database.

Has anyone run into this problem before or can see off hand where I'm being a total idiot?

Yours in headbanging desperation,
Martin

Post #237056
Posted Tuesday, November 15, 2005 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:22 AM
Points: 1,062, Visits: 355

What is your fill factor for your index?

 

mom




Post #237222
Posted Tuesday, November 15, 2005 2:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 2,281, Visits: 4,226

How very strange !!!

Just to confirm that the exact order of the steps were:

DBCC REINDEX (on the same index with a fill factor of 90)
go
DBCC updateusage ( DatabaseName, NotMyTable)
go
sp_spaceused NotMyTable
go

Some possibities:
You have an index with an extremely low fill factor - this should not be the case as the sp_spaceused is showing a low size for index usage of 2008 KB.

These are all bugs:
1. sp_spaceused has a bug, possibly with an overflow
2. DBCC updateusage has a bug and is not recording correct information in the sysindexes table
3. Space Management has a bug
4. The table master.dbo.spt_values has a bad value for number of bytes in a page.


Try running the below SQL which will show the columns used by sp_spaceused from the sysindexes table and post back. just replace "employee" with the real table name.

declare @pagesBytes integer
, @PagesToKb integer
select @pagesBytes = d.low
from master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
set @PagesToKb = @pagesBytes / 1024.0
select 'sb means should be'
select @pagesBytes as PageBytes_sb_8096
, @PagesToKb as KbPerPages_sb_8

select IndexName
, indid
, rows
, OrigFillFactor
, ReservedPages
, DataPages
, ReservedPages * @PagesToKb as ReservedKb
, DataPages * @PagesToKb as DataKb
, ( WorkPages - DataPages ) * @PagesToKb as IndexKb
from (
select i.name as IndexName
, i.indid
, i.rows
, i.OrigFillFactor
, i.reserved
, i.dpages
, i.used
, case when indId in (0, 1, 255) then reserved
else null
end
as ReservedPages
, case when indId < 2 then dpages
when indId = 255 then used
else null
END
as DataPages
, case when indId in (0, 1, 255) then used
else null
end
as WorkPages

from sysindexes i
where i.id = object_id ('employees')
) as X
exec sp_spaceused employees
go


SQL = Scarcely Qualifies as a Language
Post #237336
Posted Tuesday, November 15, 2005 5:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:03 AM
Points: 166, Visits: 277
Hi Carl,

Thanks for the reply. Yep, I re-ran the REINDEX, UPDATEUSAGE and sp_spaceused again exactly as you mentioned to double check it wasn't my befuddled brain but it's coming out the same. The exact order I last ran these in was:

dbcc cleantable ('DatabaseName','NotMyTable')
go
backup database DatabaseName to disk ='C:\DatabaseName.bak' WITH INIT, NOUNLOAD, NOFORMAT, SKIP,STATS = 10
go
dbcc shrinkdatabase (DatabaseName, 10)
go
dbcc shrinkfile(1)
go
exec sp_updatestats
go
dbcc dbreindex ('DatabaseName.dbo.NotMyTable', IX_NotMyTable, 90)
go
dbcc indexdefrag (DatabaseName, NotMyTable, IX_NotMyTable)
go
dbcc shrinkfile(1)
go
dbcc updateusage (DatabaseName) with count_rows
go
exec sp_spaceused 'NotMyTable'
go

Here are the results from the query (sorry about the formatting):

PageBytes_sb_8096 KbPerPages_sb_8
8192 8


IndexName indid rows OrigFillFactor ReservedPages DataPages ReservedKB DataKb IndexKb
IX_NotMytable 1 2616997 90 34620 34363 276960 274904 2008
_WA_Sys_KeyID_5FF32EF8 2 0 90 NULL NULL NULL NULL NULL
_WA_Sys_An1ID_5FF32EF8 3 0 90 NULL NULL NULL NULL NULL
_WA_Sys_An2ID_5FF32EF8 4 0 90 NULL NULL NULL NULL NULL
_WA_Sys_An3ID_5FF32EF8 5 0 90 NULL NULL NULL NULL NULL
_WA_Sys_TC_5FF32EF8 6 0 90 NULL NULL NULL NULL NULL
_WA_Sys_UpdBy_5FF32EF8 7 0 90 NULL NULL NULL NULL NULL
_WA_Sys_UpdWhen_5FF32EF8 8 0 90 NULL NULL NULL NULL NULL
_WA_Sys_UpdAction_5FF32EF8 9 0 90 NULL NULL NULL NULL NULL
tNotMytable 255 0 0 729546 224736 5836368 1797888 0


name rows reserved data index_size unused
NotMyTable 2616997 6113328 KB 2072792 KB 2008 KB 4038528 KB

Can I also ask, do you know where I could get more info on spt_values or is it one of those MS-only-undocumented ones?


Yours Appreciatively,
Martin

Post #237364
Posted Tuesday, November 15, 2005 7:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 2,281, Visits: 4,226
AHAH, at least we can now narrow down the problem to the text!

The clustered index (indid = 1) has reserved 34,363 pages and used 34,363 pages which is 265 Mb or 27,8202,848 bytes. Dividing by the number of rows gives an average row size of 106.30 bytes per row. As the sum of the column physical sizes is about 145 bytes (include 16 bytes for the pointer to the text) and there are some variable length columns, this is reasonable.

But look at indid = 255, which is for the text:
Reserved is 729,546 pages and data is 224,736 leaving 504,810 pages as unused, which is 3.8Gb.

You did not post the SQL Server version, so if you are running 7, take a look at
"FIX: TEXT/IMAGE Data Storage Space Not Reclaimed Correctly"
http://support.microsoft.com/kb/272220/EN-US/

Workaround for this problem is:
To defragment the text or image data and recover the unavailable unused space, use the bulk copy program (BCP) to bulk copy the data out of the table and then back into the table so that the storage will then be contiguous.

Regarding the table master.dbo.spt_values, this is one of those Entity-Attribute-Value tables that the Sybase programers in the 80s designed as a catch-all place to store parameters and descriptions. For example, a type of "C" indicates the names for the instance configuration values. As there are less than 800 rows, you can just examine by eye. sp_depends can tell you what system procedures reference the table and just look at the code.



SQL = Scarcely Qualifies as a Language
Post #237369
Posted Tuesday, November 15, 2005 11:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:03 AM
Points: 166, Visits: 277
Hi,

Yep, I see what you mean. I don't know why that text column is locking the space like that though.

And yes, sorry, forgot to say that we're running SQL Server 2000 SP4 on a Win2000 Server SP4 box.

I ran a bcp to a text file with a format file, then truncated the table and bulk inserted back into the table. Then ran DBCC SHRINKFILE(1) [there's just one datafile] and it worked a treat. The database has freed the space and is about back to a normal size. I know I should have specified a target size allowing for growth space but I just wanted to get this shrunk for now.

Many thanks for the help!

Martin
Post #237399
Posted Wednesday, November 16, 2005 4:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 2,281, Visits: 4,226
As a further suggestion, as Microsoft would charge if you opened a problem, post to a Microsoft newsgroup. An MS person might be willing to open a problem for free if you can clearly state the problem, the analysis and have a database backup so they can investigate the problem. As space management changes are captured in the transaction log, a pre-condition backup and all of the transaction log backups until the problem occurred would be ideal.

To post, recommend using http://www.developersdex.com/sql/default.asp?p=580 as this site has a cleaner interface than the MS interface.

Cheers


SQL = Scarcely Qualifies as a Language
Post #237456
Posted Wednesday, November 16, 2005 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

I happen to know the solution to this ( experience!! ) to get the space back you must use a dbcc shrinkfile(1)  which attempts to return the mdf file back to its original size. This will reclaim the space.

How do I know? I have a large database containing xml in a text column, we archived 50% of the data but the original database stayed within 80% of its original size despite best efforts. I asked my friendly MVP and he found some info from a system engineer about text data .. so .. use this extreme shrink and you'll get the space back.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #237529
Posted Wednesday, November 16, 2005 3:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:03 AM
Points: 166, Visits: 277
Thanks Carl! I think I'll do just that. I'll have to get a release for the data though.

And thanks for the advice Colin but I wish it were that simple. The whole problem is that shrinkdatabase and shrinkfile aren't working properly (see above).

Thanks guys!
Martin
Post #237687
Posted Friday, November 18, 2005 8:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 2,281, Visits: 4,226
Looks like 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


SQL = Scarcely Qualifies as a Language
Post #238284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse