deadlock issue

How SQL server 2000 tracks the deadlocks - per PID or per connection? I started to have multiple deadlocks, made the trace and logged the deadlock events in the error file, so I know where I'm now but...

Here's the situation:

The app starts and logs as PID.Then I could see multiple process running with the same PID. One of this process select and updates records in the staging table. The second process takes the same records loaded in the bus and process them with specific logic. Then it starts from the beginning. When I have 4-5 of this PIDs with several processes inside each and they are all running against the same tables I get huge amount deadlocks. If I run the same application on Oracle I could see each of these processes with separate connection and I do not have deadlocks. My colleagues is running this on Oracle with no problems. My question is how the SQL server handles these connections differently?
One more complication - if instead of a staging table I use a flat file which the application reads, then there's no deadlocks on SQL server - why reading a tables is different and why it causes contention? I also tried to create the same staging table in separate database - in this case I have much less deadlocks - like only 10%.
Can somebody help me to try to explain and possible to troubleshoot this?
Thanks a lot, mj

I am not sure what you mean by PID. Are you talking about SPID or the process id? That reflects a connection. Deadlocks can happen even within the same connection / statement. It is best to think of deadlocks happening between multiple tasks or transactions. Based on your description it is really hard to understand the type of operations you are doing to result in deadlock, number of tables involved, schema, indexes etc. You could post the deadlock trace output so we can try to figure out why the deadlock happens or the lock resource for example. Please check out the link below for some troubleshooting tips for SQL Server 2000. There is similar link for SQL Server 2005 or you can look it up in the Books Online.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp

deadlock errors

I'm setting up a push merge replication, after the initial merge agent completes without errors I move the subscriber from a wired connection to a wireless connection. At this point I get an error…

The schema script …Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\[table]_93.sch' could not be propagated to the subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

Cannot drop the table '[table]' because it is being used for replication.

(Source: dfdmfvltgh09 (Data source); Error number: 3724)

After this error the initial merge agent kicks off again trying to apply the initial snapshot where I then get the following deadlock errors.

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

(Source: dfdmfvltgh09 (Data source); Error number: 1205)

The process could not deliver the snapshot to the Subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

All are getting set up on sql 2000 sp4 on a push.We are trying to set up 10 units that all are pointing to the same dlink router model dwl-2100AP.5 are working the other 5 are coming up with this error.Anybody have any ideas what could be causing this.I don’t mind the getting cut off due to poor network connectivity, I really want to stop the initial merge agent from kicking off after it’s already completed.

Thank you in advanced,

Pauly C

Let's tackle the snapshot failure first. Are you saying merge is trying to apply the snapshot twice, once on the wired network and once on the wireless network? Are these two networks on the same domain?

|||

Yes, the snapshot completes without errors, and replication works, changes get propergated back and forth between the subscriber and publisher, then when they move the subscriber to a wireless connection the first error occurs and the initial merge agent starts again.After applying some of the snapshot it gets the deadlock error. and it keeps trying to apply the initial merge agaent over and over.

|||And yes they are on the same domain. Somebody told me they were able to ping a machine that wasn't connected and actually got results. I did not see this with my own eyes so I'm not sure how true it is.

DBPROCESS is dead

I have a user that is losing his database connection
after about 6 minutes or so with error:
Database error code: 10025
Database error message: Select error: Possible network
error: Write to SQL server failed. General network error.
The same user access another application on the same
server, different database, with no problems and the same
application used by another user who is having no
problems.
Any ideas what could be going on'
Thanks,
AngelinaHi,
Execute the SQL Profiler for that user (set the filter) and
check what is happening.
This can be because of slow network as well (in his PC ).
Thanks
Hari
MCDBA
"Angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:16d5701c448ab$4f0ee440$a601280a@.phx.gbl...
> I have a user that is losing his database connection
> after about 6 minutes or so with error:
> Database error code: 10025
> Database error message: Select error: Possible network
> error: Write to SQL server failed. General network error.
> The same user access another application on the same
> server, different database, with no problems and the same
> application used by another user who is having no
> problems.
> Any ideas what could be going on'
> Thanks,
> Angelina|||Could be a firewall or VPN setting that automatically closes the connection
when it is idle. 300 seconds is a common default setting. I know that is
five minutes, but 1 minute of activity and 5 minutes of inactivity = 1 dead
connection.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:16d5701c448ab$4f0ee440$a601280a@.phx.gbl...
> I have a user that is losing his database connection
> after about 6 minutes or so with error:
> Database error code: 10025
> Database error message: Select error: Possible network
> error: Write to SQL server failed. General network error.
> The same user access another application on the same
> server, different database, with no problems and the same
> application used by another user who is having no
> problems.
> Any ideas what could be going on'
> Thanks,
> Angelina

dbnetlib problem when connecting to sql server through vb6

hi,
I am getting this error message when trying to connect to sql server through vb-sqldmo
-2147467259 (80004005)
DEBNETLIB connection open connect() sql server not found.
please help me out of this problem.
vinod.Can u post your connection string here?|||this is the connection string:
note: this was working alright till yesterday since last two years.

ConStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Pharma;Data Source=" & lServerName & ";Connect Timeout=15"|||Originally posted by bvmantos
Can u post your connection string here?

this is it:

ConStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Pharma;Data Source=" & lServerName & ";Connect Timeout=15"|||have you tried both network libraries in your connection string?|||Originally posted by ms_sql_dba
have you tried both network libraries in your connection string?

please explain which libraries u r refererring to, i am using sqldmo.dll