How To Mark A Database For Replication in SQL Server 2000

Today I am going to cover how to mark a database in SQL Server 2000 for replication. This is important when you are attempting to shrink a transaction log that has transaction marked for replication but have not been flushed out of the log yet. I have come across this situation when restoring a backup of a replicated production database to a staging environment or a development environment. Once you get the database restored to your destination server the first thing you want to do is to allow access to the system tables. Here are the steps to accomplish ‘marking’ a database for replication:

Step 1 – Allow access to the system tables
use master
go
exec sp_configure ‘allow updates’, 1
reconfigure with override
go

Step 2 – Update the ‘category’ column in the master.dbo.sysdatabases table for the database you want to update.
update sysdatabases
set category = 1
where name = <database name>

Step 3 – Remove access to the system tables
use master
go
exec sp_configure ‘allow updates’, 0 — Does not allow updates
reconfigure with override
go

If you query the master.dbo.sysdatabases table now you will see the category column is now set to 1. You are able to remove the replicated transactions now and shrink the file. If you have another way to remove replicated transactions let me know!