How Do I Use a Heartbeat Table in HVR 6?

Question

I am capturing from SQL Server and my SQL Server transaction log keeps filling up.

This can occur when a subset of tables from a source database in an HVR channel remains idle, with no changes being applied to them. If there is a lot of activity (i.e. updates, inserts, deletes) on other tables outside of those in your channel, this will cause the SQL Server transaction log to grow. Since the capture process controls the secondary truncation point and is not releasing it enough due to little or no activity within the HVR channel, the SQL Server transaction log will continue to grow until it is released. This can cause potential issues, including a database crash if the log drive runs out of space.

How can I prevent the transaction from log filling up?

Environment

HVR 6

Answer

Create a heartbeat table in SQL Server and replicate it using HVR.

1. Connect to the source SQL Server database. Ensure you adjust any database-specific references in this example to fit your use case.

2. Create a table called hvr_heartbeat in your source database:

   USE [HVR6_SOURCE]
   GO
   CREATE TABLE [dbo].[hvr_heartbeat]( [hvr_timestamp] [datetime] NOT NULL)
   GO
   

3. Insert the initial timestamp into the heartbeat table:

   INSERT INTO [dbo].[hvr_heartbeat] VALUES(getdate())
   GO
   

4. Select the data to review it:

   SELECT * FROM [dbo].[hvr_heartbeat]
   

   

5. Create a SQL Server Agent Job to update the table every 10 minutes (adjust this frequency as needed based on how quickly the log fills up).

   • In SSMS, expand SQL Server Agent > Jobs and click on New Job:

     

   • On the General tab, provide a job name:

     

   • On the Steps tab, create a new job step:

     

   • Provide a Step name and paste the following DML in the Command box as shown below, then click OK:

     USE HVR6_SOURCE
     GO
     UPDATE [dbo].[hvr_heartbeat] SET [hvr_timestamp] = GETDATE()
     GO
     

     

6. Create and configure a new schedule:

   • On the Schedules tab, create a new schedule:

     

   • Configure the job schedule as shown below and click OK:

     

   • Click OK one more time to save the new job:

     

7. Now add the hvr_heartbeat table to your HVR channel:

   

8. Activate replication in your channel, ensuring that the following replication components are selected: Jobs, Table Enrollment, Supplemental Logging, and State Tables.

   

9. Perform Refresh in your channel to create the hvr_heartbeat table:

   

Once completed, HVR 6 will move the secondary log truncation point at least as often as the heartbeat table is updated.