I'm creating a generic alter that will catch ANY state change in my sql server mirroring setup. I currently have 5 databases that I"m mirroring.
The problem that I'm running into is my message looks like whats below. I'd like to make it more descriptive so I'd like to pull info from wmi about what server the alert came from, the specific databasebase and what is the event.
DATE/TIME: 10/23/2007 1:36:41 PM
DESCRIPTION:
COMMENT: (None)
JOB RUN: (None)
Here is how the alert is created.
*************************************
USE [msdb]
I want to send an alert (email) to the subscriber of a report when the report fails to execute properly. I€™ve been looking through the documentation for RS 2005 and can not find how to set this functionality up.
i have developed a pakage which populates a two different tables with reference to the xml files added to a folder which is watched by a security WMI task.it is governed by a sequence container which contains three for each loop container for working on the different files.i have different event handlers set up inside for each loop container tasks which contains , data flow task, execute sql task, and moving the processed file to the desired destination.i want to set up a send mail task on the package level using event handler on error, where i have set up a task for looging the error to the error table , i have tried to collect all the error messages in a array list variable . and trying to use that variable a s a message source. i could not under stand if i set the propogation variable in the sequence container as false than will the onpost execute event will fire the onpostexecute event handler in the package level.if show how can i send only one email for all the errors of package with error looging.
I have log shipping every 15 minutes working between 2 servers, and while the secondary server reports everything ok (time since last copy, restore are all under 15 minutes), the primary server reports that the time since last backup is 7400+ minutes. I have verified that the LS backup job has no failures. How can I correct this false error state and get the primary server to start reporting actual statuses?
Can you use the below query to get CPU high utilisation alert purposes for both named and default instance? or, do I need to make any changes here (@wmi_namespace=N'.ROOTCIMV2' ) ?
USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'CPU_WM_Utilization_Check', @message_id=0, @severity=0,
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Folks, i want an alert to be displayed by SQL when the processor shoots above 80% constantly for 30 seconds. Is there any perfomance counter alert sepcifically for this purpose?
Hi, I have to generate an automatic mail alert based on the sql server database table.In that table has expiry dates baed on that dates ,I have to send an automatic mail through sql server. please give solution for this issue. Thanks in advance. Regards, Raja.
I'm trying to develop a email alert feature on my project. I was trying to approach with SQL trigger wich I think is the best option. Basically, when a new record is inserted into ad table, a email alert goes to peolpe who selected to receive alert wehen certain conditions are met. What would be the best approach? any examples?Thanks
I have set up an alert to detect when Page Deadlocks rise above 0. Overnight I have DTS packages populating SQL Server and various other jobs (Cognos Cube Builds etc.). My alert detected a Deadlock during the night but all of my processes completed fine. My problem/misunderstanding is that my alert is still popping up every 5 mins saying there is a Deadlock yet there is nothing running or no-one accessing SQL server and I cannot see any trace of the Deadlock in the Current Activity. Is this normal or is it a bug?
1) how can I get an alert (via paging or email) when the SQL server is down ? 2) how can I get an alert ( via paging or email) when the SQL Agent goes down?
Currently, my client is allowing all the developers to log in as sa and most servers have a NULL sa password. Not a good idea. I am recommending them to move to integrated security with NT groups and to move away from using the sa login altogether. Then to secure the sa login, actually give it a password (something cryptic). I also wanted to set up an alert whenever someone logs in using the sa password. How can that be accomplished? A trigger on the sysprocesses table would work but seems a little risky.
I've created an alert that is raised when the Percent Log Used rises above 80%. The response to this alert starts a job that performs a log backup.
Sometimes the database server is pretty loaded and the log backup takes some minutes and the alert is raised 3 (or more) times before the log backup ends. In response to this alert, plus 3 backups are performed.
Is it possible to make the alert sleep while the log backup is running, to avoid those extra backups?
I thought about disabling the alert in the first step (and re enabling it as the last step) but if the job fails the alert wouldn't be enabled... Right?
I am trying to setup an alert when the following message occurs: Stored function 'isxpf_execsql' in the library 'c:issrvinisxpf.dll' generated an access violation. Error: 0, Severity: 20, State: 0 The Alert is defined as: severity - 020 databasename: all databasese error message contains this text: isxpf_execsql
This has occured sinde the alert was setup and the history of the alert says it has occured 0 times. Any help would be appreciated. TIA, Philip
if in response to an alert i have defined a job and this job is scheduled for 10.00 am daily, will this job execute daily or only in response to the alert? also i have set notifications to my computer in alert as well as in job. buti get notification defined in job and not in alert. why is it so?
I am setting up alerts on a new installation of SQL 2000 (on windows 2000 op-sys). I have set up the operator for email and paging. When I test the email and paging notification at the operator level, it works fine. I have set up an alert for a user defined message to page and email the operator if the error is raised. The error writes successfully to the SQL Server log however, the page and email do not work. I have similar alerts set up on several SQL 7.0 installations. What am I missing? Any suggestions are greatly appreciated. Thanks tcb
We have encountered a major SQLServer 2000 bug. I would like to find out whether anyone has seen this or whether there are work around. We are using SQLServer 2000 Standard Edition running on Windows 2000 Server.
Here is the problem. We have a main SP that calls several long SPs (1000+ lines in each SP) to migrate data from one SQL2000 server to another SQL2000 server. (Both database servers are within the same PC.) The bug occurs in the main SP. It skips a specific SQL statement (which is a call to a SP). There is nothing unique about this SQL statement because the same statement in other parts of the same SP works. This bug occurs consistently. We were able to trace it by putting PRINT statements around this SQL statement and inside the calling SP. We also tried to trace it using the Query Analyzer's Debug with a strange behavior. It seems like the Debugger has a line shifted in it's memory (lines are displayed fine.) This shift occurs a few statements before the Problematic Line. We observe this by trying to put a break point on a valid SQL statement. But the red dot actually shows on a comment line right after the intended line. It does not Step Into the Problematic Line (which is a call to a SP). Note that when we run small SPs, this behavior does not show up. Everything works fine for small SPs.
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?
I am trying to set up a 'SQL Server event alert' that will 'net send' a warning message to one of the Operators that have been set-up on our SQL Server.
The alert has been configured to act on severity level '009 - User Defined', for all databases.
I have created a user defined system message with a severity level '9' on the sysmessages table.
EXEC sp_addmessage 80000, 9, 'My User Defined Message'
...and I have raised this error on our SQL Server.
RAISERROR(80000, 9, 1) WITH LOG
The error message is being generated in the SQL Server Log file, but for some reason my Alert does not appear to fire off the 'net send' event.
The operator is 'available to receive notifications', and I have tested the 'net send' command successfully.
Does anyone know why this Alert might not be sending a message with the 'net send' command?
I want to monitor updates on a certain table using an alert. When the table gets updated (any update), the alert will trigger a job.
Btw, I'm not allowed to add a trigger on the table, hence my idea to use an alert. Somehow I think I should use a WMI event alert, but I'm not sure which server event I should choose. In fact I'm not even sure I shóuld use a WMI event alert.
I'm a newby and I have a quick question. I have set up this procedureto run as a job. I can create a block and have it populate the table,but I need to be alerted via email when the table is being populated. Ido not know what step/s am I missing. Here is the scripts I ran:CREATE TABLE BlockMonitorInfo(spid smallint,blocked smallint,waittype binary,InfoTime datetime)go/*********creates a control table that lets you turn the backgroundprocess on and off**********/CREATE TABLE BlockMonitorControl(BlockMonitorOn tinyint)go/**********populates the control table and initially turns on thebackground process******/INSERT INTO BlockMonitorControlVALUES (1)go/*StartBlockMonitor accepts one datetime paramter that controls thesampling delay for capturing blocking information*/drop PROC StartBlockMonitorgoCREATE PROC StartBlockMonitor@DelayTime char(9)ASDECLARE @CurrentInfoTime datetime-- Set the control flag in BlockMonitorControl = Onupdate BlockMonitorControl set BlockMonitorOn = 1-- Capture blocking lock info until the control flag is set = false-- We set it off by running StopBlockMonitor which we'll create soon.WHILE (SELECT BlockMonitorOn from BlockMonitorControl) = 1BEGINSELECT @CurrentInfoTime = getdate()INSERT INTO BlockMonitorInfo (spid, blocked, waittype, InfoTime )SELECTspid, blocked, waittype, @CurrentInfoTimeFROMmaster..sysprocesses (nolock)WHEREblocked <> 0WAITFOR delay @DelayTimeENDgo/*StopBlockMonitor turns off the capture of blocking info by setting thecontrol flag in BlockMonitorControl = off*/DROP PROC StopBlockMonitorgoCREATE PROC StopBlockMonitorASUPDATE BlockMonitorControl SET BlockMonitorOn = 0goSELECTBlockMonitorInfo.*FROMBlockMonitorInfo,(SELECT distinct InfoTime FROM BlockMonitorInfo)BlockChainTimeWHEREBlockMonitorInfo.InfoTime =BlockChainTime.InfoTimeand Blocked not in(SELECT spidFROM BlockMonitorInfoWHERE InfoTime =BlockChainTime.InfoTime)This last query is what I have set up in a job, but I need the job toalert us when the table gets new information added.Thank you for the assistance!
HI, I have to send an automatic e-mail based on database table .Could any body help me how to write stored procedure and where to execute it.How to send an automatic e-mail through sql server? Thanks in advance.
I'm getting an intermittent alert on SQL 7.0. This alert is firing on a severity 24 hardware error. This has now happened twice and both times I have looked at both the SQL error logs and the SQL Server event viewer. There are no references to the severity 24 errors in either the SQL error logs or the event viewer. What could cause this alert and why isn't it being logged properly?
I am currently having problems with a SQL server running out of connections. Setting the number of connections to unlimited is not an option. The connections that are established are from other applications and asp pages that should disconnect but dont. I have setup an alert to send me a message everytime the maximum number of connections is met. I have also setup a job to run sp_who. The alert runs the job with the sp_who command when it is fired. How can I save the results of sp_who to a file?
