Dead lock issue

Question

Post reply

Dead lock issue

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
September 19, 2011 at 8:27 am

#245080

HI All,

I enabled dbcc traceon(1222,-1) on my server and i run a profiler with Deadlock: graph for finding dead locks but i am not finding any dead locks in sql server error logs and profiler .

I getting an error from appllication side is No ManagedConnections available within configured blocking timeout

Please help on this , this is very urgent issue for me

Regards,

Lavanya

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

If you're not seeing deadlocks in the error log with that traceflag on then you aren't getting any deadlocks. That's a good thing.

As for the error, that's not a SQL Server error, maybe consult with the application vendor and find out what causes it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 2

HI Gila ,

Thanks for the quick response . At that time i find a query

(@P0 int OUTPUT,@P1 nvarchar(4000) OUTPUT,@P2 varbinary(8000),@P3 varbinary(8000),@P4 int)EXEC master..xp_sqljdbc_xa_commit @P0 OUT, @P1 OUT, @P2, @P3, @P4

By running the profiler with sp: stmt complted and TSQl : Stmt completed

i am seeing the below queries taking more cpu compare to other

EXEC master..xp_sqljdbc_xa_end @P0 OUT, @P1 OUT, @P2, @P3, @P4

EXEC master..xp_sqljdbc_xa_start @P0 OUT, @P1 OUT, @P2, @P3, @P4, @P5 OUT, @P6

EXEC master..xp_sqljdbc_xa_commit @P0 OUT, @P1 OUT, @P2, @P3, @P4

Any suggestions on this .........

Many Thanks

Lavanya

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

That's JDBC stuff, has to do with it's transaction handling.

Nothing more that can really be said with the little you posted.

I'd start with the app vendor (or developers if it's in-house) and find out what causes that error (because it is not a SQL error)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 4

HI Gila,

Thanks for the update .

By using the profiler we can find the which queries are running ,but yesterday night i faced a problem with site outage . We need to find whcih queries are effected this ??

If any possibilty can we find out which quries are run at that particular time with any script or ??

Regards,

Lavanya

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Unless you had some form of trace or auditing running at the time, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 6

Hi Gila,

Thanks for the information .

I had some confusion , what is the recommended transaction isolation level .

I run " dbcc useroptions " i find that :

read committed for system databases and

read committed snapshot for user databases .

It may cause for any performance issues ??

Any suggestions on this ..

Regards

Lavanya

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Hang on...

What is your question here? What is your problem? You're jumping around from topic to topic and I can't figure out what the root problem is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 8

HI ,

sorry for confusion.

Iam facing some performance issues with my server . I am trying to find out root cause of this , so i am going step by step.

1. I try to find out deadlocks by enabling dbcc and profiler -- no deadlocks

2. i find out some queries and posted -- as per u r suggestion its related to JDBC

3. Now i am looking for isloation levels [may be it cause for performance ]

Final my aim is to find out the performance issue is related to db side or application side .

regards,

Lavanya

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

You're barking up the complete wrong tree.

If you have no idea where to start, get a specialist in to help. Or

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 10

HI All,

I am continuously seeing the below queries taking more cpu time .

EXEC master..xp_sqljdbc_xa_end @P0 OUT, @P1 OUT, @P2, @P3, @P4

EXEC master..xp_sqljdbc_xa_start @P0 OUT, @P1 OUT, @P2, @P3, @P4, @P5 OUT, @P6

EXEC master..xp_sqljdbc_xa_commit @P0 OUT, @P1 OUT, @P2, @P3, @P4

Its very urgent for me

i need u r suggestions ..???

Please help on this , i am not find any solution in google also ..

Thanks

Lavanya sri

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Lavanyasri (10/3/2011)

HI All,

I am continuously seeing the below queries taking more cpu time .

EXEC master..xp_sqljdbc_xa_end @P0 OUT, @P1 OUT, @P2, @P3, @P4

EXEC master..xp_sqljdbc_xa_start @P0 OUT, @P1 OUT, @P2, @P3, @P4, @P5 OUT, @P6

EXEC master..xp_sqljdbc_xa_commit @P0 OUT, @P1 OUT, @P2, @P3, @P4

Its very urgent for me

i need u r suggestions ..???

Please help on this , i am not find any solution in google also ..

Thanks

Lavanya sri

You're basically asking us why your car can NOT go above 250 mph. There's 1 million answers to that question.

Either get better at fixing this in 1 second or hire someone.

Calling your application support line might be a good way to go (SOMETIMES it works, not always).

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 12

Lavanyasri

SSCarpal Tunnel

Points: 4907

October 3, 2011 at 6:11 am

#1389445

HI Ninja,

Just i want to know when we will get these type of queries ?

It is due to application changes or Db changes ?

Thanks ,

Lavanya

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Ninja's_RGR'us

SSC Guru

Points: 294069

October 3, 2011 at 6:23 am

#1389452

How can I possibly know that? I don't see you're screen and I can't investigate nor did I ever see those queries before.

I'd love to help but I have nothing to go on.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

As I said right at the beginning...

GilaMonster (9/19/2011)

That's JDBC stuff, has to do with it's transaction handling.

Nothing more that can really be said with the little you posted.

I'd start with the app vendor (or developers if it's in-house) and find out what causes that error (because it is not a SQL error)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass