August 2, 2005 at 10:34 am
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
You probably have a transaction in publishing database that affects many rows, so generating many commands.
Cristi.
August 3, 2005 at 3:39 am
Any idea how I investigate this ?
August 3, 2005 at 4:59 am
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
August 5, 2005 at 2:34 am
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
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
August 5, 2005 at 7:51 am
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
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
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
August 24, 2005 at 6:06 am
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