SQL Server Blog

Yesterday I had an issue in which someone had accidentally run a tidy up script on a transactionally replicated table without considering the consequences this would have on replication.



Effectively what they had done was delete 2.5 million records in one transaction which amounted to around 5GB of data changes heading into the replication network. Not good.

I’ve had numerous occasions in which I see something going wrong with replication, but rarely are the error messages clear and, to be honest, most of these messages simply specify something along the line of “enable verbose logging”.

This is genuinely one of the things I’m most asked, and by all manner of people from DBAs to Devs because it’s never as simple as it looks and that is how to obtain the start of day from a current_timestamp (or any other datetime value).

In the second episode of Sheldon Cooper’s Fun with Flags, we’re going to be looking for deadlocks.



There are numerous ways of tracking deadlocks within SQL Server but as we rarely know when they’re going to occur, don’t want traces running all day, and want to react quickly, the best method I’ve found is to have them sent to me in an email each time they appear.

Obviously I would be shocked if you don’t already have something in place which will let you know that your SQL Server Cluster has failed over to another node. But I always like to have my own backups to any other automated alerting in order to keep me informed.

I’ve complained before about Scalar Value Functions and how much I dislike them but, in case you weren’t convinced the first time around, here’s another reason for you.



Scalar Value Functions stop the optimizer from using parallelism.

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.



The problem was that we had no clue as to the underlying cause. All developers were certain that any recent changes would not have had this effect and therefore we were in the dark. The only way to get to the bottom of it was to track which columns were being updated and by what in order to verify whether these updates were valid and necessary.

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.

This topic came about the other day when I was required to move some tables into a completely new database as part of a schema restructure and new project.