strange Problem

[DaveL]

Hello, hope u can help or point me in the right direction

I have 2 Services on our production System
1 Service is Vb6 (not the problem)
2 nd service is on another maching c# (not the problem)

these service insert and update a common Table
that links 2 separate databases (and there tables)

database1 and associated tables
database2 and associated tables

main database table PK

ive set up local running the services 30,000 files both services on separate
machings hitting the same table
this takes place in a Stored Proc

but in Production it locks up the system with a blocking
so web site is usless and internal processing is Useless

In the c#side and proc that i uses ive removed all begin trans, and on the
updates and selects i use (nolock)
and updates use (rowlocks)

still the same problem in production

any ideas would be greatly helpful

thanks
Dave

[Erland Sommarskog]

Re: strange Problem

DaveL (dvs_bis@sbcglo bal.net) writes:

Hello, hope u can help or point me in the right direction
>
I have 2 Services on our production System
1 Service is Vb6 (not the problem)
2 nd service is on another maching c# (not the problem)
>
these service insert and update a common Table
that links 2 separate databases (and there tables)
>
database1 and associated tables
database2 and associated tables
>
main database table PK
>
ive set up local running the services 30,000 files both services on
separate machings hitting the same table
this takes place in a Stored Proc
>
but in Production it locks up the system with a blocking
so web site is usless and internal processing is Useless
>
In the c#side and proc that i uses ive removed all begin trans, and on the
updates and selects i use (nolock)
and updates use (rowlocks)
>
still the same problem in production

First put back those BEGIN TRANSACTION, and remove those NOLOCK, so
that you don't put the integrity of your database at stake.

Next, you need to analyse what is blocking what, and what the resources
the blocked processes are waiting for. Presumably, you need to review
your indexing.

On my web site you can find a stored procedure beta_lockinfo that
helps you to analyse to your blocking:
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx