Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server DBA Fights a Demon

By David Bird,

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:

"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.

SQL Agent Alert for TEMPDB Growth

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.
Total article views: 7583 | Views in the last 30 days: 11
 
Related Articles
SCRIPT

Create Mirror Alerts for All Mirrored Databases

Creates Mirror State-Change Alerts for ALL mirrored databases on a server - can save hours when sett...

FORUM

Alert Database creation

Database creation and backup alert

FORUM

ALert When Database State Changes

ALert When Database State Changes

FORUM

Alert when no witness

When creating an alert

FORUM

How to create Custom Alert.

custom alert.

Tags
administration    
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones