Deadlocks on sysobjects

Hi,
We've been getting some deadlocks on our production box.
I've turned on the 3605 and 1204 flags and I've found an error in the
errorlog:
I think the key lines I'm concerned about are these:
Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
0x2
Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
0x2
This appears to be locks on the sysobjects table in my database?
Both processes appear to be running this commands:
Input Buf: RPC Event: sp_prepexec;1
Does anyone have an idea where I can go from here? I was assuming I'd be
getting a lock on a user table making it easier to debug.
The only major change recently, is a view that joins 3 tables together
partitioned on date.
Any ideas appreciated.
Nick
Hi Nick
You may want to post the full details for each node. Alternatively you may
want to see if sp_blocker_pss80 produces clearer output
http://support.microsoft.com/kb/271509/EN-US/
John
"Nick Dawson" wrote:

> Hi,
> We've been getting some deadlocks on our production box.
> I've turned on the 3605 and 1204 flags and I've found an error in the
> errorlog:
> I think the key lines I'm concerned about are these:
> Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
> 0x2
> Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
> 0x2
> This appears to be locks on the sysobjects table in my database?
> Both processes appear to be running this commands:
> Input Buf: RPC Event: sp_prepexec;1
> Does anyone have an idea where I can go from here? I was assuming I'd be
> getting a lock on a user table making it easier to debug.
> The only major change recently, is a view that joins 3 tables together
> partitioned on date.
> Any ideas appreciated.
> Nick
>
>

Deadlocks on sysobjects

Hi,
We've been getting some deadlocks on our production box.
I've turned on the 3605 and 1204 flags and I've found an error in the
errorlog:
I think the key lines I'm concerned about are these:
Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
0x2
Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
0x2
This appears to be locks on the sysobjects table in my database?
Both processes appear to be running this commands:
Input Buf: RPC Event: sp_prepexec;1
Does anyone have an idea where I can go from here? I was assuming I'd be
getting a lock on a user table making it easier to debug.
The only major change recently, is a view that joins 3 tables together
partitioned on date.
Any ideas appreciated.
NickHi Nick
You may want to post the full details for each node. Alternatively you may
want to see if sp_blocker_pss80 produces clearer output
http://support.microsoft.com/kb/271509/EN-US/
John
"Nick Dawson" wrote:
> Hi,
> We've been getting some deadlocks on our production box.
> I've turned on the 3605 and 1204 flags and I've found an error in the
> errorlog:
> I think the key lines I'm concerned about are these:
> Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
> 0x2
> Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
> 0x2
> This appears to be locks on the sysobjects table in my database?
> Both processes appear to be running this commands:
> Input Buf: RPC Event: sp_prepexec;1
> Does anyone have an idea where I can go from here? I was assuming I'd be
> getting a lock on a user table making it easier to debug.
> The only major change recently, is a view that joins 3 tables together
> partitioned on date.
> Any ideas appreciated.
> Nick
>
>sql

Deadlocks on sysobjects

Hi,
We've been getting some deadlocks on our production box.
I've turned on the 3605 and 1204 flags and I've found an error in the
errorlog:
I think the key lines I'm concerned about are these:
Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
0x2
Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags:
0x2
This appears to be locks on the sysobjects table in my database?
Both processes appear to be running this commands:
Input Buf: RPC Event: sp_prepexec;1
Does anyone have an idea where I can go from here? I was assuming I'd be
getting a lock on a user table making it easier to debug.
The only major change recently, is a view that joins 3 tables together
partitioned on date.
Any ideas appreciated.
NickHi Nick
You may want to post the full details for each node. Alternatively you may
want to see if sp_blocker_pss80 produces clearer output
http://support.microsoft.com/kb/271509/EN-US/
John
"Nick Dawson" wrote:

> Hi,
> We've been getting some deadlocks on our production box.
> I've turned on the 3605 and 1204 flags and I've found an error in the
> errorlog:
> I think the key lines I'm concerned about are these:
> Process 1 -> PAG: 7:1:784316 CleanCnt:2 Mode: IX Flags:
> 0x2
> Process 2 -> PAG: 7:1:812678 CleanCnt:2 Mode: SIU Flags
:
> 0x2
> This appears to be locks on the sysobjects table in my database?
> Both processes appear to be running this commands:
> Input Buf: RPC Event: sp_prepexec;1
> Does anyone have an idea where I can go from here? I was assuming I'd be
> getting a lock on a user table making it easier to debug.
> The only major change recently, is a view that joins 3 tables together
> partitioned on date.
> Any ideas appreciated.
> Nick
>
>

Deadlocks & BEGIN/END TRANSACTION

Greetings,

I've been reading with interest the threads here on deadlocking, as I'm
finding my formerly happy app in a production environment suddenly
deadlocking left and right. It started around the time I decided to
wrap a series of UPDATE commands with BEGIN/END.

The gist of it is I have a .NET app that can do some heavy reading (no
writing) from tblWOS. It can take a minute or so to read all the data
into the app, along with data from other tables.

I also have a web app out on the floor where people can enter
transactions which updates perhaps 5-20 records in tblWOS at a time.
The issue comes when someone is loading data with the app, and someone
else tries an update through the web app: deadlocks-ville on the
application and/or the web app.

Again, I believe it began around the time I wrapped those 5-20 record
updates to tblWOS on the web app with BEGIN/END. The funny thing is
that the records involved are not the same ones, so I'm thinking some
kind of table-level lock is going on.

I've played with UPDLOCK in examples, but don't quite understand what
it's attempting to do. Since the web update is discrete and short, and
it is NOT updating records that are getting loaded, I'd like the
BEGIN/UPDATE/END web transaction to happen and not deadlock the loading
application.

Any suggestions? I'd be most grateful.

thanks, LeafLeaf (rangerleaf@.hotmail.com) writes:
> I've been reading with interest the threads here on deadlocking, as I'm
> finding my formerly happy app in a production environment suddenly
> deadlocking left and right. It started around the time I decided to
> wrap a series of UPDATE commands with BEGIN/END.
> The gist of it is I have a .NET app that can do some heavy reading (no
> writing) from tblWOS. It can take a minute or so to read all the data
> into the app, along with data from other tables.
> I also have a web app out on the floor where people can enter
> transactions which updates perhaps 5-20 records in tblWOS at a time.
> The issue comes when someone is loading data with the app, and someone
> else tries an update through the web app: deadlocks-ville on the
> application and/or the web app.
> Again, I believe it began around the time I wrapped those 5-20 record
> updates to tblWOS on the web app with BEGIN/END. The funny thing is
> that the records involved are not the same ones, so I'm thinking some
> kind of table-level lock is going on.
> I've played with UPDLOCK in examples, but don't quite understand what
> it's attempting to do. Since the web update is discrete and short, and
> it is NOT updating records that are getting loaded, I'd like the
> BEGIN/UPDATE/END web transaction to happen and not deadlock the loading
> application.

Of course, if you want those 5-20 updates to be performed all or none
of them, but not only half of them, user-defined transactions is the way
to go. But since you then will hold locks for a longer period, you will
be more prone to deadlocking.

Deadlock situations can be fairly straight-forward to understand, but can
also be very complex. Therefore it's difficult to give precise advice from
any distance.

I can give some general advice though:

Indexing is important. Make sure that all involved queries uses Index Seek
or Clustered Index Seek, so the queries do not require table locks. You
can study the query plans by running the queries from Query Analyzer, but
you can also use Profiler to trace the application, and include the
Show Execution Plan event.

Important is also the order of access. Say that process A updates rows
1, 5, 9, 11, 17 in that order and process B updates rows 24, 27, 11, 1, 2
in that order. They will deadlock, because when A comes to row 11, B already
has updated that one, but not committed it. B then gets stuck on row 1,
because A has a lock on that row.

I don't if this happening in your application, but it's very important to
not have transactions in progress while waiting for user input. You could
be waiting all day in such case.

Finally, UPDLOCK, is a locking hint which is good when you read a row,
with the intention to update it in the same transaction. UPDLOCK itself
is a shared locks, and thus readers are not block. But the lock remains
to the end of the transaction, and no other process can have an UPDLOCK
on the same resource.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks so much for your quick and verbose response.

> Indexing is important. Make sure that all involved queries uses Index
Seek
> or Clustered Index Seek, so the queries do not require table locks.

I'm not using queries, but rather ADO.NET to pull data from tables by
filtering on IDs. Can I take that to mean I should make sure that any
IDs in my WHERE statements should be indexed? All records in my DB have
a primary key, clustered index. But in one-to-many tables, I filter
heavily on that related table to the primary key in another table. For
example, tblWOS.FactoryOrderID is the reference in a child table to PK
in tblFactoryOrders.FactoryOrderID:

SELECT * FROM tblWOS WHERE FactoryOrderID=10

Are you implying that I should make sure tblWOS.FactoryOrderID should
be indexed, too?

I'll be a bit more explicit. The web app passes a series of discrete
SQL commands via an ADO.NET connection object:

BEGIN TRANS
-- Update primary table (one record)
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
-- Update related child table (many records)
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS

tblFactoryOrders has one record in it (primary record), and tblWOS
could have 5-20 related to tblFactoryOrders.

These transactions can happen for any primary record at any time from
the web by 100 users, but generally each primary record gets hit just a
couple times a day.

Meanwhile, there's a .NET app which periodically (2-10 times daily)
loads a set of data from tblFactoryOrders and tblWOS, about 900 records
from the first and 5,000 records from the second. It loads this way:

SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...

and then a series of for each record in tblFactoryOrders

SELECT * FROM tblWOS WHERE FactoryOrderID=...

The issue is that while this load is happening, someone on the web
doing an UPDATE on records unrelated to the loaded values cause a
deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?

It's OK if someone on the web updates while this load is happening.

I'd like to resolve:

- if two folks on the web hit the same FactoryOrderID object, I'd like
them not to deadlock, but one to wait on the other.

- if someone is loading the application, I'd like it not to deadlock
when someone on the web does a transaction.

- the app can also write back to tblWOS (again, records not available
by the web app), and not deadlock the web app or the save process.

Would it be wise to stick a:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANS
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS

thank you, Leaf|||Leaf (rangerleaf@.hotmail.com) writes:
> I'm not using queries, but rather ADO.NET to pull data from tables by
> filtering on IDs. Can I take that to mean I should make sure that any
> IDs in my WHERE statements should be indexed? All records in my DB have
> a primary key, clustered index. But in one-to-many tables, I filter
> heavily on that related table to the primary key in another table. For
> example, tblWOS.FactoryOrderID is the reference in a child table to PK
> in tblFactoryOrders.FactoryOrderID:
> SELECT * FROM tblWOS WHERE FactoryOrderID=10
> Are you implying that I should make sure tblWOS.FactoryOrderID should
> be indexed, too?

Assuming tblWOS is of any size, you should definitely have an index
on that column. Now, I don't know much about this table, but I like
to point out from what you said here, it could very well be that this
is the column you should have your clustered index on.

> Meanwhile, there's a .NET app which periodically (2-10 times daily)
> loads a set of data from tblFactoryOrders and tblWOS, about 900 records
> from the first and 5,000 records from the second. It loads this way:
> SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...
> and then a series of for each record in tblFactoryOrders
> SELECT * FROM tblWOS WHERE FactoryOrderID=...

It would certainly be a good idea, to write a stored procedure that
produces rwo result sets: one that contains the rows from tblFactoryOrders,
one that contain all rows from tblFactoryOrders. In any case, sending
a query for each FactoryOrderId means a lot of network roundtrips.
Basically, if you get 100 ids pact, the load takes 100 times of what
it could take.

> The issue is that while this load is happening, someone on the web
> doing an UPDATE on records unrelated to the loaded values cause a
> deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?

Not really. The table scans are the real issue here.

> - if two folks on the web hit the same FactoryOrderID object, I'd like
> them not to deadlock, but one to wait on the other.

And then the guy that is number #2 overwrites the updates of #1? The
common strategy is to use optimistic locking. This can be implemented
in several ways, but the easiest is to add a timestamp column.
Timestamp columns are automatically updated by SQL Server each time
you update a row. (And they have nothing to do with date and time.)
So you add a timestamp condition to the UPDATE, and if they don't
match, the user is informed of an update conflict?

> - if someone is loading the application, I'd like it not to deadlock
> when someone on the web does a transaction.

I know too little about the scenario to tell whether just adding the
index will help. I'm also a little concerned of the consistency the
data that is being loaded. What happens if there is an update to
tblWOS when a load is in progress? What is the desired result?

By wrapping the load in a transaction, with the isolation level of
REPEATABLE READ or SERIALIZABLE, you could ensure consistency, as no
update of orders being loaded could be performed while the load is going
on.

This would even more require you to make sure tblFactoryOrders is
read only once, and not once for each ID.

> Would it be wise to stick a:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANS
> UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
> UPDATE tblWOS SET ... WHERE FactoryOrderID=10
> COMMIT TRANS

Actually, as hinted above, it's more the SELECT transaction that
can benefit for a higher isolation level. The UPDATE transaction
will not change that much, if at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your kind reply. VERY helpful.sql

deadlock: could not perform retention-based meta data cleanup

Hi SQL Replication Gurus:

I got some issues in my production environment, so please help me out. The following is the message I got from the replication monitor and I don't what to at this point.

Appreciate you help.

Yong

==========================================================================================

Command attempted:

{call sp_mergemetadataretentioncleanup(?, ?, ?)}

Error messages:

The merge process could not perform retention-based meta data cleanup in database 'TT'. (Source: Merge Replication Provider, Error number: -2147199467)
Get help: http://help/-2147199467

Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: ply-db-svr1, Error number: 1205)
Get help: http://help/1205

Just restart the agent. This is a transient problem due to the deadlock.|||

Michael ,

Thank you for your response. I have tried restarting the sql server agent, the sql Server Engine, and even restarting the windows server. That did not resolve the problem at all.

Thanks.

Yong

|||

1) How many subcribers connecting to publisher?

2) What is the amount of data loaded?

3) What is your configuration on SQL Merge profiler?

Deadlock within stored procedure - need help

We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the system, we get the following error
periodically:

Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)

Here is the script that creates our stored procedure:

USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetGeneratorValue]
@.genTableName varchar(50),
@.Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @.Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
UPDATE GENERATOR SET GENVALUE = @.Gen_Value+1 WHERE
GENTABLENAME=@.genTableName
COMMIT;
SET @.Gen_Value = @.Gen_Value+1
SELECT @.Gen_Value
END

This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.On Jun 12, 9:37 am, byahne <bya...@.yahoo.comwrote:

Quote:

Originally Posted by

We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the system, we get the following error
periodically:
>
Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)
>
Here is the script that creates our stored procedure:
>
USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
>
CREATE PROCEDURE [dbo].[GetGeneratorValue]
@.genTableName varchar(50),
@.Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @.Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
UPDATE GENERATOR SET GENVALUE = @.Gen_Value+1 WHERE
GENTABLENAME=@.genTableName
COMMIT;
SET @.Gen_Value = @.Gen_Value+1
SELECT @.Gen_Value
END
>
This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.

1. Down your isolation level to REPEATABLE READ.
2. UPDATE first, then SELECT.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
UPDATE GENERATOR SET GENVALUE = GENVALUE + 1 WHERE
GENTABLENAME=@.genTableName
SELECT GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
COMMIT;

3. Consider allocating your numbers in batches rather than one at a
time.|||Fantastic! That appears to have fixed the problem! Thank you for
your timely response.
-b|||Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.

Any other words of wisdom on why this might be happening and how to
avoid it?|||byahne (byahne@.yahoo.com) writes:

Quote:

Originally Posted by

Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.
>
Any other words of wisdom on why this might be happening and how to
avoid it?

Did you also rewrite the procedure as Alex suggested? Or did you just
change the isolation level? In the latter case, you should add
"WITH (UPDLOCK)" to the SELECT query.

Else what happens is that two processes both get the read-lock on
the wrong, and then no one can procede with the UPDATE. Since only
one process at a time can hold an Update lock, one them will be held
up at this point - rather than both being held up later.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to Erland's suggestion, see
http://blogs.msdn.com/sqlcat/archiv...nce-number.aspx
for tweaks to the technique Alex suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"byahne" <byahne@.yahoo.comwrote in message
news:1181745584.424801.302750@.o11g2000prd.googlegr oups.com...

Quote:

Originally Posted by

Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.
>
Any other words of wisdom on why this might be happening and how to
avoid it?
>

Deadlock trace

I'm currently having some deadlock issues in my production database, and I
need to trace them to investigate further. I have read on
www.sql-server-performance.com that I can run a create trace wizard and use
the "Identify The Cause of a Deadlock" trace. However I haven't been able to
find it...? How do I find the wizards in profiler...? I use sql server 2000
standard edition...
Henning FrettemLook under the Locks event class in Profiler. There isn't a wizard for doing
it...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Henning Frettem" <henninggf@.frisurf.no> wrote in message
news:ytBKb.17195$%J2.2675@.news-binary.blueyonder.co.uk...
> I'm currently having some deadlock issues in my production database, and I
> need to trace them to investigate further. I have read on
> www.sql-server-performance.com that I can run a create trace wizard and
use
> the "Identify The Cause of a Deadlock" trace. However I haven't been able
to
> find it...? How do I find the wizards in profiler...? I use sql server
2000
> standard edition...
> Henning Frettem
>|||The reference at www.sql-server-performance.com was for SQL Server 7, which
has a CREATE TRACE WIZARD. There are trace templates in SQL Server 2000,
but not a wizard, and none of the predefined templates are specifically for
tracking down deadlock.
In the Books Online page called "SQL Profiler Scenarios", it describes how
to use profiler to trace deadlocks.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Henning Frettem" <henninggf@.frisurf.no> wrote in message
news:ytBKb.17195$%J2.2675@.news-binary.blueyonder.co.uk...
> I'm currently having some deadlock issues in my production database, and I
> need to trace them to investigate further. I have read on
> www.sql-server-performance.com that I can run a create trace wizard and
use
> the "Identify The Cause of a Deadlock" trace. However I haven't been able
to
> find it...? How do I find the wizards in profiler...? I use sql server
2000
> standard edition...
> Henning Frettem
>|||> In the Books Online page called "SQL Profiler Scenarios", it describes how
> to use profiler to trace deadlocks.
Excellent, thanx alot... :)
Henning Frettem

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

DDL Best Practices question

I am looking for some examples of how to manage DDL scripts among
various versions of a production db and development and testing. I
have tried a few things in the past, and it always gets very muddled
and cumbersome.

I need to be able to build any version of the database from scratch,
BUT I also need to maintain an upgrade path from any version to any
later version. So it is not enough to just maintain a master build
script, but I don't want to maintain 2 different things (modify the
master build scripts AND create a new "ALTER" script for each version
change).

I thought I had seen an article somewhere that layed out a process for
managing this, but I can't find it now (I thought it was in SQL Server
Mag). Does anybody know of this article or have a resource they could
point me to that outlines best practices in this area?

Thanks,
Jason Wood, DBA in training."Woody" <jaydub99@.hotmail.com> wrote in message
news:a895dd46.0311241003.70d5a28d@.posting.google.c om...
> I am looking for some examples of how to manage DDL scripts among
> various versions of a production db and development and testing. I
> have tried a few things in the past, and it always gets very muddled
> and cumbersome.
> I need to be able to build any version of the database from scratch,
> BUT I also need to maintain an upgrade path from any version to any
> later version. So it is not enough to just maintain a master build
> script, but I don't want to maintain 2 different things (modify the
> master build scripts AND create a new "ALTER" script for each version
> change).
> I thought I had seen an article somewhere that layed out a process for
> managing this, but I can't find it now (I thought it was in SQL Server
> Mag). Does anybody know of this article or have a resource they could
> point me to that outlines best practices in this area?
> Thanks,
> Jason Wood, DBA in training.

One possible approach is to maintain only CREATE scripts, and use versioning
in your source control system to ensure that you can always build a given
version from scratch. To generate a upgrade script, you can then create
empty databases for the source and target versions, and use a comparison
tool such as the one from Red Gate to create a migration script. If you have
many versions, then you might do this only on demand; if you have fewer, you
might do it every time you produce a new version.

Whatever approach you take (and I'm sure there are many others which work
fine), a database comparison tool is always a good investment. The Red Gate
one is relatively cheap compared to multi-platform tools like Embarcadero,
and works very well:

http://www.red-gate.com/sql_tools.htm

Simon