Post reply

Distribution DB needs shrinking

  • March 3, 2008 at 2:32 am

    #71703

    I am running SQL 2000 with replication.

    The Distribution is taking up 5gb of disk space that I would like to reclaim at least some of. How do I shrink the actual DB size?

    I have run

    SELECT min_distretention, max_distRetention, History_Retention

    FROM msdb.dbo.MSdistributiondbs

    And get this:

    min_distretention = 0

    max_distRetention = 72

    History_Retention = 48

    Any thoughts?

    Thank

    Andy

  • March 3, 2008 at 12:57 pm

    #784641

    How much of that 5GB disk space is actually data vs empty space?

    Run sp_spaceused in the distribution database.

    You may be able to reclaim some space back without changing your retention settings for replication.

    However, the database may grow back to that size if it needs to, defeating the purpose of shrinking it. You need to identify whether it is regularly using most/all of that 5GB or whether it was a one-off occurrence that caused that growth (and if that one-off is likely to occur again).


    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare

  • March 3, 2008 at 3:34 pm

    #784700

    You may use

    DBCC SHRINKDATABASE to implement your task. However, you may not be able to release space occuppied by transactions, which have not been replicated yet.

  • March 4, 2008 at 1:43 am

    #784806

    Good Morning

    Thanks for the input. The database has got some spare capacity but only 400meg, otherwise it is full of data

    DBName...................distribution....................distribution

    Tablename..................MSsnapshot_history........MSmerge_history

    row...............................7992770.....................9983480

    reserved.....................1802480 KB.................2781976 KB

    data..........................1515984 KB.................2495016 KB

    IndexSize.....................261472 KB...................286712 KB

    Unused..........................25024 KB........................248 KB

    These two tables contain data going back 3 years, which suprised me :w00t: as I thought the retention would have been 5 days based on the pervious results.

    Generally speaking I did alot of work to limit the table transactions, so the replication only transfers approx 15-20k records per day

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

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