August 8, 2019 at 12:04 pm
The title really says it all here. I've seen a lot of references as to why the log could grow on the publisher but I can't really find anything that explains why it would happen on the subscriber.
Our data warehouse server holds copies of several of our production databases all of which are in simple recovery. These are replicated from various publishers, all of which are in full recovery. As part of some routine drive space monitoring it became apparent that the log drive on the data warehouse was slowly but steadily filling up. I established that there were auto-growth events occurring every couple of days on two of the databases adding 512Mb of space each time. It appears that they were being caused by the push replication job. A bit more investigation lead to the discovery that replication can cause the log to grow on publishers that are in simple recovery but I'm not sure why it would happen on a subscriber. I can't see any errors on either the (standalone) distribution server or the production box from which these two databases are replicated. Both of the databases showing the issue are on the same publisher which seems a little coincidental but I'm not assuming anything.
Investigation is slightly hampered by the fact that the subscriber has been recently re-started as part of a patching program. This has obviously cleared the log. Due to unidentified fat-fingered gremlins, the initial size of this log is truly colossal and we now have a 124GB log that's largely composed of empty space. This now means the auto-growth events are (hopefully) not going to happen again for some time. However this does mean I'm having to keep a closer eye on the free space in the file to find the culprits. At the moment though we're only capturing the file size data on a daily basis so I can't monitor things as precisely.
Can anybody shed any light on this?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
August 8, 2019 at 5:05 pm
What sort of replication was set up? Transactional replication, or snapshot? Under transactional replication this is difficult to understand, unless you are somehow getting index rebuild commands sent to you by the publisher. Snapshot replication may explain it, as the data would be bulk copied each time. The size of the log may be getting dictated by the size of the largest table in the snapshot replication.
August 8, 2019 at 6:57 pm
It's transactional replication. We do rebuild indexes on the publisher but they shouldn't get replicated. The other reason this is unlikely is the timings of the growth events doesn't coincide with the rebuilds, however never say never. I'm off for a couple of weeks now so it'll be a while until I can get back to this.
It's very puzzling.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
August 8, 2019 at 7:15 pm
the timing of any transaction at the subscriber would depend on the subscription agent running. It could be that the subscriber agent is set to run on a schedule, or the subscriber can not keep up with a sudden flood of requests it has to get through from the distributor. Monitoring what is going on when the log appears to grow would be the best bet. It may be you have a set of "materialized views" in the database, and when they get rebuilt, the log will expand.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply