MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































Reasons Behind Large Transaction Log File Size when database is configured for Snapshot Replication

Read Comments   |   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.

Recommendation

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

Conclusion

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....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server


Learn More...







Last Updated On: Dec 18, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: