How creating an Alert turned into a Fight with a Demon

I was tired of developers and business analyst running monstrous SQL, eating up a

20 GB TEMPDB database, and still wanting more. Rarely am I asked for

assistance with the SQL before it causes problems. Having such a large

TEMPDB would allow poorly written SQL to run but consume more resources

than it really needs. Running more than one of these queries at the same

time would cause problems. It is time to be proactive and create an

alert.

First, I shrink TEMPDB to 1 GB in size. Next, I need to create an alert

to e-mail me when it starts growing again. This will allow me to catch

the troublesome SQL while it's running and perhaps find the means to make

it more efficient. Being this is my first attempt at creating an alert in

SQL Server 2005, I try Books Online, but it's no help. Next, I switch to

using the Internet to find some SQL Server 2005 examples.

I find a site with SQL for creating SQL Server 2000 demo

alerts in 2005. A quick cut and paste, removing some unknown commands,

and then execute. Wallah, I get a bunch of alerts with names containing

some unrecognizable characters. Ah! I can't update or delete them because

of those characters. The source SQL split the names across lines so those

characters were probably a carriage return or line feed. My mistake, I

should have examined the SQL more closely.

This being a development server with no one currently using it, I restore

the MSDB database to last nights backup. To accomplish this, I stop the

SQL Agent and kill the remaining SA connection. The restore is

successful. Next, I fix the names in the SQL and create the demo alerts.

These alerts do not provide what I am looking for. I decide to

create alerts using WMI. Searching the Internet, I find some sample

alerts on site1 and site2. My

attempts to create them fail with the message:

Well that's as clear as mud.

I Google the message and get two hits. One has a Microsoft fix for

computer names longer than 15 characters. No good, my server name is only

8 characters. Another link blamed the problem on moving the MSDB database

files, which I have not done.

Searching with part of the message gives me a hit on another site that recommends running wbemtest.exe to confirm

WMI is working. I run it and it works with the command "SELECT * FROM

CREATE_DATABASE". So WMI is working but not from SQL Server.

It's time for Plan B. I verify that I can create alerts with WMI on other

SQL Server 2005 instances. Next on a fresh install of SQL Server 2005, I

successfully create an alert with WMI, drop the alert, restore MSDB, and

try creating the alert.

It fails with the the dreaded "@wmi_query" error message. Shaking my

head, I decide to confer with a coworker who is a Unix Oracle DBA. He

smiles while I recount what happens when I restore the system database. I

knew he would not be much technical help but talking out technical

problems can be useful. Being it is Friday afternoon; I decide to wait

until Monday before giving Microsoft Tech support a call. Calling

Microsoft for support can be let's just say tiresome.

So back to my original goal, I need to create an alert to e-mail me when

TEMPDB starts growing. This is something I know I can do in SQL Server

2000 so it does not matter that WMI for SQL Server 2005 is not working.

Since Books Online doesn't have the answer, it's time to try another

resource. I try the book "SQL Server 2000 High Availability" and find

what I am looking for.

I create the alert and go to Database Mail to check on the configuration.

Clicking on manage accounts; I get a pop-up saying the service broker

needs to be enabled. What, I never enabled it before. I click "Yes" to

enable it and it hangs. Ahhhh. After killing the hung window, I reboot

the server and check every setting I could think of. No luck.

Back to the Internet, I find a hit on the SQL Server Central blog by Haidong Ji. It describes my problem and

explains

how to fix it. Thanks Haidong Ji.

• Stop SQL Agent
• Execute SQL "ALTER DATABASE MSDB SET ENABLE_BROKER"

After executing the SQL, I check Database Mail and it is working. Now I

create an alert using WMI. Success, it worked! Shouting, "I am Beowulf"

in my head for I had just defeated a demon.

Conclusion:

If you restore MSDB on a SQL Server 2005, remember to:

• Stop SQL Agent
• Execute "ALTER DATABASE MSDB SET ENABLE_BROKER"
• Howl in triumph for you have just defeated a demon.