April 22, 2012 at 12:31 pm
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?
April 22, 2012 at 4:38 pm
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
Change is inevitable... Change for the better is not.
April 22, 2012 at 5:24 pm
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
April 22, 2012 at 6:07 pm
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
April 23, 2012 at 4:03 am
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.
April 23, 2012 at 5:07 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply