Decipher blocked process waitresource

When attempting to identify blocking issues, you may find yourself looking in sysprocesses at the waitresource column, and trying to decipher the meaning of something along the lines of:

KEY: 32:72057594097827840 (f5593d0e605b)

Thanks - you couldn't just tell me the actual object could you?!

But help is at hand - apparently this key consists of the database id, followed by a a hobt_id - no, not a small person with large feet from New Zealand, but a "Heap or B-Tree" id.

This id value can be found in the sys.partitions table, so a quick join from that to sys.indexes, and back to sys.obejcts finally reveals the index we are blocked on.
E.g.

SELECT o.name, i.name
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594097827840

Service Broker GUID does not match the one in sys.databases

Sometimes you can get a problem when trying to configure Service Broker on a database that has been restored / copied from another source (e.g. to a DR server), or even when trying to enable Database Mail on an msdb database that has been copied from elsewhere.

The error is something along the lines of:

Alter failed for database 'xxx'

Cannot enable the Service Broker in database "xxx" because the Service Broker GUID in the database (guid number) does not match the one in sys.databases (different guid number)
ALTER DATABASE statement failed.

This is because the master database also keeps track of service broker for each database, so this is just saying the master database thinks service broker has last processed a different record to the the one the database believes.

In the situation above, where we know this is the case because the database has come from another source, the quickest fix is just to reset the service broker as follow:

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE

This will drop and recreate all service broker settings and queues - so please only use this if you really do want to reset everything, don't do it if you have queues with statements in that you want processing - but for a quick fix on a DR database it can save a lot of headscratching.

Use a user data type (UDT) in temp table

Every now and then someone wants to use a user-defined data type (UDT) from a database in a temp table.

Initially trying it gives the error:
CREATE TABLE #Test (TestField GrumpyDBA)
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type GrumpyDBA

To enable the datatypes in temp tables, create them in TEMPDB ... and assuming it's not just for a one off script, also in MODEL (so they are rebuilt in tempdb on each startup).

CREATE TYPE GrumpyDBA FROM INT NOT NULL

So far, so obvious, but the issue that now crops up is the following error when a user tries to create the table again:

Msg 15247, Level 16, State 4, Line 2
User does not have permission to perform this action.

It's quite easy to give up at this point, but it's actually an easy update to fix this as well, just run this in TEMPDB (and Model)

GRANT REFERENCES ON TYPE::dbo.GrumpyDBA TO public

And you should now find you can use the UDT in temp tables from the original database.

Net Send becomes MSG

OK, so it's not news, but NET SEND no longer is the favoured way to send messages to machines, and MSG is the new method - unfortunately it's not as easy to use, and by default doesn't even work!

NET SEND is built into SQL which is a problem when that's used as an alternative to email for getting alerts.  Getting it to use MSG instead can raise other problems too.

Basic syntax to use MSG to send a message to a user / workstation :- the main issue here is you need to know what machine the user is on, but unlike netsend, you can at least configure it to auto close, so you don't come in at 8am and find 5000 popups to click close on.

MSG * /SERVER:workstation001 /TIME:60 Hello I'm testing

Next issue is you are likely to get this error:

Error opening Terminal server Workstation001
Error [1722]:The RPC server is unavailable.

It seems that the latest versions of Windows, by default, do not allow the relevant RPC call

To fix, you need to open regedit, navigate to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TerminalServer

Open the AllowRemoteRPC registry entry, and change the value to 1

You should now be able to use the MSG command.

You can use this to get SQL alerts as follows:
Create a file (e.g. msg.cmd file) with entries for each persons machine you want notified, such as:
MSG * /SERVER:workstation001 /TIME:60 %1
MSG * /SERVER:workstation002 /TIME:60 %1

Create a SQL agent job that calls that file via a CmdExec type step, along with [A-MSG], such as
\\network\DBA\msg.cmd "$(ESCAPE_NONE(A-MSG)) FROM $(ESCAPE_NONE(SRVR)) "

I've then configured alerts (e.g. backupfailed, replication issues etc.) on failure response to execute this job.

SQL 2008 - adding a column to a replicated table fails

There seems to be an issue in certain situations with adding columns to replicated tables in SQL 2008 where the publication is set to include schema updates.

The first error we saw was:

Msg 650, Level 16, State 1, Procedure sp_MSfixup_base_columns, Line 40
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

I'm also aware of a similar error mentioning a different system proc:

Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

It doesn't look like there is currently any supported workaround for it, but I managed to get it work by doing the following.

Step 1 - In the database where you are trying to change the table, script out the database trigger called "tr_MStran_altertable" as a create statement

Step 2 - Give the trigger a new name in this script, e.g. tr_MStran_altertable_ReadCom.  
Also, in the code you will see it has several SET statements, and to this section add:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Step 3 - run this script to create a new trigger.  

Note the reason for creating a new trigger is that you will get an error if you try to just ALTER the existing trigger to add this setting saying:

Msg 21598, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 60

Modifying DDL triggers created by replication is disallowed since these are required to track DDL changes.

Step 4 - disable the original trigger

DISABLE TRIGGER [tr_MStran_altertable] ON DATABASE

You should now find the original ALTER TABLE statement completes without an error, and the new field is also added to the replication.