Cannot reclaim unused space in table.
-
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
-
What is your fill factor for your index?
mom
-
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 @pagesBytesinteger
, @PagesToKbinteger
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 @pagesBytesas PageBytes_sb_8096
, @PagesToKb as KbPerPages_sb_8
select IndexName
, indid
, rows
, OrigFillFactor
, ReservedPages
, DataPages
, ReservedPages * @PagesToKbas ReservedKb
, DataPages* @PagesToKbas 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
-
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_NotMytable126169979034620343632769602749042008
_WA_Sys_KeyID_5FF32EF82090NULLNULLNULLNULLNULL
_WA_Sys_An1ID_5FF32EF83090NULLNULLNULLNULLNULL
_WA_Sys_An2ID_5FF32EF84090NULLNULLNULLNULLNULL
_WA_Sys_An3ID_5FF32EF85090NULLNULLNULLNULLNULL
_WA_Sys_TC_5FF32EF86090NULLNULLNULLNULLNULL
_WA_Sys_UpdBy_5FF32EF87090NULLNULLNULLNULLNULL
_WA_Sys_UpdWhen_5FF32EF88090NULLNULLNULLNULLNULL
_WA_Sys_UpdAction_5FF32EF89090NULLNULLNULLNULLNULL
tNotMytable25500729546224736583636817978880
name rows reserved data index_size unused
NotMyTable2616997 6113328 KB2072792 KB2008 KB4038528 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
-
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
-
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
-
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
-
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
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
-
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply