SQL Server Blog

This occured on a client's machine after they had migrated their SQL Server to new hardware with a fresh install of Windows Server 2008 R2. They attempted to use a linked server and received the following error message:

This is a topic which I encountered myself a few months back and it drove me mad for a while, but the solution is actually incredibly simple.

There are numerous variants of sp_who2 kicking around the internet and most of them go to absurd levels of intricacy.



For my situation, all I wanted was something that would show you a blocking chain and would also translate the SQL Server Agent Job name into plain English. That was all.

This is a bug I found within SQL 2008 (and R2) which has caused me endless problems within my system. I now have a workaround, but it’s clumsy and I really don’t appreciate having to do it.

Initially I would have said to set this within sp_configure because although it is CPU intensive, I have always found that the cost is more than covered by the level of reward.

A company I was visiting were having problems with the "Agent History Clean up" job that Microsoft installs as standard on a Distributor server. They had disabled the job because, as the job history showed, it was no longer completing as expected but was running forever and in doing so was causing the distribution database log file to rapidly increase in size until it Windows reported the disk as full. Once full they were having to kill the job, restart SQL Server (very risky as the job was now in rollback but couldn't comlpete due to disk space), truncate the transaction log, and then fix the backup chain for the sake of their disaster recovery requirements. This was clearly a huge problem for them as, without this job running, the distribution database would never stop increasing in size.

This is an issue I’ve only seen a few times in our system, but when it does appear the effects are very noticeable indeed.



What I witnessed was that replication suddenly slowed down on a machine and the CPU started to rise to uncomfortable and unusual levels.

This was something I came across when working in a heavily replicated environment… the replication created job “Distribution clean up: distribution” suddenly began to fail constantly. This led me on a trail which had such drastic results that I felt compelled to document it.

It's not a frequent occurence but times have arisen in which it's necessary to relocate the master database within your SQL Server. With SQL Server 2008 this is an incredibly simple operation and shouldn't cause you any problems.



This is surprisingly easy, in fact it’s a little too easy really.