Blocking Chain script

I had a need to follow some quickly changing blocking chains today, and couldn't find any simple blocking chain scripts that I liked, so I wrote one. It's not the prettiest method, but it's simple, easy to modify, and doesn't rely on lots of hard-to-debug dynamic SQL.

The next updates I'll probably make will be to display only the top-level blockers rather than all blocking chains, and to get rid of the 0s at the end of each blocking chain line.

Here's the SQL:

if object_id('tempdb..#blockers0') is not null
drop table #blockingchain

create table #blockingchain
(
blockingchain varchar(255)
)

insert into #blockingchain
select cast(spid as varchar(5)) + ' -> ' + cast(blocked as varchar(5))
from master..sysprocesses
where blocked <> 0

while exists (select 1 from #blockingchain where cast((substring(right(blockingchain, 5), 1 + charindex('>', right(blockingchain, 5)), 5)) as smallint) <> 0)
begin

update bc set blockingchain = blockingchain + ' -> ' + cast(sp.blocked as varchar(5))
from #blockingchain bc
inner join master..sysprocesses sp
on cast((substring(right(bc.blockingchain, 5), 1 + charindex('>', right(bc.blockingchain, 5)), 5)) as smallint) = sp.spid

end

select * from #blockingchain
order by len(blockingchain) asc,
substring(blockingchain, 1, charindex('-', blockingchain)) asc

Sample output:

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.