SQL transaction log unexpecting grow on my published database
Problem description: The transaction log grow up without end.
Cause: After investigation we can confirm that the distributor database is with sync with backup option. And that cause the issue
Resolution: At this end to solve the issue, I remove this option that we don't need. I also check this property to all our publised database to be sure on the impact. And get a back in recovery model simple
EXECUTE sp_replicationdboption@dbname= 'DistributionDB',@optname= 'sync with backup' , @value= 'false'
But if you need it, you could also plan a backup log every hour and every day for your distributation base in recovery model FULL.
Work done : here I'm sharing my troubleshooting step that I did to understand this issue.
Step 1: I run the dbcc loginfo, and I found out that the number of VLF is increasing to 8135. Yesterday it was around 5000. That we can see that the status is 2, so almost all VLF are used and we are not able to truncate them.
Step 2: I had a look to see what the log is waiting to be reuse, we can see that we are pending replication.
Step 3: I had a look to the Log reader and it was running and replicating the transactions on the distributor database. We saw that inside the verbos log level 2 that we enable.
-Publisher [myPublisherServer] -PublisherDB [myPublisherDB] -Distributor [MyDistributorServer] -DistributorSecurityMode 1 -Continuous -OutputVerboseLevel 2 -Output C:\ReplLog\ReplOutput.txt
Step 4: I enabled the Tracer to see the latency, and we don’t have any latencies on this replication.
Step 5: “Oldest non-distributed LSN” does not change since a couple of days. I found that after runing several time a DBCC OPENTRAN :
Transaction information for database 'myDBName'.
Replicated Transaction Information:
Oldest distributed LSN : (1218321:2940:24)
Oldest non-distributed LSN : (1218321:2959:1) --> this part didn't change during serverals days
Step 6: Now the step was to see if the option synchWithBackup is enable the Distributor and the Publisher
On the distributor: select @@servername, DATABASEPROPERTYEX('DistributionDbName','IsSyncWithBackup')
This option was at TRUE
On the publisher : select @@SERVERNAME, DATABASEPROPERTYEX('publisherDBName','IsSyncWithBackup')
This option was at FALSE
For information this option means : The database is either a published database or a distribution database, and can be restored without disrupting transactional replication.
Step 7: To confirm the issue is due to this option, I put in the same recovery model the distributor database that the use db. Then I run a backup full an log
BACKUP DATABASE[distributionDB]TO DISK=N'C:\mydump.BAK';
BACKUP LOG[distributionDB]TO DISK=N'C:\mydump.TRN';
After this backup log all VLF on the published database become to 0 and the transaction log can be truncate now.
Michel Degremont | Xbox Live Music - Data Engineer - SQL Server & PDW |