SQL Server: Truncate the Transaction Log
Check Which Type of Replication is in Use
Before truncating the transaction log, first check whether the source is using MS-CDC or MS-REPLICATION to replicate changes to Etleap.
Run the following SQL query to see whether the CDC capture job is running for the database:
USE <db name>;
EXEC sys.sp_cdc_help_jobs;If MS-CDC is enabled on the source, you should see an entry where job_type is capture.
In this case, the capture job can be configured directly.
If there is no capture job present, then MS-REPLICATION is being used instead, and Etleap’s CDC process will use a LogReader agent installed on the source to capture changes.
In this case, the LogReader agent must be configured via SQL Server Management Studio (SSMS).
If both MS-REPLICATION and MS-CDC are in use, the Log Reader Agent will be solely responsible for reading from the transaction log and you should follow the MS-REPLICATION steps below.
Truncate the Log
Follow the steps for either MS-REPLICATION or MS-CDC depending on which is in use on the source.
using MS-REPLICATION
You need the sysadmin fixed server role or the db_owner fixed database role to truncate the log.
Open SQL Server Management Studio and follow the steps below to truncate the transaction logs:
- Right-click the Database and select Properties → Options.
- Set the recovery model to
Simpleand exit the menu. - Right-click the database again and select Tasks → Shrink → Files.
- Change the type to
Log. - Under Shrink Action, select Reorganize pages before releasing unused space and click OK.
- When the process completes, switch the recovery model back to
FullorBulk-Loggedand take a full database backup.