Deadlock Problems

Hi all
Now a day I am facing too-many deadlock problems, is there any way or
possibility that I make automated job, for diagnose the deadlock victim and
kill that process.
Any advice will be greatly appreciated.
Farhan Iqbal
Why would you want to kill the victim? The application should be able to
recover from a deadlock situation and either retry or gracefully handle it
some how. That really isn't a database issue. You would be best served by
spending that time fixing the reason why deadlocks occur in the first place.
Andrew J. Kelly SQL MVP
"Farhan Iqbal" <mr_farhaniqbal@.hotmail.com> wrote in message
news:eR0mzUrTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim
and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
|||Shouldn't SQL automatically be killing the victim, rolling back it's transaction? There really isn't a need to write a job to do this for you.
Have the application constantly checking for the 1205 message.
"Farhan Iqbal" wrote:

> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
>

Deadlock Issue when dropping/creating tables

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

Quote:

Originally Posted by DWiggin

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...

Deadlock Issue

Hi ,
I always face the Deadlock issue in our production DB.We are not running any
Profiler nor any the Error Flag is set ON.The job fails and we trace the LOG
file to check the error. We are not supposed to run any of these.Is there an
y
way to check the DEADLOCK Issue after it has occured,such as to Trace back
the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LOGs
or any other option.
KINDLY HELP ME ON THIS ASAP!
Thanks in advance.
Regards,
ShyamIf you haven't set anything up to capture the deadlock info, I'm afraid ther
e
is not much you can do to analyze the deadlocks that already took place. One
of the most effective ways to capture and analyze deadlocks is set up trace
falg 1204 at startup (i.e. add -T1204 as a startup parameter from Enterprise
Manager).

> We are not supposed to run any of these.
Well, I'm not sure who set the rule. But if you are expected to solve
problems, you've got to have access to proper tools.
Linchi
"Shyam" wrote:

> Hi ,
> I always face the Deadlock issue in our production DB.We are not running a
ny
> Profiler nor any the Error Flag is set ON.The job fails and we trace the L
OG
> file to check the error. We are not supposed to run any of these.Is there
any
> way to check the DEADLOCK Issue after it has occured,such as to Trace back
> the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LO
Gs
> or any other option.
> KINDLY HELP ME ON THIS ASAP!
> Thanks in advance.
> Regards,
> Shyam

Deadlock Issue

Hi ,
I always face the Deadlock issue in our production DB.We are not running any
Profiler nor any the Error Flag is set ON.The job fails and we trace the LOG
file to check the error. We are not supposed to run any of these.Is there any
way to check the DEADLOCK Issue after it has occured,such as to Trace back
the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LOGs
or any other option.
KINDLY HELP ME ON THIS ASAP!
Thanks in advance.
Regards,
ShyamIf you haven't set anything up to capture the deadlock info, I'm afraid there
is not much you can do to analyze the deadlocks that already took place. One
of the most effective ways to capture and analyze deadlocks is set up trace
falg 1204 at startup (i.e. add -T1204 as a startup parameter from Enterprise
Manager).
> We are not supposed to run any of these.
Well, I'm not sure who set the rule. But if you are expected to solve
problems, you've got to have access to proper tools.
Linchi
"Shyam" wrote:
> Hi ,
> I always face the Deadlock issue in our production DB.We are not running any
> Profiler nor any the Error Flag is set ON.The job fails and we trace the LOG
> file to check the error. We are not supposed to run any of these.Is there any
> way to check the DEADLOCK Issue after it has occured,such as to Trace back
> the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LOGs
> or any other option.
> KINDLY HELP ME ON THIS ASAP!
> Thanks in advance.
> Regards,
> Shyam

deadlock in agent job

I have a job with a single t-sql step. The tsql executes a stored proc that
occasionally deadlocks.
I know i can't trap the deadlock in the stored proc, but I'd like to trap
the deadlock in the agent job, and retry the stored proc.
What is the best way to handle this?
-Rand yThis is normal behavior. A trigger is executed for each statement that cause
s
the trigger to fire and not for each row affected by the statement. See
"Multirow Considerations" in BOL.
AMB
"Randy" wrote:

> I have a job with a single t-sql step. The tsql executes a stored proc th
at
> occasionally deadlocks.
> I know i can't trap the deadlock in the stored proc, but I'd like to trap
> the deadlock in the agent job, and retry the stored proc.
> What is the best way to handle this?
> -Rand y
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> This is normal behavior. A trigger is executed for each statement that cau
ses
> the trigger to fire and not for each row affected by the statement. See
> "Multirow Considerations" in BOL.
>
> AMB
> "Randy" wrote:
>|||What about increasing "Retry attempts" in the advanced tab when creating or
modifing the job step.
AMB
"Randy" wrote:

> I have a job with a single t-sql step. The tsql executes a stored proc th
at
> occasionally deadlocks.
> I know i can't trap the deadlock in the stored proc, but I'd like to trap
> the deadlock in the agent job, and retry the stored proc.
> What is the best way to handle this?
> -Rand y
>
>

Deadlock error(-2147467259) + SQL Server

Hello,
I have a window-less VB application running as a SQL server job.
The application is very simple and it just picks up the blob file from
filesystem and inserts into the database. I am using SQL Server 2000
running on Windows server 2003 standard edition.
Most of the time my application works fine, but sometimes the
following error is thrown and then and the application terminates.
"Transaction (Process ID 56) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction. Error Number : -2147467259."
I am unable to trace the problem. I have all the latest servicepacks
and updates installed on my system. Please help me on this.
Regards,
Anil K Gupta.Hi
There are a few chapters in the BOL about DEADLOCKs ,have you read them?
"Anil K. Gupta" <anylcumar@.gmail.com> wrote in message
news:2770ae9d.0504262034.14d69c91@.posting.google.com...
> Hello,
> I have a window-less VB application running as a SQL server job.
> The application is very simple and it just picks up the blob file from
> filesystem and inserts into the database. I am using SQL Server 2000
> running on Windows server 2003 standard edition.
> Most of the time my application works fine, but sometimes the
> following error is thrown and then and the application terminates.
> "Transaction (Process ID 56) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction. Error Number : -2147467259."
> I am unable to trace the problem. I have all the latest servicepacks
> and updates installed on my system. Please help me on this.
> Regards,
> Anil K Gupta.|||As above +
Sounds like your VB code needs error trapping IE On Error GoTo ...
OR
Inline error checking where you check Err.Number after every statement of
significance. A good method is On error goto ... for general code blocks
that could produce an error and to use the inline checking for lines where
you expect you may get an error and handle it there and then specifically.
EG.
sub MyUpdater()
dim ....
On error goto MU_Error
... general code.
... avoid zero divides as always
... prevent preventable errors as always
... now for somethin error prone
On error resume next
err= 0
rs.Update
if err <> 0 then
.. error handler specific to the above.
end if
On error goto MU_Error
...
...
Exit sub
MU_Error:
msgbox "Error:" & err.description
... possibly log the error to a disc file so you can add specific handlers
end sub
"Anil K. Gupta" <anylcumar@.gmail.com> wrote in message
news:2770ae9d.0504262034.14d69c91@.posting.google.com...
> Hello,
> I have a window-less VB application running as a SQL server job.
> The application is very simple and it just picks up the blob file from
> filesystem and inserts into the database. I am using SQL Server 2000
> running on Windows server 2003 standard edition.
> Most of the time my application works fine, but sometimes the
> following error is thrown and then and the application terminates.
> "Transaction (Process ID 56) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction. Error Number : -2147467259."
> I am unable to trace the problem. I hav
e all the latest servicepacks
> and updates installed on my system. Please help me on this.
> Regards,
> Anil K Gupta.

Deadlock

Hi there!
In my database client application (C#) I sometimes get an exception telling
me, that my application (which runs a some kind ob batch job) was selected
as deadlock victim and the active transaction was terminated.
No I want to get mor information about, which other application causes the
deadlock.
I thought about executing "sp_lock" just after I received the
deadlock-exception, but I think that this is too late, because my
transaction will be already terminated at this time, and ther won't be a
deadlock any longer.
Can anyone tell me, what would be the best way to receive information about
the active database-connections and their locks, BEFORE the deadlock
situation is cleared by the database-engine?
Is there some kind of trigger or sth. like that?
As the problem doesn't occur regularly, and it's only on the productive
machine, I absolutely don't want to have the profiler active!
Thanks in advance!
Max
Markus
Take a look at sp_who2 stored procedure on clolumn blkby (If I remember
well) . That tells you who is blocked and by whom.
Regarding to DEADLOCKS please visit at this site
http://www.sql-server-performance.com/deadlocks.asp
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:%23GGruXUfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi there!
> In my database client application (C#) I sometimes get an exception
telling
> me, that my application (which runs a some kind ob batch job) was selected
> as deadlock victim and the active transaction was terminated.
> No I want to get mor information about, which other application causes the
> deadlock.
> I thought about executing "sp_lock" just after I received the
> deadlock-exception, but I think that this is too late, because my
> transaction will be already terminated at this time, and ther won't be a
> deadlock any longer.
> Can anyone tell me, what would be the best way to receive information
about
> the active database-connections and their locks, BEFORE the deadlock
> situation is cleared by the database-engine?
> Is there some kind of trigger or sth. like that?
> As the problem doesn't occur regularly, and it's only on the productive
> machine, I absolutely don't want to have the profiler active!
> Thanks in advance!
> Max
>
|||Use the trace flag 1024 to receive notifications about deadlocks.
DBCC TRACEON (1024, -1)
[]s
Luciano Caixeta Moreira
Meu blog: http://br.thespoke.net/MyBlog/Luti/MyBlog.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMKxokUfFHA.1044@.tk2msftngp13.phx.gbl...
> Markus
> Take a look at sp_who2 stored procedure on clolumn blkby (If I remember
> well) . That tells you who is blocked and by whom.
> Regarding to DEADLOCKS please visit at this site
> http://www.sql-server-performance.com/deadlocks.asp
>
>
> "Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
> news:%23GGruXUfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> telling
> about
>

DBMail used for job and alert emails

Can you use DBMail for job and alert emails without having to resort to usin
g
the DBMail stored procs? I have DBMail setup and the test email works fine
but when I run a job and expect to see a completion email send to a defined
operator no email occurs but neither does any error stating why the email wa
s
not sent. I have told SQLAgent to use DBMail and a specified account so I a
m
unsure what else I need to do.Any errors on the Database Mail Log? (Right-click Database Mail and select
View Database Mail Log).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Boggess" wrote:

> Can you use DBMail for job and alert emails without having to resort to us
ing
> the DBMail stored procs? I have DBMail setup and the test email works fin
e
> but when I run a job and expect to see a completion email send to a define
d
> operator no email occurs but neither does any error stating why the email
was
> not sent. I have told SQLAgent to use DBMail and a specified account so I
am
> unsure what else I need to do.|||Database Mail alerts did not work for me until after applying SQL 2005 SP1.
"Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
> Can you use DBMail for job and alert emails without having to resort to
> using
> the DBMail stored procs? I have DBMail setup and the test email works
> fine
> but when I run a job and expect to see a completion email send to a
> defined
> operator no email occurs but neither does any error stating why the email
> was
> not sent. I have told SQLAgent to use DBMail and a specified account so I
> am
> unsure what else I need to do.|||I found the issue. When you alter a DBMail configuration you need to restar
t
SQL Server Agent. The SQL Server Agent retains the last DBMail setting that
was available when the Agent started. Since I had no DBMail the last time
SQL Server Agent had been started it did not care what I was doing with
DBMail until I started the Agent again and then it refreshed its information
and worked fine.
This means any changes to an existing configuration of DBMail will also
require a restart of SQL Server Agent otherwise the Agent will be using the
configuration that existed when it started last. I was hoping for more of a
dynamic communication between DBMail and SQL Server Agent but it is working
now for jobs and alerts so hooray.
"Michael D'Angelo" wrote:

> Database Mail alerts did not work for me until after applying SQL 2005 SP1
.
> "Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
> news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
>
>

DBMail used for job and alert emails

Can you use DBMail for job and alert emails without having to resort to using
the DBMail stored procs? I have DBMail setup and the test email works fine
but when I run a job and expect to see a completion email send to a defined
operator no email occurs but neither does any error stating why the email was
not sent. I have told SQLAgent to use DBMail and a specified account so I am
unsure what else I need to do.Any errors on the Database Mail Log? (Right-click Database Mail and select
View Database Mail Log).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Boggess" wrote:
> Can you use DBMail for job and alert emails without having to resort to using
> the DBMail stored procs? I have DBMail setup and the test email works fine
> but when I run a job and expect to see a completion email send to a defined
> operator no email occurs but neither does any error stating why the email was
> not sent. I have told SQLAgent to use DBMail and a specified account so I am
> unsure what else I need to do.|||Database Mail alerts did not work for me until after applying SQL 2005 SP1.
"Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
> Can you use DBMail for job and alert emails without having to resort to
> using
> the DBMail stored procs? I have DBMail setup and the test email works
> fine
> but when I run a job and expect to see a completion email send to a
> defined
> operator no email occurs but neither does any error stating why the email
> was
> not sent. I have told SQLAgent to use DBMail and a specified account so I
> am
> unsure what else I need to do.|||I found the issue. When you alter a DBMail configuration you need to restart
SQL Server Agent. The SQL Server Agent retains the last DBMail setting that
was available when the Agent started. Since I had no DBMail the last time
SQL Server Agent had been started it did not care what I was doing with
DBMail until I started the Agent again and then it refreshed its information
and worked fine.
This means any changes to an existing configuration of DBMail will also
require a restart of SQL Server Agent otherwise the Agent will be using the
configuration that existed when it started last. I was hoping for more of a
dynamic communication between DBMail and SQL Server Agent but it is working
now for jobs and alerts so hooray.
"Michael D'Angelo" wrote:
> Database Mail alerts did not work for me until after applying SQL 2005 SP1.
> "Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
> news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
> > Can you use DBMail for job and alert emails without having to resort to
> > using
> > the DBMail stored procs? I have DBMail setup and the test email works
> > fine
> > but when I run a job and expect to see a completion email send to a
> > defined
> > operator no email occurs but neither does any error stating why the email
> > was
> > not sent. I have told SQLAgent to use DBMail and a specified account so I
> > am
> > unsure what else I need to do.
>
>