Bug: Implementing Incremental Statistics
Produced: 28/08/2016 18:36:00
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.


Be Very Careful with Sampled Statistics
Produced: 15/05/2016 16:03:00
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.

Transactions and Table Variables
Produced: 10/01/2016 19:53:00
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.

Well, this is yet another reason why I’m not a fan.

Naming Constraints on Temp Tables
Produced: 26/04/2015 18:13:00
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.

It involves the use of named constraints within temp tables.

Which Columns Are Being Updated
Produced: 15/06/2014 10:14:00
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.

Huge tempDB Log File
Produced: 08/06/2014 16:27:00
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.

I’m used to the data file growing as required, but it’s incredibly rare I see such behaviour from the log.

SHRINK a data file? Just say NO!!!
Produced: 22/12/2013 14:47:00
DBCC SHRINKFILE, DBCC SHRINKDATABASE, and Auto-shrink… they’re all truly, truly evil and should not be allowed near any system… ever!!!

Now, to be fair there are times when shrinking a log file is useful and even necessary. I’ve written my fair share of bug riddled code which has caused the transaction log to spiral into oblivion and leave me with a 20GB data file and 500GB of logs. In that case… shrink away. It’s really quite handy. But data files… no… just no… again no… please just no!!!!!!

Unable to begin a Distributed Transaction
Produced: 17/11/2013 18:13:00
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:

OLE DB provider "SQLNCLI" for linked server "X" returned message "No transaction is active".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "X" was unable to begin a distributed transaction.

How to easily move a replicated database in SQL Server
Produced: 10/11/2013 14:48:00
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.

Normally it’s a simple task of “detach -> move file -> attach” and you’re done. But of course this doesn’t work with published databases as you get the error:

SQL Server 2008 Swapping Database Names (sp_renameDB Bug)
Produced: 27/10/2013 19:08:00
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.

Agent History Cleanup - Distribution Transaction Log Full
Produced: 13/10/2013 20:45:00
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.

Merge Replication Records Stuck with genStatus4
Produced: 06/10/2013 14:40:00
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.

SQL Server "Distribution clean up: distribution" Job Failing Large MSRepl_commands
Produced: 29/09/2013 16:04:00
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 had a look around on the server in which this job was failing and found that the MSRepl_Commands table was HUGE!!! (This is the table which holds all commands to be replicated).

Could not find stored procedure dbo.sp_MScheckIsPubOfSub
Produced: 07/07/2013 15:46:00
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…

This was the error…