Shrinking log files of formerly replicated DBs

To start, I should note that a) there's probably a better way to do this, and b) I'm sure this is already out on the net somewhere, possibly even on my old blog as a post from Mike Forman. But this way works, it's fast, and I couldn't Google it when I needed it, so I'm posting it again.

In our QA Static and Dev environments, we have static copies of a DB that is a replication subscriber in Production. This DB gets refreshed (restored) from Production every few weeks, or when I break everything and have to restore a fresh copy. When restored, it still has transactions marked as pending replication in the log, and so the log cannot be shrunk.

Naturally, we don't have as much space as we'd like in QA and Dev, and since the DB in question is static, it doesn't need the 50 GB transaction log that it has in Production. So I wanted to shrink the transaction log, but was prevented from doing so by this error:

Cannot shrink log file 4 (Test_Log1) because all logical log files are in use.

So how do you fix this?

My solution was to fool (read: hack) the system tables to make SQL think this DB was still a replication subscriber, then use sp_repldone to set all the transactions to done. This short script will do it, although I recommend you BE CAREFUL, because this is the master database you're messing with.

sp_configure 'allow updates', 1
reconfigure with override

update master.dbo.sysdatabases set category = 1 --
where dbid = 10 -- replace with appropriate dbid for your DB

use Test
GO

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

update master.dbo.sysdatabases set category = 0
where dbid = 10 -- ditto

sp_configure 'allow updates', 0
reconfigure with override

After the sp_repldone, you'll be able to shrink the log file to your heart's content. There may be another way to do this, such as attaching the DB without its log file, but I'm not sure that works, and besides, I like hacking system tables.