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

select @@SERVERNAME,DATABASEPROPERTYEX('DistributionDB','IsSyncWithBackup')

EXECUTE sp_replicationdboption@dbname=  'DistributionDB',@optname=  'sync with backup'  ,  @value=  'false'

select @@SERVERNAME,DATABASEPROPERTYEX('DistributionDB','IsSyncWithBackup')

 
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.

select name,log_reuse_wait_descfromsys.databaseswherename='myDBName'

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.

Regards,

Michel Degremont | Xbox Live Music - Data Engineer - SQL Server & PDW |

Comments

• Anonymous
  February 25, 2016
  Nice post!