Post reply

Huge amount of index_size and unused in syscolpars

  • 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?

  • April 22, 2012 at 4:38 pm

    #1477219

    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)
    Intro to Tally Tables and Functions

  • April 22, 2012 at 5:24 pm

    #1477222

    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

  • April 22, 2012 at 6:07 pm

    #1477229

    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

  • April 23, 2012 at 4:03 am

    #1477368

    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

    #1477395

    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

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

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