An Introduction to Policy Management
Produced: 30/06/2014 17:31:00
This was a new feature added to SQL Server 2008 and it’s just superb. I honestly don’t think it gets the appreciation that it deserves as it seems to be rarely seen or used despite the control it can give you over your instances.

Database Mail Working But No SQL Agent Alerts
Produced: 23/06/2014 15:20:00
This is a common “problem” that I see within SQL Server and was asked about just yesterday, but 99% of the times, and with this particular instance as well, it’s actually not a problem at all just a missing setting within the SQL Server Agent.

How to Fire a Trigger on Demand
Produced: 16/06/2014 15:14:00
Now, first of all, let me say that I’ve rarely found a good reason for needing to bypass a trigger or only wanting to fire one under certain circumstances (I have done this myself, but only in a cross site dual distributor replication using service broker… therefore not exactly a common requirement!!!), however, it is possible to achieve and surprisingly simple to do.

SHRINK a data file? Just say NO!!!
Produced: 09/06/2014 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!!!

Pitfalls of the MERGE Statement and NULL Values
Produced: 02/06/2014 11:28:00
We all know and love the MERGE statement (if you don’t know it, then read this first), but it does have its quirks, especially when using null values and these can be a major problem if they go undetected as they can leave huge holes in your data if you’re not careful.

The MERGE Statement
Produced: 26/05/2014 11:07:00
So you’re working with some data and maybe you have another table which has newer, updated data... how do we combine these two tables to create one, correct, dataset?

Temp Tables vs Variable Tables
Produced: 19/05/2014 16:39:00
This is possibly one of the most contentious issues I’ve come across in SQL Server. If you look online then there are numerous people arguing numerous aspects of these two types of table and weighing the benefits and pitfalls of each. This includes where they’re stored, how they’re used, which is best etc.

Replication and the Disabled Index
Produced: 12/05/2014 17:53:00
This is something that I never thought to blog about as I figured it was a common practice. I’ve no idea where I got that idea from as I’d never spoken to anyone about it, just used it within a couple of environments.

Unable to begin a Distributed Transaction
Produced: 05/05/2014 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:

How to easily move a replicated database in SQL Server
Produced: 28/04/2014 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.

Blocking Chain
Produced: 21/04/2014 16:04:00
There are numerous variants of sp_who2 kicking around the internet and most of them go to absurd levels of intricacy.

SQL Server 2008 Swapping Database Names (sp_renameDB Bug)
Produced: 14/04/2014 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.

SQL Server 2008 backup compression... instance level or not?
Produced: 07/04/2014 16:34:00
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.

Agent History Cleanup - Distribution Transaction Log Full
Produced: 31/03/2014 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. 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.

Merge Replication Records Stuck with genStatus4
Produced: 24/03/2014 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.

SQL Server "Distribution clean up: distribution" Job Failing Large MSRepl_commands
Produced: 17/03/2014 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.

How to Move the Master Database in SQL Server 2008 R2
Produced: 10/03/2014 14:14:00
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.

How To Move The tempDB Database
Produced: 03/03/2014 12:45:00
New, faster disks arrived? Maybe you''ve done a reshuffle and ended up with a clean, unused physical drive? Either way your tempDB needs to be relocated. Doing so is actually incredibly simple and requires just a few short steps.

SQL Server AUTOCOMMIT within a while loop
Produced: 24/02/2014 13:15:00
This is just a little tip in case you ever have to make a lot of updates or inserts using a while loop and don’t mind a short amount of blocking while you do it.

Identity Management in Merge Replication
Produced: 17/02/2014 11:37:00
One of the most crucial and most complex stages in selecting the Article Properties of a table with an Identity column is how to utilise replication's Identity Range Management.

How to Move the Model and MSDB Databases in SQL Server 2008 R2
Produced: 10/02/2014 17:38:00
This came to light when I was working with a company who were struggling with their MSDB database. They had a requirement to maintain huge volumes of historical job data and backup information and didn't want to archive this to another database. Therefore this meant a large MSDB which had filled their drive and they needed to relocate it.

Copy-Only Backup
Produced: 03/02/2014 09:50:00
This is a crucial addition to SQL Server which is invaluable at times. It basically takes a snapshot of the database rather than an actual backup. Therefore it can be used to restore a database and to provide a copy, but crucially it does not break the backup chain.

Backup a Database Using TSQL
Produced: 27/01/2014 20:17:00
This is actually the cleanest and quickest way to produce a backup as it is a one line command in the form of:

Backup a Database Using SQL Server Management Studio
Produced: 20/01/2014 20:12:00
Making backups using the GUI is a little longer than when using T-SQL (here), but they do provide a much easier way to see and customise your backup options.

Creating a Simple Backup Maintenance Plan
Produced: 13/01/2014 19:23:00
It is possible and perfectly acceptable to put T-SQL commands into a scheduled task in order to create a full backup plan, but a Maintenance Plan is Microsoft’s way of allowing you the power of the GUI backup with the convenience of a scheduled task so that you don’t have to code your own.

Single Value Variables
Produced: 06/01/2014 19:02:00
These are incredibly useful when writing complex T-SQL or stored procedures etc. as they are very versatile and simple to use. They are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.