Hello once again… today I am covering how to remove transactions that are marked for replication in the transaction log of a restored database. Now I have seen this occur in SQL Server 2000 so many times that I have created a T-SQL script that I execute step by step to ‘unmark’ these transactions so that I am able to shrink the log of the restored database.

Here is the scenario… there is a production database that is involved in replication and the developers want to restore the backup of this database to a development server to develop against. This database will no longer participate in replication while residing on the development server. Once the production database gets restored to the development server the database is still ‘marked’ for replication as well as the transactions that were in the log at the time of the full database backup. Now in order to conserve space on the development server the log needs to be shrunken down. When a DBCC ShrinkDatabase or DBCC ShrinkFile is executed on the database the transaction log does not truncate and the log does not shrink. This is what we do to fix this…

Step 1 - Make sure that the Recovery Model for the restored database is set to ‘Full’.

Step 2 - Make sure in the master.dbo.sysdatabases table (on the development server) that the category column for the restored database is set to 1 (the database is marked for replication).

Step 3 - Run the following T-SQL script in the restored database:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

This will ‘unmark’ the transactions (for replication) in the log

Step 4 - Backup the Log

BACKUP LOG <database name> WITH NO_LOG

Step 5 - Shrink the log

USE <database name> DBCC SHRINKFILE (N’<database name>_log’)

The log will now be down to a size reasonable size. To keep your log small on the development server, set the Recovery Model to ‘Simple’. Let me know how this process works for you!