Solved

deadlocks

Posted on 2006-10-23
78
1,746 Views
Last Modified: 2008-02-01
Why aren't deadlocks written to the SQL Error Log OR to the App Event Log?  This is being reported on the app end, but nowhere else:
VERY, very important, please advise.

Transaction (Process ID xxx) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.  Rerun the transaction.
0
Comment
Question by:dbaSQL
  • 44
  • 27
  • 4
  • +1
78 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 17788356
Check this

http://builder.com.com/5100-6388-6116287.html

You can use Profiler to catch deadlocks
0
 
LVL 20

Expert Comment

by:Sirees
ID: 17788364
If your databases are suffering from deadlocks, you can track then by using the SQL Server Locks Object: Number of Deadlocks/sec. But unless this number is relatively high, you want see much here because the measure is by second, and it takes quite a few deadlocks to be noticeable.

But still, it is worth checking out if you are having a deadlock problem. Better yet, use the Profiler's ability to track deadlocks. It will provide you with more detailed information. What you might consider doing is to use the Number of Deadlocks/sec counter on a regular basis to get the "big" picture, and if you discover deadlock problems with this counter, then use the Profiler to "drill" down on the problem for a more detailed analysis.

Source: http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17788366
Yes, I know that, Sirees.  I'm just asking why they are not actually written to the SQL Error Log/App Event Log.  I assumed this default SQL logging.  Am I incorrect?
0
 
LVL 20

Expert Comment

by:Sirees
ID: 17788428
<< not actually written to the SQL Error Log/App Event Log>>

Error 1205 (deadlocks) is not written to the event log by default. You can modify it
with
EXEC sp_altermessage 1205, 'WITH_LOG', 'true'
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17788506
sirees, the offending statement is ad hoc that i've been unable to proceduralize yet (developer resistance)
what if i were to preface the statement w/this:  SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED
Or, it uses an UPDLOCK.  Do you think a ROWLOCK may be a bit better?  
Lastly, what about setting DEADLOCK_PRIORITY ?  See, I'm getting a good deal of resistance on moving this stuff to procedures.  And, the app is getting intermittent deadlocks.  I MUST improve this asap.  
what do you think of any of this?
and, btw, thank you on the other.  i will alter accordingly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17788526
and sirees, you say ' written to the event log by default...'
does this also apply to SQL Error Log ?  
0
 
LVL 20

Expert Comment

by:Sirees
ID: 17788649
I meant Application event log

Any deadlocks happening in SQL server will be logged into the error log file, ERRORLOG

<<SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED >>

When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

<<Or, it uses an UPDLOCK.  Do you think a ROWLOCK may be a bit better?  >>

UPDLOCK will increase deadlock probability;You may want to use UPDLOCK if you want to reserve the row for a subsequent update.
ROWLOCK will improve concurrency but before doing this you need to analyze and clearly understand your locking scenarios.The concurrency will improve at the expense of memory consumed by the lock manager to maintain the row level locks.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17788703
that's the whole point of my question.  i thought the same >>>>Any deadlocks happening in SQL server will be logged into the error log file, ERRORLOG
but, my client side is getting this:  Transaction (Process ID xxx) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.  Rerun the transaction.

and it's not written to the sql error log
the deadlocks are intermittent, some days not at all, other days we see them between 6 5 and 7:45am  this corresponds w/the startup of a group of apps, BUT nothing is ever written to the error log.  

this is the statement that is deadlocking intermittently

                  Statement selectStmt = con.createStatement();
                                    con.setAutoCommit(false);
                                    String selectString = "SELECT * FROM TABLENAME  WITH (UPDLOCK) WHERE SequenceId = 'Basket'";
                                    ResultSet rs = selectStmt.executeQuery(selectString);
                                    rs.next();
                                    int basketSequenceNumber = rs.getInt("SequenceNumber");
                                    rs.close();
                                    int newBasketSequenceNumber = basketSequenceNumber + BASKET_NUMBER_INTERVAL;
                           String updateString = "UPDATE TABLENAME SET SequenceNumber = " + newBasketSequenceNumber + " WHERE SequenceId = 'Basket'";
                                    selectStmt.executeUpdate(updateString);
                                    con.commit();
>>>>


angelIII had suggested we do this:

Statement selectStmt = con.createStatement();
                                    con.setAutoCommit(false);
                                    String selectString = "SELECT * FROM TABLENAME  WITH (UPDLOCK) WHERE SequenceId = 'Basket'";
                                    ResultSet rs = selectStmt.executeQuery(selectString);
                                    rs.next();
                                    int basketSequenceNumber = rs.getInt("SequenceNumber") + BASKET_NUMBER_INTERVAL;
                                    rs.UpdateInt("SequenceNumber", basketSequenceNumber);
                                    rs.updateRow();
                                    con.commit();


but, i haven't gotten them to agree yet, AND the deadlocks persist.
so, long story short, is the use of UPDLOCK possibly causing this?  and, how would I properly change this to ROWLOCK?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17790712
try to create alert with Event Viewer log..:
---
for  event 1211(deadlock)  in sql server Agent Alerts ..
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17790726
yes, yes, i was thinking exactly the same.  i just gotta figure out how to get it to talk to my cell phone
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17790835
upon failure, my jobs send me email notices w/xp_sendmail.  like this:  EXEC master..xp_sendmail @recipients ='myCellPhoneAlias@vtext.com',....

i don't use sql server mail, i can't test the operator pager or email there, even if it is talking to mycellphonealias@vtext.com
how should i do that?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 200 total points
ID: 17791054
To force logging, you can use trace flags 1204, 1205 and 3605 (always set 3605; set at least one or both of 1204|1205).  It's best to put them as startup params so that SQL always has them in effect.

For the currently running SQL Server instance, you can activate them using ONE of the following:

DBCC TRACEON (1204, 3605) --<OR>
DBCC TRACEON (1205, 3605) --<OR>
DBCC TRACEON (1204, 1205, 3605)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17791102
yep, i set 1204 earlier.  in your opinion, scott, do you feel i should do both?  
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17791232
Did you set 3605?  Otherwise I don't think it will write to the SQL log.

1205 is up to you.  Personally I would, because I would want all the info I can get on the blocking :-) .
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17791656
also for deadlock notification you can use SQL Server performance condition alert:
more:

SQL Server Agent - Creating Performance Condition Alerts on Database Server Objects
By Alexzander Nepomnjashiy
http://www.databasejournal.com/features/mssql/article.php/1498781


0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17794763
agreed, scott, i've got all three in place
eugene, thanks for that link, i really like.  but, my netsend fails.  simply says 'Failed to Net Send to the address 'MYMACHINENAME'.

i'm not sure how to proceed.  your thoughts?
this is kinda my problem before.  i don't do sqlmail.  so how do i get around this?
all my jobs send failure notices to my cell phone, can't i also use the cell phone as an operator for alerts?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17797724
any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17798759
do either of you have any ideas as to how i can set up an opeator using the cell address?  
this is the address i'm using for all job failures, can i not use the same for an operator address?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17798769
Sorry, not sure about that, haven't done that.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17798855
thanks anyway, scott.  you know, i just now checked my error logs, since i flipped the flags yesterday, i've been logging a TON.  i expected a bit of a hit, but not really to this extent.  logs before averaged at 6KB, now i'm carrying more than 1044KB.  More importantly, though, i'm not sure how to interpret what i'm seeing.  i have a TON of what i've copied below.  but, i can't see an actual deadlock being found.  what do you think i'm reading here?

.......
...........
2006-10-23 10:10:47.04 spid94    DBCC TRACEON 1204, server process ID (SPID) 94.
2006-10-23 14:47:01.59 spid73    DBCC TRACEON 1205, server process ID (SPID) 73.
2006-10-23 14:47:01.61 spid73    DBCC TRACEON 3605, server process ID (SPID) 73.
2006-10-23 14:50:30.08 spid4     ----------------------------------
2006-10-23 14:50:30.08 spid4     Starting deadlock search 3499

2006-10-23 14:50:30.08 spid4     Target Resource Owner:
2006-10-23 14:50:30.08 spid4      ResType:ExchangeId Stype:'AND' SPID:112 ECID:0 Ec:(0x2E7FD508) Value:0x802b0288
2006-10-23 14:50:30.08 spid4      Node:1       ResType:ExchangeId Stype:'AND' SPID:112 ECID:0 Ec:(0x2E7FD508) Value:0x802b0288
2006-10-23 14:50:30.08 spid4    
2006-10-23 14:50:30.08 spid4     End deadlock search 3499 ... a deadlock was not found.
2006-10-23 14:50:30.08 spid4     ----------------------------------

2006-10-23 15:06:10.08 spid4     ----------------------------------
2006-10-23 15:06:10.08 spid4     Starting deadlock search 3500

2006-10-23 15:06:10.08 spid4     Target Resource Owner:
2006-10-23 15:06:10.08 spid4      ResType:ExchangeId Stype:'AND' SPID:107 ECID:0 Ec:(0x2C419508) Value:0x802b05c4
2006-10-23 15:06:10.08 spid4      Node:1       ResType:ExchangeId Stype:'AND' SPID:107 ECID:0 Ec:(0x2C419508) Value:0x802b05c4
2006-10-23 15:06:10.08 spid4    
2006-10-23 15:06:10.08 spid4     End deadlock search 3500 ... a deadlock was not found.
2006-10-23 15:06:10.08 spid4     ----------------------------------

2006-10-23 15:11:35.08 spid4     ----------------------------------
2006-10-23 15:11:35.08 spid4     Starting deadlock search 3501

2006-10-23 15:11:35.08 spid4     Target Resource Owner:
2006-10-23 15:11:35.08 spid4      ResType:ExchangeId Stype:'AND' SPID:106 ECID:0 Ec:(0x2C421508) Value:0x802b02e4
2006-10-23 15:11:35.08 spid4      Node:1       ResType:ExchangeId Stype:'AND' SPID:106 ECID:0 Ec:(0x2C421508) Value:0x802b02e4
2006-10-23 15:11:35.08 spid4    
2006-10-23 15:11:35.08 spid4     End deadlock search 3501 ... a deadlock was not found.
2006-10-23 15:11:35.08 spid4     ----------------------------------

2006-10-23 15:15:55.08 spid4     ----------------------------------
2006-10-23 15:15:55.08 spid4     Starting deadlock search 3502

2006-10-23 15:15:55.08 spid4     Target Resource Owner:
2006-10-23 15:15:55.08 spid4      ResType:ExchangeId Stype:'AND' SPID:112 ECID:0 Ec:(0x2E7FD508) Value:0x802b01d0
2006-10-23 15:15:55.08 spid4      Node:1       ResType:ExchangeId Stype:'AND' SPID:112 ECID:0 Ec:(0x2E7FD508) Value:0x802b01d0
2006-10-23 15:15:55.08 spid4    
2006-10-23 15:15:55.08 spid4     End deadlock search 3502 ... a deadlock was not found.
2006-10-23 15:15:55.08 spid4     ----------------------------------
.................................................
.......................................
................................
....................
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17799496
<can't i also use the cell phone as an operator for alerts?
Yes (BTW: does your Sql server agent is sending emails to operator(s)?)
---


USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'test',
            @enabled=1,
            @pager_days=0,
            @email_address=N'myCellPhoneAlias@vtext.com'
GO

---
<what do you think i'm reading here?>

read
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17802392
>>BTW: does your Sql server agent is sending emails to operator(s)?)
No, the agent fails to test email, pager and net send.  see, as i said, i'm not using sql mail.  any attempt on eht three fail w/the same error:

Error 22022:  SQLServerAgentError:  The SQLServerAgent mail session is not running; check the mail profile and/or the SQLSErverAgent service startup account in the SQLServerAGent Properties dialog.

I should have provided that error message days ago.  I do not know how to get around this.

thank you for the deadlock link, eugene.  i will read it.  unfortunately, i'm going to have to flip those flags soon...that log is getting pretty darn big.  AND no deadlocks are being found.  i just have tons and tons of what it takes to get these:  End deadlock search 6856... a deadlock was not found.

sure, sure, the moment i turn them off, surely there will be a deadlock.  
but, the operator.  can you please, please tell me how to get these into place?  if i do this sp_add_operator, am i not still going to receive this SQLServerAgentError about the mail session?

0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17802714
do you have MS outlook installed on the box?
if 'yes'
check
INF: How to Configure SQL Mail
http://support.microsoft.com/kb/263556/EN-US/

--
you just need
DBCC TRACEON (3604)

DBCC TRACEON (1204)
---if you do not use 1205 - you will get less messages..


--------------------------------

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

-----

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17802846
yes, i understand that, eugene.  but again, i do not use sql mail.  nor do i have outlook on the box.  i use smtp
and like i said, sql is sending all my error notifications (sql agent jobs) to my cell phone:  'rmyname@vtext.com'
to your knowledge, is there any way at all that i can give this address to my operator and have certain alerts - such as DEADLOCKS - sent to the same address?
do you know if this is possible?
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 300 total points
ID: 17803207

Create job- e.g. Alert Notification:

where you put your SMTP based email  to 'rmyname@vtext.com'
like
Sending Alerts Via a Custom SMTP procedure
http://www.sqlservercentral.com/columnists/ckempster/sendingalertsviaacustomsmtpprocedure.asp

or
based on xpsmtp
http://www.sqldev.net/xp/xpsmtp.htm

-------------------------------------------
in Alerts - goto your Deadlock Alert -> properties->

TAB 'Responce':

where check 'Execute Job' and select just created job 'Alert Notification'
...
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17803302
ok, very good, let me see what i can do w/this.  i'll let you know shortly.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17803333
well, xpsmtp is out....  (oddly enough, i'm already using this in some other areas, just not for these darned alerts)

Q: How do I use XPSMTP, for sending SQL Agent Alerts and job notitications?
A: Since SQL Agent does not use SQL Mail, but relies on a different MAPI based mail implementation (named SQL Agent Mail), which is hosted in a separate DLL (SEMMAP.DLL), IT IS NOT possible to replace the SQL Agent Alerts and job notifications directly with an SMTP based solution.

 i'm trying the smtp dll.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17803371
try to create job
and call the job from alert (if it triggered) as per my post above
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17803399
i don't understand that, eugene.  i want to be notified if/when deadlocks occur.  what job can/should i create around that?  i'm not doing anything, i'm just watching more closely.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17803939
Try
to create alert
event 1211(deadlock)  in sql server Agent Alerts
and in TAB 'Responce' of the new alert
 check 'Execute Job' and select sql server agent precreated job name that send SMTP email...
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17804706
oh, oh, ok.  let me try this
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17804814
ok, i wonder if i can force a deadlock (in the dev bed) to test this
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17806401
well eugene, i am trying to figure out how to test this.  but i'm gonna go ahead and award.  thank you very much, eugene, sirees and scott, for all of your assistance.  i will split the points, eugene and scott, as you both showed me quite a bit
thanks much
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17807513
please post the test result
EZ
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17810652
yes, definitely.  as of yet, though, i'm not so sure how to test it.  but, i'll post it when it happens
thx, eugene
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17812740
use Sql server agent alert 1205
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17812820
yeah, i know, but the whole deal is i want to test the alert.  how can i produce a deadlock which will fire the thing off?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17813375
test:
I - create in test Db 2 tables:
---table 1
create table t1 (i int)
insert into t1 values(9)
insert into t1 values(1)
--
---table 2
create table t1 (i int)
insert into t1 values(9)
insert into t1 values(1)
--
II - open to QA sessions (2 windows) pointed to your test db
---
III-

---copy in first window:

BEGIN TRAN
UPDATE t2 SET i = 1 WHERE i = 1
WAITFOR DELAY '00:00:10'
UPDATE t1 SET i = 9 WHERE i = 9
COMMIT

--copy in second window:
BEGIN TRAN
UPDATE t1 SET i = 9 WHERE i = 9
WAITFOR DELAY '00:00:10'
UPDATE t2 SET i = 1 WHERE i = 1

COMMIT

---
run them

--see error - > check alert
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17813401
oh, excellent, eugene.  why did i not think of this.  thank you
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 17

Author Comment

by:dbaSQL
ID: 17813514
ok, must be an error in there somewhere...no deadlock issued, no alert sent
lemme see if i can find it
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17813526
oh yeah, btw, no error to see or check
i created t1 and t2, ran those two updates from 2 different windows, one ran for 10sec, the other for 11, no errors issued
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17813798
check tab messages- >
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17813808
if you're referring to in QA, i did.  


(0 row(s) affected)
(2 row(s) affected)

on one
and the reverse on the other
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17814391
in 1 I got: after 15sec

/*-----------------------------
BEGIN TRAN
UPDATE t2 SET i = 1 WHERE i = 1
WAITFOR DELAY '00:00:10'
-----------------------------*/

(1 row(s) affected)

Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

---
second: after 20sec
/*-----------------------------
BEGIN TRAN
UPDATE t1 SET i = 9 WHERE i = 9
WAITFOR DELAY '00:00:10'
-----------------------------*/

(2 row(s) affected)


(1 row(s) affected)
----------------
-----------------------------

----make sure the data is like -------------------------
select * from t1
--result
9
9
select * from t2
--result
1
1

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17825321
i will do it again, eugene, and i'll let you know if it works
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17833286
it didn't work before because i pretty much just cut/pasted your tsql, and as you can see, it creates two t1 tables, and then inserts into two t1 tables.  i'd changed it to create t1 and t2, but i didn't correct the insertion.  this is why i didn't force the deadlock.  i've fixed all that, and now it works.  the alert was sent to my phone and i was notified via email as well.  perfect.  very, very good.
now, if we get one, i'll at least write it to the sql error log, and receive a notice that it occurred.
initially, i had enabled 1204 and 1205, but that 1205 is an awful lot of auditing.  for now, i'll just stick w/1204 and 3605
hopefully i can find and figure out what's causing these darn things.
thank you, eugene.  very big thanks
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17833649
Cool
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17842081
by the way, eugene, this is great.  these alerts are working beautifull, and, i believe i may have found the source of the deadlocks.  maybe.  all i gotta do is decipher this darn log.  

just wanted to thank you again
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17843402
you are welcome

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17849405
eugene, may i ask just a quick question?
dbcc traceon (3605,1205,1204)

having run the two updates above, msg 1205 is written to the message tab in query analyzer
the log reports this:   End deadlock search 55 ... a deadlock was found.
but error 1205 is not written, hence, my alert isn't working
any thoughts?

i can open another inquiry, if you'd prefer
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17849644
try:

dbcc traceon (1205, 3605, -1)
go
dbcc tracestatus(-1)
go
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17849670
this is happening on two servers now.  still works on the one you helped me with, i just need to deploy elsewhere, and it's not happening.  maybe i'm missing something obvious.  but, here's -1 details, i don't understand why it worked on the other (all set up the same), but not on these two

1    Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17849723
1204      1      
1205      1      
3605      1      
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17849768
ok, ok, clearly i am missing something.  3rd box now, not one of them is writing 1205 to the log, nor are they sending my alerts
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17849990
open alert - and make sure it has check - write to event log...
or just run:

EXEC sp_altermessage 1205, with_log, true

BTW: 3rd box? do you have anybox that does notify?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850004
yes, just the one i did w/you yesterday (maybe the day before)   that one works, i'm happy, so i'm trying to deploy to the same to my other resources.  i've now tried it on 4 of them, it works on none, so i was sure i missed something in there obvious, but what gets me most is why isn't 1205 written to the log(s)?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850058
did you run

EXEC sp_altermessage 1205, with_log, true
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850076
BTW:

DBCC TRACEON (3604)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850095
i thought 3604 sends it to the client & 3605 sends it to the error log, right?  
my alert(s) are based on error 1205 occurring in the error log
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850197
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850214
EXEC sp_altermessage 1205, with_log, true
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850233
yes, same thing here:  http://www.sqlservercentral.com/columnists/RDyess/traceflags.asp
does it not say that 3605 is needed in order to write to the log?

3604 Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.  
3605 Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)  

i swear, the one this is working on is running 3605
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850295
i've used sp_altermessage AND i tried 3604, it's still not happening at all.  error 1205 is written to the message tab in QA, but nothing is written to the log.  well, a lot is, but no error 1205.  and, of course, my alerts aren't firing cuz they are dependant upon a 1205 occurence in the sql error log

you say 'open alert - and make sure it has check - write to event log...'
i'm in there, where is that option?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850579
<i'm in there, where is that option?
sp_altermessage - is did it -
but it is in error options ' ...'
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850610
is sql server agent running?

you put the trace on or not - alert should run :
why it does not run on these boxes and runs on the one - it is question..Hmm
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850635
did you restart first (working box) after alert was set?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850640
yes, of course, the agent is running on all my servers
i really don't know what this is
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850689
no. none of the boxes have been restarted since day one on these alerts
and, i just now re-tested the good one, it works just fine
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17850939
when you open alert (in EM)  - can you see in General Tab - History - is it 0?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850966
yep.  the one that works is at 15...it's 'occurrence count', not history (just incase i'm not looking in the right place)
all these that ar failing say Occurrence count: 0
oh crap, i think maybe i found it
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17850984
no, i was wrong, found nothing.
darnit.  i just don't know what this is.
i am going to delete the alert AND the job and recreate it.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17851007
try to recreate Alert ....
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17851087
i did, it didn't work.  i really don't feel like it's a problem w/the alert, eugene.  my alerts are based on the occurrence of error 1205 in the sql error log.  and, it hasn't occurred in any of the logs on these failing boxes, at all
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17851543
eugene, possibly should i open another inquiry?  i don't want to pester you for free, and i really have got to get around this.
why would i not write 1205 to the log on any of these boxes?
that has to be what's causing the alerts to fail
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17851549
well, they don't even fail.  they're just not run at all, cuz the 1205 isn't there to invoke them
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17851661
<  i don't want to pester you for free
I am do it for free :)
But you can open another one to bring more EE to help
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17851705
< I am do it for free :)
quite true

ok, new inquiry, but please do let me know what you think, eugene, if you think of anything
thanks very much
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17851733
sure
try to compare the sql servers : sql server editions\ service packs!; os\OS sP, etc

just put link to new question
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now