May 21, 2014 at 3:11 pm
We have a need to offline (or detach) a database that is a subscriber to a publisher on the same server. We will either be bringing the database back online or dropping the database depending on whether or not it is needed and people/jobs complain. We have run traces and believe it is not used but won't know 100% until we offline the DB.
We are wondering what will be the fallout. I assume the distribution database will grow while the DB is offline. Any other gotchas or things to look out for?
Is there a preferred process to use so replication can be turned back on should we need the database? The database is quite large and will take too much time to start from scratch.
Thanks
May 22, 2014 at 3:49 am
Hi
If you're using transactional replication, the log file for the publishing database will probably grow too as the VLFs inside that log file won't be marked as inactive (and therefore reusable) until the distribution database has distributed the transactions. It would be best to unsubscribe any articles before taking the the subscribing database offline.
Hope that helps.
May 22, 2014 at 4:16 am
NickBalaam (5/22/2014)
If you're using transactional replication, the log file for the publishing database will probably grow too as the VLFs inside that log file won't be marked as inactive (and therefore reusable) until the distribution database has distributed the transactions.
No.
As long as the log reader agent is running and copying the replicated transactions to the distributor, the log will be marked reusable as usual. It's not the absence of the subscriber that causes a publisher's log to grow, it's the absence of the distributor.
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
May 22, 2014 at 4:18 am
DentalDBA (5/21/2014)
We are wondering what will be the fallout. I assume the distribution database will grow while the DB is offline.
Probably not even that. Replication has a retention period, it's how long the transactions are stored at the distributor, so there shouldn't be any growth as it's normal to retain transactions. Unless you keep the subscriber offline for longer than the retention period (which is, iirc, by default 2 weeks), there shouldn't be any impact. If you do keep it offline for longer than that, the subscription will expire and you'll have to reinitialise the subscriber.
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
May 22, 2014 at 5:56 am
Ah right I see. I thought if the transactions had not been distributed to the subscriber (via the distributor) then they wouldn't. But that's not the case. You're saying that as long as the log reader agent has sent the transactions to the distributor, then it doesn't matter whether or not the distributor sends them to the subscriber, then the VLFs will be reusable. If I've understood correctly?
May 22, 2014 at 6:01 am
Yup.
Doesn't matter whether there is a subscriber. Doesn't matter if there are ten suppliers and half of them are offline. The log reader agent reads the publisher's transaction log and sends the 'for replication' entries to the distributor.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply