If you have this issue, I’ve put together a step-by-step solution in this post. The capture and cleanup jobs created are set up as if the database exists only on a single instance.Īnd that’s a problem, because I would guess quite lot of databases are in Availability Groups. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.If you’ve ever had to implement Change Data Capture (CDC) for a database in an Availability Group, then you know that the CDC jobs don’t really consider the Availability Group. The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Troubleshoot CDC instance errors in Microsoft change data capture for Oracle by Attunity. For example, run the following: BACKUP LOG your_cdc_database TO DISK='c:\folder\n'ĭBCC SHRINKFILE (yourcdcdatabase_log, 1024)įor more information, see Manage the size of the transaction log file. You should also be able to shrink the transaction log file to reduce the disk space that's consumed. Now you should be able to truncate the Transaction log by using log backups. This returns output that resembles the following: log_reuse_wait_desc name To make sure that the transaction log can be reused, confirm that there is no other reuse reason indicated on the database: select log_reuse_wait_desc, name from sys.databases where name = 'your_cdc_database' This returns output that resembles the following: No active open transactions. Run the following command to verify that the transaction is marked as replicated in the CDC database: DBCC OPENTRAN Using the numbers from step 1, run the sp_repldone command as follows to signal that BeginTran and CommitTran LSN pairs are already replicated: sp_repldone = 0x000000260000012C0001, = 0x0000002A000001B50001 You should receive output that resembles the following: xdesid xact_seqno xact_seqnoĠx000000260000012C0001 0x0000002A000001B50001Ĭopy the LSN transaction sequence numbers for the next command. Run the following command in a query window that's connected to the CDC-enabled database in SQL Server: exec sp_repltrans Select the log_reuse_wait_desc name from sys.databases, where the name is : REPLICATION If you run this query, the log_reuse_wait_desc option returns a value of REPLICATION, indicating the cause. This non-distributed LSN corresponds to the log entries to add the mirrored table in the Attunity CDC database. You may have a non-distributed LSN because CDC for Oracle uses CDC for SQL stored procedures, and that, in turn, uses the replication log reader. If DBCC printed error messages, contact your system administrator. You'll see a non-distributed LSN number as shown in the following example: Replicated Transaction Information:ĭBCC execution completed. To verify this exact cause, run the DBCC OPENTRAN command while you're connected to the SQL Server CDC database. Because the transaction doesn't have to be replicated in SQL Server, it's safe to manually mark the transaction as distributed by using the workaround that's described later in this article. However, because there's no SQL CDC replication involved when CDC for Oracle is used alone, there's no log reader to clear the transactions that are marked for replication. This behavior occurs because CDC for Oracle relies on underlying system stored procedures that resemble those that are used in CDC for SQL Server. When Change Data Capture for Oracle instances are configured, the SQL database that receives the change data will have mirrored tables, with transactions marked for replication. In this scenario, the SQL Server database transaction log file growth accumulates and consumes too much disk space over time. The transaction log on the SQL Server database grows, and transactions aren't marked for truncation as data changes are captured.The change capture values are stored in SQL Server change capture databases.You create a CDC instance to capture changes from Oracle database tables.You use Microsoft SQL Server 2017 on Windows, SQL Server 2016, 2014, or 2012 Change Data Capture for Oracle by Attunity.Original product version: SQL Server 2012 and the later versions Original KB number: 2871474 Symptoms This article helps you resolve the problem where you notice continuous transaction log growth for a CDC enabled database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |