Post reply

Table size grows but with has zero rows

  • May 17, 2010 at 6:08 pm

    #103940

    Hi All,

    I have a db which grows in size very quickly. After analysis with sp_spaceused, there is one table which is clearly the culprit.

    It is used to log only a couple of records for a job which runs about every 5 mins, however, the stats for this table are as follows:

    name......rows....reserved........data..............index_size.....unused

    WSLog....0........4625320 KB.....4600368 KB....22144 KB.......2808 KB

    It seems to be reserving space within the table, but ... (don't know what to add here - sorry).

    We dropped the table and re-created it and the stats returned to normal.

    Does anyone have any ideas about how the first set of stats can arise (the job writing to the log is definitely not in a loop)?

    Is there a way of monitoring these sort of stats, because I'm concerned it may grow like that again.

    I'd be very grateful for any help

    Thanks, James

  • May 17, 2010 at 6:53 pm

    #1168545

    Did you, by any chance, do a SELECT COUNT(*) from the table to make sure that sp_SpaceUsed isn't "lying" to you?

    --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)

  • May 17, 2010 at 8:46 pm

    #1168560

    Jeff (and any others who may have been having a think about this)

    Thanks for your input.

    After more exhaustive testing of the devlopers code, a loop was found for a specific data condition. This, in turn, set the code into an ucontrolled insert loop.

    Further controlled tests of this loop, showed that if the SP is aborted (either by crashing or operator intervention), then the rows will show zero and the reserved space will be retained.

    This is exactly what we experienced. Restarting the SQL Server Service, seemed to free up that reserved space.

    Once again, thanks and my apologies if anyone feels I wasted their time.

    James

  • May 17, 2010 at 9:02 pm

    #1168563

    james.mcallester (5/17/2010)

    Jeff (and any others who may have been having a think about this)

    Thanks for your input.

    After more exhaustive testing of the devlopers code, a loop was found for a specific data condition. This, in turn, set the code into an ucontrolled insert loop.

    Further controlled tests of this loop, showed that if the SP is aborted (either by crashing or operator intervention), then the rows will show zero and the reserved space will be retained.

    This is exactly what we experienced. Restarting the SQL Server Service, seemed to free up that reserved space.

    Once again, thanks and my apologies if anyone feels I wasted their time.

    James

    Heh... been there and done that and it's definitely not a waste of time to find out what happened. Thanks for posting what you did.

    --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)

Viewing 4 posts - 1 through 3 (of 3 total)

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