Issues and Bugs

I’ve covered Incremental Statistics over a couple of posts now and although they are a fantastic feature, there is a bug which you need to be aware of. Luckily it’s by no means a showstopper, more just an annoyance.

Okay, we all know that Sampled Statistics are not perfect otherwise there would be no need for a Full Scan stats update. However, there’s an issue I encountered recently that has really bothered me in regard to these.

Now, anyone who’s read enough of my blog will probably know that I’m not too fond of the Table Variable as I see it being used too often and generally in a way that it’s not designed.

This post comes about due to my spending an annoying large amount of time on a code failure that turned out to be utterly infuriating, but ultimately interesting as well.

This came about as a result of a series of mass updates being applied to a replicated table. This series of updates was causing replication to lock up and become so far behind that the only acceptable course of action was a full rebuild. This is by far and away the course of action I least like taking, but in this example it was unavoidable.

I had an interesting problem appear this morning in which the tempDB Log file began to grow… and by grow I mean it went from just 10GB to well over 150GB in one morning and showed no signs of stopping.

DBCC SHRINKFILE, DBCC SHRINKDATABASE, and Auto-shrink… they’re all truly, truly evil and should not be allowed near any system… ever!!!

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.

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.

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.

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.

I was working on one site when we got a very strange replication error appear. I’ve still no idea what caused it, but that won’t stop me posting the error message itself along with the simple solution…