I am having a problem registering any WMI Event in the ootMicrosoftSqlServerServerEvents namespace on all of my SQL Servers. It doesn't matter what instance I append behind this either. MSSQLSERVER fails as will any other named instance. I get the same exact errors on every server which isn't surprising since they currently all have identical configurations, excluding hardware:
"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."and it doesn't apply because my servernames are all 8 characters long, and I already applied CU6 to the servers months ago. The current @@Version on all Servers is:
Microsoft SQL Server 2005 - 9.00.3233.00 (X64) Mar 6 2008 21:58:47 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
the only differece is that some of them are x86 instead of X64, but the problem occurs on the x86 servers as well.What is interesting is I can register other WMI namespaces just fine, like the following from Jason Massies Blog:
EXEC msdb.dbo.sp_add_alert
@name =N'Test',
@enabled =1,
@delay_between_responses =0,
@include_event_description_in =0,
@wmi_namespace =N'.ROOTCIMV2',
@wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 100000'
GO
I can create the WMI Alert on my laptop which is build 3054.
We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
I am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %
I've created multiple select statement to pull in varies fields from a table and one of those fields is Fiscal_Year, however, after the select statement I'm adding a Where clause:
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Is there a way that I can get SQL Server to tell me (Alert?) when it has grabbed another chunk of space for a database that has the "Automatically grow file" option set?
I was wondering if anyone knew of some key error numbers that I should look for to fire alerts off of.
For example, ERROR 926 "Database cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server error log for more information."
I am looking for some key error messages (Error Numbers) like the one above that are good to initially setup within SQL Server for alerting Administrators of potential issues.
Hi, I just realized there are several severity alerts on the Server Agent, from Severity 19 to 25. Is there anyways to delete those messages? If I don't delete those, would it be affected later? The Online Book mentions those are some of the system error messages, but they don't show how to delete them. Can anyone helps? Thanks a lot.
I am having trouble with locks and performance issues in one application. The SQL server is specific to this application. What settings should i choose to set an alert to let me know when i get too many locks? the locks i am interested in ar ethe ones that show up in SQL EM. ie where a process is blocking another process(es).
I have tried New Alert | lock requests /sec but that is a huge figure when the server is running OK, so i cant guess what value to set for the counter. Is there a better object/counter i should use?
I've setup alerts and an operator to email on a particular event code. I can successfully test the operator. (It e-mails fine) The problem I am having is that despite having a particular event log to the NT application log, no SQL Alert is triggered. I've tried logging to the NT Application log, as well as the SQL errorlog. The SQLExecutive is running. Thanks in advance. John
I am working with alerts and trying to see if there is a way that I can control the message on the notification. I have the job set to send an email and a net message when the job succeeds. Since this message will be going to the user, I owuld like to be able to make the message as clear as possible for them. Does anyone know if the message being sent can be modified or altered with an alert? And how?
Hey guys, I was just thinking, is it possible to send alerts via the Managment/SQL Server Agent/Operators/Alerts to an SMS system?
Is it even technically posibble?
Actually I am trying to develop an alert system that reads the database table periodcally, the data being read is a datetime, once it reaches the time, I need to send an sms to the person.
I defined an Alert in Enterprise Manager for Severity 19 - Fatal Error In Resource. The alert is enabled, the type is "sql server event alert" and the alert is defined against a specific database. SQLSERVERAGENT and SQL Mail session are both started. I test the alert using the following statement in the specified database.
raiserror (50001,19,1) with log
A message is placed in the SQL Server log, however no alert is fired. The alert history indicates the alert has never occurred. What am I missing?
Does anyone know of a way, either by 3rd party tools or using SQL server, to have alerts get emailed to you in a clustered active/active environment? I work as the DBA at a quickly-growing company, whose production databases need to be online 24X7. I want to set up the alerts to email/page me if there are problems with the databases, but since (according to microsoft) SQL mail is not supported in a clustered environment I'm at a loss.
I'm looking for some help in trying to resolve this issue either by 3rd party means or a SQL Server work around.
Can anyone point me in the direction of a good resource that will help us set up our system so that notifications from alerts/jobs send emails to our Groupwise email system?
I have several alerts set up and a job setup to notify via email if alerts occur. There are times when I might be doing maintenace on a box that may generate alerts but I don't want to get emailed or paged for these "dummy" alerts. I thought first of setting the enabled column of sysalerts to 0 - that doesn't work. I still get notified. Then I thought I'd set the enabled column in sysjobs for my notification job to 0 while I'm doing the work. But as soon as I re-enable the job I get an alert. It seems that as soon as the job is re-enabled it checks the NT application log and sees that an alert was fired and somehow knows that there was no notification sent so sends one. If I purge the NT application log before re-enabling the job is solves the problem. But that is a very in-ellegant solution and I don't want to loose all the info stored in the event log any way. Does anyone know how SQL SErver aggent keeps track of this stuff? (ie. that an alert was fired but it's response job is disabled and to check once a job is re-enabled to see if any alerts occured while it was disabled?) I did a Profiler trace while re-enabling the job but didn't see anything pertinant in there.
I want to write a sql alert to notify me when my database is out of space. So as not to duplicate any messages - has anyone seen threads on this subject? If not, can anyone give ideas on how to create this script? thanks in advance.
I'm searching for tips on how to alert users regarding downtime of our SQL Server. Is it good enough to alert users by email, and then shut down the service, or do you use a better approach (lock database, sp_who, one-by-one alert...)?
I want to creat an alert that will back up my database when the log files get to 90% full. When i try to create a new alert and select performance condition and select sqlserver:databases as the object then log file(s) Used size (kb) as the counter but when i go and select the instance i dont see my database in the drop down list but if I change to sql server event alert I see the database i want to use for this alert. I dont want to wait until the log is already full to back it up i want to do it when it hits a percent full. Is there a way to do this as a performance condition? thanks for your help!
Maybe a dumb question, the standard alerts like Severity 24 and 25 that come when you install SQL Server, will these alerts be called by SQL Server itself if there is a problem. I was going to set up notifications for these but wasn't sure if it was worth the trouble.
We are in the process of setting up a SQL Server 2000 cluster, but have been told that MAPI email is not cluster aware so we can't use the alert system.
Has anyone got any idea how to get round this or how to set up alerts in another way (eg: when a backup fails, can we send smtp mail?) etc
I once had a procedure to setup exchange client as email for SQL alerts, but I've misplaced it. (threw it out, actually) I've not had great luck finding it on the web at MS, or otherwise. Does anyone know where this can be found? Thanks in advance, John
We have occasional problems with transactional replication between 2 databases, and the problem is we dont notice right away. Usually the customer notices a few days after replication has stopped, and thats no good. We want to be notified as soon as there is any problem related to replication so we can go in and figure it out.
Ive been playing with the alerts, and it LOOKS like I should be able to do what I want, but after several hours I still cannot make any of the alerts work.
In my test environment, I'm forcing the following error, in order to try and test an alert: error 20598 "The row was not found at the Subscriber when applying the replicated command."
This seems to be one of our more common errors, and also its really easy to produce, so it seemed like a good candidate for testing the alert.
First off, I noticed that the agent profile was set to "Continue on data consistancy errors", which ignores error 20598. So I changed the profile to "Default", which doesnt skip any errors.
Next I set up a simple Job that simply writes a string to a file. I tested the job by calling it manually and it works. Once i can prove that this job is actually being called, I will change it to email me and the team.
Then I created a new alert in the publisher database which watches for error code 20598. Its response is to call the job I mentioned above. So when the error occurs, a string should be written to that file.
Then I forced the 20598 error to occur. I deleted a row in the subscriber DB, and then changed the corresponding row in the publisher. This causes the error to occur. In my replication monitor, I see new entries saying "Skipped 1 error(s) when applying transactions at the Subscriber." I'm not sure why it says skipped, but I know that these entries are the 20598 error that I'm trying to produce.
Now, there was nothing written to my text file, and I can see from the job history that my job was never called. If I look at the history of my test alert, it says "never occured".
I forced the 20598 error to occur, and I have an alert that is watching for the 20598 error, but it never fires.
I also created an alert in the subscriber database, watching for the same error. This alert was not called either.
