Reasons Behind Large Transaction Log File Size when database is configured for Snapshot Replication
Related Topics: SQL Server Replication Tips>
Next Topic: SQL Server Indexing Articles & Tips
Why Transaction Log is Huge in Snapshot Replication?
One of the Production Database had Snapshot Replication configured on it and all of a sudden it was noticed that the Transaction log of the database kept on growing. Luckily there was plenty of free space available on the server for the Transaction log file to grow thereby avoiding any downtime. In this tip we will take a look at how to identify and fix sudden surge of transaction log file especially when a database is configured for Snapshot Replication.
Let us learn Why Transaction Log file size becomes huge when a database is configured for Snapshot Replication.
Step 1: Identify the cause of Transaction Log File Usage as of last CHECKPOINT
One can find the cause of transaction log file usage from log_space_reuse_wait column of sys.databases database catalog view. The log_space_reuse_wait column describes the reason why the database cannot reuse the transaction log space as of last checkpoint.
SELECT name AS [Database Name] ,log_reuse_wait_desc AS [LOG Usage] ,recovery_model_desc AS [Recovery Model] ,state_desc AS [Database State] FROM SYS.DATABASES WHERE name = 'DatabaseName' GO
The above query returned REPLICATION (log_space_reuse_wait column) as the reason for the transaction log to grow. However, the database was configured for Snapshot Replication. Ironically, Snapshot Replication does not use Transaction logs to replicate data to the remote server.
Let us check then what is happening behind the scene in this scenerio.
Step 2: Identify if there are any Open Transactions within the Database which is configured for Snapshot Replication
To identify open transactions within a database you need to run DBCC OPENTRAN command.
Use DatabaseName GO DBCC OPENTRAN GO
The results of DBCC OPENTRAN command mentioned that there is an undistributed LSN.
Replicated Transaction Information: Oldest distributed LSN: (0:0:0) Oldest non-distributed LSN: (104:2751:1)
The DBCC OPENTRAN query output confirms that the above non-distributed transaction has caused the log growth. However, in a Snapshot Replication changes are replicated using snapshots and not using the transactional log.
This points out to a known issue with Snapshot Replication in
SQL Server 2005 i.e., if there is any SCHEMA change which is
marked for replication within transaction log of the database
then it is not unmarked when the changes are propagated. This
results in Transaction log file to grow resulting in this issue.
DBCC OPENTRAN provides us the LSN value in Decimal format. You can convert the values from decimal to hexadecimal.
Let us convert DBCC OPENTRAN output from Decimal to Hexadecimal
- 104 Decimal = 68 Hex
- 2751 Decimal = abf Hex
- 1 Decimal = 1 Hex
Hence the LSN output is 00000068:00000abf:0001
SELECT [Transaction ID] from fn_dblog(NULL, NULL) WHERE [Current LSN] = '00000068:00000abf:0001' GO
The above query has returned the following value "0000:00000068c" on our server. Let us check the transaction log file again to see the details of the transaction to see if there is any ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION, ALTER TRIGGER statements. For more information, see How to: Replicate Schema Changes (Replication Transact-SQL Programming).
SELECT * FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:00000068c' GO
The above query result showed that it was an ALTER TABLE statement. Hence, it confirms that the reason for transactional log file to grow was due to SCHEMA changes which were not getting propagated.
Let us now look at fixing this issue permanently on SQL Server.
Step 3: Fix REPLICATION OF SCHEMA CHANGES for the tables involved in Snapshot Replication
Execute the below TSQL Script against the database on which you have configured snapshot replication and had issues with transactional log file ever growing.
Use DatabaseName GO EXEC sp_repldone null, null, 0,0,1 GO
SP_REPLDONE is a safe command to run against a database which is configured to for Snapshot Replication as it marks all transactions as distributed. Once the above command is executed successfully run the SNAPSHOT AGENT Job.
Finally, you will be able to SHRINK transactional log file.
It is highly recommended to execute the below TSQL script to disable schema changes from being propagated to avoid this issue in future.
/* Below script will turn off replication of DDL changes from the Publisher Database */ USE DatabaseName GO DECLARE @Publication AS SYSNAME SET @Publication = N'SNAPSHOT REPLICATION - ADV' EXEC sp_changepublication @publication = @publication ,@property = N'replicate_ddl' ,@value = 0 GO
In this article we have see the reasons behind SQL Server Database Transaction Log file becomes large when database is configured for Snapshot Replication and how to fix this issue permanently.
Thank you for taking your time to read
this article. Let's be Connected....
- SQL Server Replication Tips and Articles for DBAs and Developers
- SQL Server Indexing Tips and Articles for DBAs and Developers
- How to Configure and Use Transparent Data Encryption in SQL Server
- How to Configure and Use Backup Encryption in SQL Server 2014
- Different Types of SQL Server Recovery Models
- Different Types of Database Backups supported in SQL Server
- Permissions Required to Perform Database Backup in SQL Server
- How to Perform FILEGROUP Backup in SQL Server a Step by Step Tutorial with Examples
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
- How to Perform TAIL-LOG Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: Dec 18, 2014
Please leave your Valuable Comment or Let us know how this article helped you: