Cody Konior Senior SQL Server Database Administrator Spider photographer, stand-up comedian, astronomer, and occasional beekeeper.

A few quick notes on SQL Server 2012 slipstreaming

SQL Server 2008 allowed you to slipstream service packs and cumulative updates into the installation media. In 2012 an additional and better method was created called "Product Updates" and (later) "Servicing Updates". PSS wrote a decent blog post introducing the feature.

Leaf Curling Spider

Leaf Curling Spider photo

Unidentified Spider

Unidentified Spider photo

Jumping Spider

Jumping Spider photo

Three cases where Ola Hallengren's Maintenance Solution won't backup a database

We love Ola Hallengren's Maintenance Solution but you should always always double-check either the msdb backup history or the master.dbo.CommandLog table to make sure any important backup was taken. This is especially important if you trigger it manually and are relying on human input to get the parameters right.

Here are three easy to miss cases where the scripts won't backup a database. These absolutely, definitely, aren't bugs, they're idiosyncrasies with the underlying backup command and (sometimes) how the script works. But they're also much easier to miss in the verbose output of the script.

This is how to add a database to an Availability Group, in reverse!

Normally when you add a database to an Availability Group (AG) you need to start with the database on the primary node. You restore backups With Norecovery on the secondary (either manually or with the GUI), add the database to the AG on the primary, alter the database on the secondary to reference the AG, and your job is done.

Sometimes after all the preparation you discover the wrong node is the AG primary, and you can't justify failing everything over just to add one new database in. But if you can tolerate a short outage to the database you're adding, then you can add it to the AG "in reverse" using a tail-log backup!

Before migrating to Availability Groups check your log backups

Warning: Please read this thread first which indicates sys.fn_dump_dblog is an internal undocumented command which can cause serious issues due to a threading bug. It's "less buggy" as of SQL 2012 SP2 and SQL 2014.

Now on with the fun and possibly dangerous code you're totally going to run only in unimportant environments that you can restart afterwards!

Identify agent jobs running retrospectively

When investigating an issue one of the first things you might check for is which agent jobs were running at the time. Assuming your job history retention period is long enough, here's one way to do it.

Copy SCOM sys.messages between servers

When SCOM installs its databases it also creates a bunch of sys.messages. This can cause trouble when you migrate SCOM to another server or when setting up an AlwaysOn Availability Group; because after failover the messages aren't available.

Automatic Availability Groups for Sharepoint on SQL Server

One of the downsides of AlwaysOn Availability Groups (AOAG) is how new databases are not automatically added to a group and protected; unlike traditional Failover Cluster Instances with shared disks where everything is automatically protected from outages and with no further setup required.

But it's not difficult to change this.

New site design, please report issues

I'm in the process of converting from the HMFAYASAL Omega theme to Ren Yuan's Leonids theme which frankly looks amazing. It's at the point where I think it's stable and have gone live with it. I'm still going through doing comparisons from the old and new site but haven't found anything significant thus far.

Visualise the monitor hierarchy that contributes to a SCOM computer object's health state

When SCOM is showing multiple computer objects in a critical state it can be very time consuming to use the GUI to open Health Explorer for each computer and then drill down into what the individual problems are.

Mealybug Destroyer (native Ladybird)

Mealybug Destroyer (native Ladybird) photo

A few quick notes on real world database corruption

I came across a System Center Operations Manager (SCOM) database recently running on SQL 2012 SP1, which was known for data corruption issues.

Rebuilding the "Log on as a service" list after it has been overwritten by Group Policy

Updated 2017-04-26: Removed "gpupdate /force" from the end of the sample script. This can overwrite the changes you just made with the group policy you were trying to avoid in the first place!

This is how to delete corrupt Management Data Warehouse cache files automatically

Updated 2016-01-06: Added an extra Job_Message check.

I've been experimenting with with the Management Data Warehouse (MDW) feature which is extremely important in SQL 2014 and above because it includes the "Analysis, Migrate, and Report" (AMR) tool. Here are some excellent examples of how it works:

Prowling Spider

Prowling Spider photo

A cool way to check Reporting Services memory usage

I recently came across a reporting server whose memory was a touch too low and would cause a particularly large report to sometimes fail to execute to completion.

This is how to shred Extended Event XML faster

Note: This applies largely to ring buffers, not trace files.

A script to help delete backup history on SQL Server 2000

Every now and again I'll come across a SQL Server 2000 install which hasn't been touched in forever and has no maintenance scheduled, and I have to clear up the backup history tables manually.