Post reply

Calculate physical space taken up by certain rows

  • February 4, 2010 at 11:15 pm

    #142153

    Hi,

    We have a table which is 48 GB in size.

    I have used EXEC sp_spaceused 'TableNameHere' to get this number.

    We want to archive most of the records and only keep those for 2009 and up. I need to calculate how much space will be freed up if we archive per year.

    I need to provide a report that indicates: x GB will be gained by archiving 2006, x GB for 2007, etc

    How do I calculate this physical space taken up by certain records?

    Any help please?

  • February 5, 2010 at 1:34 am

    #1114231

    It depends how many rows are there in the table prior to 2009.

    I had a similar situation long back with a table size around 36gb.

    I did a select into a temp table into a newly created database for the rows qualifying for purging ,the size was around 20. so i reverted back to management saying 20+ gb we will recover. Just remember you are also going to recover space from indexes and unused space also.

    After purging the data you will notice lot of unused space , probably higher than the datasize+index size.

    Try rebuilding the indexes, update statistics if this also doesn't help in getting rid of the unused space try rebuilding the table and the indexes. This can truly become painful if there are lot of RI on the table.

  • February 5, 2010 at 11:39 am

    #1114671

    Casper,

    Use the column named 'AVERAGE_BYTES_PER_ROW' generated by attached script and multiply it with number of rows that qualifies for deletion for that particular year. It will give you an estimate.

    Note:- Attached script has @updateusage = 'true' in it so it might take some time when you run it.

    MJ

  • February 5, 2010 at 8:46 pm

    #1114904

    Make a swag... count the number of rows per month and then extrapolate against the total number of bytes the table and its indexes hold. For example... if a table had 4 months of info in it and the total table size is 10 gig and the monthly number of row worked out to be...

    Month Rows

    1 2k

    2 2k

    3 3k

    4 3k

    -----

    10k rows total

    ... and you wanted to get rid of all but months 3 and 4...

    10GB/10Krows = 1GB per KRow.

    Months 1 and 2 add up to 4k rows. 1GB per KRow * 4KRow = 4GB savings.\

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