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

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.

This is what sys.sp_change_users_login does

When you copy a database from one server to another one of the common tasks is to run sys.sp_change_users_login 'Report' and verify that there are no unexpected orphaned users; which occurs when a sys.database_principal (user) doesn't have a matching sys.server_principal (login).

Get-WmiObject replacement with timeout

Controlling WMI timeout behaviour becomes very important when you're parallelising queries across hundreds of servers. Even though other queries may execute quickly when one server fails to respond WMI can hang indefinitely and stop any further processing in its tracks.

How to fix Reporting Services when it won't start after patching

After server patching you might find that the Reporting Services service will fail to start; not just SQL 2005 but others too. This is described in a Microsoft knowledge base article. The problem is that applying those instructions is a manual and error prone process.

Troubleshooting WSUS errors from an SQL Server perspective

I set up a new lab environment for SQL Server recently and wanted to use some of the new Windows Server technologies I hadn't tried before… which isn't that unusual because I'm a DBA not a sysadmin.

Tips on building a Windows Server lab for clustering SQL Server in VMWare Fusion

Forcing access into SQL Server without a restart

As a DBA it can be difficult to gain access to a SQL Server instance you are authorised (and indeed required) to support.

Modifying tempdb database files without a restart

There is a myth that modifications to tempdb do not take effect until you restart the instance, understandably people have read the message that will be printed during many changes. But with careful planning the message and the restarts can be avoided and many changes can instead be made to take effect immediately.

Agent job errors when running an SSIS package

It's easy to trigger a lot of obscure errors with SQL Server Agent when scheduling an SSIS package to run within a job. The key problems occur when you override connection strings within the GUI.