Huge amount of index_size and unused in syscolpars

Question

Huge amount of index_size and unused in syscolpars

Steve Smith-163358

SSCommitted

Points: 1750
More actions
April 22, 2012 at 12:31 pm

#254621

Hi,
I'm hoping if you can help me.
I'm reviewing a SQL2008 database that had over 1 million views created by an application that weren't required.
These have now been deleted.
However, the sys.syscolpars table is still considerably larger than I would expect.
sp_spaceused is reporting that it currently has just over 4 million rows, and data size of 600,000KB (or thereabouts)
However, the index_size is being reported as nearly 17GB, and the unused field is reported as nearly 18GB.
Reserved shows as 35GB.
This doesn't seem right to me.
On the assumption that the Index_size and unused fields aren't accurate, what's the best way of resolving them?
As I understand it, i can't reindex system tables, and I don't really want to start messing too much with system tables.
However, it seems wrong to have a 35GB system table when the actual data is only about 600MB.
Is this something that SQL will fix itself, over time?
Any help would be greatly appreciated
Edit: is there any way to change the subject line so that it makes more sense?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Yowch! What on this good green Earth made you look for such a thing? I don't have an answer but I'd be interested in an answer on this one, myself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Firstly see if you can force a ghost cleanup. Connect via the DAC and query the table (select * from sys.syscolpars), see if that gets the (presumedly) ghosted rows removed. You may have to do it a few times and wait a while. Also check sys,dm_exec_requests to see if the ghost cleanup is running.

How many objects are in the DB now? (select count(*) from sys.objects)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Just consulted with an expert. No way to do what you want (assuming that the ghost cleanup doesn't fix, which it may well not)

Create new DB, script all objects, create objects in new DB, export data from old DB, import into new, make sure app doesn't do something stupid like this again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Smith-163358 SSCommitted Points: 1750 More actions · Answer 4

Thank you very much for your help.

It appears that overnight it has sorted itself out.

I now have an index_size similar to the size of the data.

The unused size is still about 5GB, but that's really not a problem.

The overall size of the table has gone from about 35GB to about 6.5GB, which I'm happy with for now.

Thank you for your help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Good to hear.

That was probably from ghost cleanup processing all the ghosted rows and being able to deallocate the pages afterwards.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass