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
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:
"The @wmi_query could not be executed in the @wmi_namespace provided.
Verify that an event class selected in the query exists in the namespace
and that the query has the correct syntax."
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
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.
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.