Post reply

Distribution database

  • August 2, 2005 at 10:34 am

    #86981

    Hi there Can anyone help

     

     

     

    I have enabled transactional replication on a small database about 500 mb, however the distribution db grows rapidly it hit 2 gb inside an hour so I had to stop the log reader and the push agent to stop it from filling up the disk

     

    Unlike another user I do not have multiple instants of OSQL running

    thanks

    Pat

     

  • August 3, 2005 at 2:51 am

    #579051

    You probably have a transaction in publishing database that affects many rows, so generating many commands.

     

     

    Cristi.

  • August 3, 2005 at 3:39 am

    #579066

    Any idea how I investigate this ?

  • August 3, 2005 at 4:59 am

    #579089

    dbcc opentran

    I normally run my db's in full recovery model, dump every 5 minutes, and shrink the tlog. This prevents the ballooning tlogs, and autogrow of them.

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

  • August 5, 2005 at 2:34 am

    #579932

    This is not a problem for tlog in publishing database.

    The problem discused here was that the distribution database is very large.

    This situation is happening even when you have few transactions running daily aggainst publishing database. For example:

    "Update clients

    set isactive=0"

    Say this transaction affects 2.000.000 rows, then this will generate 2.000.000 rows in the distribution database.

    To investigate the transactions pending for replication in the distribution database, you could use sp_browsereplcmds to see them in a readable format.

  • August 5, 2005 at 4:51 am

    #579947

    I am talking about the distribution database

    Another point, set your retention period to something smaller than the 14 days for your publications if you can, set your distribution Transaction Retention to something small. It should be 0.

    You might also want to look at replicating the execution of stored procedures. Depending on your update activity this can result in better performance and less use of the distribution database.

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

     

     

  • August 5, 2005 at 7:51 am

    #579997

    If you are talking about the distribution database, then I don't understand why your recovery model is Full, Hilary.

  • August 24, 2005 at 4:25 am

    #584525

    Hi there

     

    Do I change the retention period in the settings at the ende of the distribution cleanup job or do I do it in the stored procedur eitself ?

     

    Cheers

     

    Pat

  • August 24, 2005 at 4:59 am

    #584531

    It doesn't matter when you change the retenetion settings. They will become effective the next time the agent runs.

    Regarding putting the distribution database in full recovery model. I find that I get more efficient transaction log management with frequent dumps that by using the simple recovery model.

    There are advantages to having small tlogs.

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

     

     

  • August 24, 2005 at 6:06 am

    #584547

    Hi Hilary

    I was not very clear in my question

    Should I edit the cleanup task in the jobs section where the max retention size is appended after the MSdistribution_cleanup sp is specified ?

    or should I change it directly in the SP

    Also is 1 hour a good figure for the max retention size ?

    Cheers

    Pat

Viewing 10 posts - 1 through 9 (of 9 total)

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