Post reply

Space is not reclaimed in distrubution database

  • January 26, 2009 at 9:27 am

    #217952

    Hello!

    I have setup transactional replication (4 publications) between two SQL 2005 servers. Distributor is on the same server as Publisher. Distribution jobs are running once a day. I have noticed that space consumed in distribution database keeps growing. Even after I manually execute distrubitoon jobs space is not reclaimed. When I check 'Undistrubuted commands' in Replication monitor right after synchronization with Subscriber it shows me 0. I was wondering when and how space is being reclaimed in distribution database. My understanding is that once transactions are delivered to Subscriber, they should be removed from distribution database.

    Any help is greatly appreciated.

    Thanks,

    Igor

  • January 26, 2009 at 6:49 pm

    #932944

    How about running this query

    use distribution -- assuming this is your distribution db name

    select name, total_space_MB=size/128.0, Used_space_MB= fileproperty(name, 'spaceused')/128.0

    from sys.database_files

    and please post back the results and we can diagnose what happens.

    HTH,

    JY

  • January 27, 2009 at 7:05 am

    #933153

    Let us say that you did an update on a big table row by row. All these commands are written to the distribution DB. Just because the Commands have been executed and the commands are deleted from the distribution does not mean that it will reclaim the space used by the DB. (From the best of my knowledge)

    -Roy

  • January 27, 2009 at 8:45 am

    #933243

    Distribution database is like any other DB. If you insert a lot of commands it has to grow.

    File size management is up to you!


    * Noel

  • January 27, 2009 at 9:25 am

    #933278

    Hello!

    Thanks for your replies! I did figure out that because of my retention policy (72) in distribution database, old transactions haven't been deleted immediately. I see that space is being reclaimed now.

    Thanks,

    Igor

  • January 27, 2009 at 9:27 am

    #933283

    Good you figure it out.

    Retention policy > 0 is usually not a bad thing when not a lot of transactions go through the distribution DB. It is not that good when the opposite happens.


    * Noel

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

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