August 19, 2011 at 7:36 am
we have a prod server replicating to a subscriber server(both transactional and snapshot). The weekend reorg on prod/publisher DB caused big log backups on the prod/publisher server. Later found that while the reorg was taking place, the subscriber server was also running low on space.
I found that the files for replication and the log file of the subscriber DB are on the same drive. The subscriber DB is in simple mode though. Was not sure how much the transaction log would have contributed to running the drive low. I assumed this was more of the replication files filling the drive, and was going to schedule backup after the reorg on subscriber.
I was going to change the replication folder to another drive and reinitialize replication, but wanted to identify what could have been the factor behind the growth of the drive to about 140 GB (150 GB total) from 50 GB. The log file is now about 3 GB or so.
August 19, 2011 at 9:04 am
The rebuilds are logged, so I would assume they flowed through replication. Since a rebuild is one big transaction, simple mode doesn't help. Simple still needs a peak log size (peak amount of transactions).
If you had snapshot, those files shouldn't be larger because of the rebuild, but they can eat up space. Potentially there was disk usage from both.
I would monitor these by forcing the rebuild and snapshot at a similar time to see if they combine to use more space. Perhaps you can move schedules around to prevent this.
August 19, 2011 at 12:19 pm
Thank You Steve.
I am trying to move replication folder to another drive. They have been using snapshot and transactional replication. Certainly was not good to find the log file of subscriber database.
Also, I hope that would spare the log file. Would I need to put a job to put the database into full mode, run a log backup with truncate and then put it back into simple? However, the re-org was still running well past the time this alert was generated.
Interestingly enough, there was a purge a day later, which resulted in similar production/publisher log file and log backup size growth. This didn't seem to have impacted the subscriber database. Atleast there were no alerts.
August 19, 2011 at 12:30 pm
didier41 (8/19/2011)
Also, I hope that would spare the log file. Would I need to put a job to put the database into full mode, run a log backup with truncate and then put it back into simple?
No point. The log is truncated on a regular basis in simple recovery model. If you put the DB into full you're requiring a log backup to do that truncation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2011 at 5:47 pm
got it. Thank You Gail. I guess I was thinking auto shrink needs to be on for that. You are right. Anyways, I have mentioned to the app team they would need to have transaction log file and replication in separate drives.
August 20, 2011 at 3:28 am
Nope, and you definitely do not want autoshrink on ever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2011 at 9:33 pm
FYI : replication is, typically, only interested in DML commands. Index rebuilds and the like are not of interest to replication. If you need to perform these sort of operations, they would need to be done on both publisher and subcriber. I believe that this is by design since the definition of each database may be different (especially if you are replicating to a reporting server).
Replication may cause log records to be retained in the publishing database if the logreader agent is not running (for transactional replication).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply