Deadlocks in Profiler

I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were
generated by Loadrunner scripts (stress testing) simulating application
SQL via an ODBC DSN connection.

2 things are puzzling me in the SQL Profiler traces that I have logged

1) There are a large number of Lock:Timeout events but the 'lock
timeout' setting is the default 'wait forever' so I dont know what is
timing out.

2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using the
same ClientProcessId at the time of deadlock. What is the
ClientProcessId and is it relevant to the deadlock?

Thank you in advance for any replies.<Robert_Couldry@.linfox.com> wrote in message
news:1113887215.714232.311400@.f14g2000cwb.googlegr oups.com...
> I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were
> generated by Loadrunner scripts (stress testing) simulating application
> SQL via an ODBC DSN connection.
> 2 things are puzzling me in the SQL Profiler traces that I have logged
> 1) There are a large number of Lock:Timeout events but the 'lock
> timeout' setting is the default 'wait forever' so I dont know what is
> timing out.

Lock timeouts are set by the client, so perhaps one particular connection or
application has set a specific value - you can use sp_lock to investigate
exactly which process is holding a lock and on which resource, and Erland
has a useful tool for investigating locking:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

> 2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using the
> same ClientProcessId at the time of deadlock. What is the
> ClientProcessId and is it relevant to the deadlock?

ClientProcessID is the operating system process ID (ie. the PID in Task
Manager) of the client application connecting to MSSQL. One application may
have multiple SPIDs - Query Analyzer has one for the main window, and
another for the Object Browser. So it seems that your application is somehow
blocking itself.

This KB article may help you, if you haven't already seen it (there are a
number of other articles about deadlock caused by known issues also):

http://support.microsoft.com/defaul...kb;en-us;832524

Simon

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS

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

dbo-owner

when I run the same scripts twice with different users (all having db_owner
rights), the SQL server duplicates the objects (for example, the stored
procedures).
How can I avoid it . If I define "dbo." for each object , it will be ok ?
thanks
That should do it !
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:636B38A4-CFCB-44F5-9775-1717A2409A16@.microsoft.com...
> when I run the same scripts twice with different users (all having
> db_owner
> rights), the SQL server duplicates the objects (for example, the stored
> procedures).
> How can I avoid it . If I define "dbo." for each object , it will be ok ?
> thanks
>

dbo-owner

when I run the same scripts twice with different users (all having db_owner
rights), the SQL server duplicates the objects (for example, the stored
procedures).
How can I avoid it . If I define "dbo." for each object , it will be ok ?
thanksThat should do it !
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:636B38A4-CFCB-44F5-9775-1717A2409A16@.microsoft.com...
> when I run the same scripts twice with different users (all having
> db_owner
> rights), the SQL server duplicates the objects (for example, the stored
> procedures).
> How can I avoid it . If I define "dbo." for each object , it will be ok ?
> thanks
>

dbo-owner

when I run the same scripts twice with different users (all having db_owner
rights), the SQL server duplicates the objects (for example, the stored
procedures).
How can I avoid it . If I define "dbo." for each object , it will be ok ?
thanksThat should do it !
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:636B38A4-CFCB-44F5-9775-1717A2409A16@.microsoft.com...
> when I run the same scripts twice with different users (all having
> db_owner
> rights), the SQL server duplicates the objects (for example, the stored
> procedures).
> How can I avoid it . If I define "dbo." for each object , it will be ok ?
> thanks
>