[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1266
  • Last Modified:

Deadlock in MS-SQL server version 2000 ??????

Hello   Please HEEEEEEEEEELP........

I have an Java solution using tomcat and Jboss and eather Mysql or MSSQL.
When I use same application on MySQL it  runs without problems and I have no prob. at all with application.
But I have one customer running MSSQL and it runs great but somethimes I m receiving calls that they can not log on the system.
When I check all services they are startet (Tomcat, Jboss, CollaborationServer(my own), and MSSQL)
But in Jboss log I can see that it throws this error: (I think it is some kind of deadlock in MSSQL?)

[2006-01-24 08:19:27,203 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319220, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 08:19:27,343 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319224, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 08:19:29,015 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319235, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 08:21:33,468 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319267, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 08:28:00,859 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319273, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:16:33,750 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319275, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:16:37,156 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319277, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:16:38,390 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319279, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:16:49,734 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319281, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:17:24,265 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319283, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:19:14,734 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319285, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:25:53,000 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319287, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:25:56,218 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319289, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 09:49:34,250 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319291, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 10:18:08,656 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319293, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 11:35:30,300 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319295, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 11:37:33,672 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319297, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 12:36:47,327 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319299, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 12:47:17,087 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319301, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 13:55:53,287 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319303, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:01:49,751 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319305, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:52:20,522 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319307, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:52:45,656 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319309, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:52:59,667 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319311, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:55:41,981 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319313, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:59:48,802 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319315, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 15:59:48,990 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319317, BranchQual=] timed out. status=STATUS_ACTIVE
[2006-01-24 16:00:20,559 - WARN TxCapsule] Transaction XidImpl [FormatId=257, GlobalId=www18//30319319, BranchQual=] timed out. status=STATUS_ACTIVE

And I have to restart all services beore this is gone....and it is not specific time this occure...sometimes every week sometimes  1 time in a monthe....Please heeeeeelp????



Machine spec:

OS: Windows server 2003 service pack 1
RAM: 1 GB
Processor: Intel XEON 3.00 GHz

Software:

MS SQL server 2000


0
dm98101
Asked:
dm98101
  • 9
  • 3
1 Solution
 
Eugene ZCommented:
post sql server 2000 error log ..
0
 
dm98101Author Commented:
2006-04-24 10:22:04.57 server    Microsoft SQL Server  2000 - 8.00.818 (Intel X86)
      May 31 2003 16:08:15
      Copyright (c) 1988-2003 Microsoft Corporation
      Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-04-24 10:22:04.57 server    Copyright (C) 1988-2002 Microsoft Corporation.
2006-04-24 10:22:04.57 server    All rights reserved.
2006-04-24 10:22:04.57 server    Server Process ID is 2244.
2006-04-24 10:22:04.57 server    Logging SQL Server messages in file 'c:\MSSQL\log\ERRORLOG'.
2006-04-24 10:22:04.60 server    SQL Server is starting at priority class 'normal'(2 CPUs detected).
2006-04-24 10:22:04.71 server    SQL Server configured for thread mode processing.
2006-04-24 10:22:04.71 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-04-24 10:22:04.82 server    Attempting to initialize Distributed Transaction Coordinator.
2006-04-24 10:22:07.20 spid2     Starting up database 'master'.
2006-04-24 10:22:07.56 spid2     0 transactions rolled back in database 'master' (1).
2006-04-24 10:22:07.56 spid2     Recovery is checkpointing database 'master' (1)
2006-04-24 10:22:09.14 spid2     Server name is 'WWW18'.
2006-04-24 10:22:09.17 spid5     Starting up database 'msdb'.
2006-04-24 10:22:09.17 server    Using 'SSNETLIB.DLL' version '8.0.818'.
2006-04-24 10:22:09.17 spid6     Starting up database 'model'.
2006-04-24 10:22:09.17 spid8     Starting up database 'videlity'.
2006-04-24 10:22:09.23 server    SQL server listening on 127.0.0.1: 1433.
2006-04-24 10:22:09.31 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2006-04-24 10:22:09.31 server    SQL Server is ready for client connections
2006-04-24 10:22:09.32 spid5     22 transactions rolled forward in database 'msdb' (4).
2006-04-24 10:22:09.45 spid5     0 transactions rolled back in database 'msdb' (4).
2006-04-24 10:22:09.45 spid6     Clearing tempdb database.
2006-04-24 10:22:09.50 spid5     Recovery is checkpointing database 'msdb' (4)
2006-04-24 10:22:09.96 spid6     Starting up database 'tempdb'.
2006-04-24 10:22:10.39 spid8     Analysis of database 'videlity' (7) is 100% complete (approximately 0 more seconds)
2006-04-24 10:22:10.39 spid8     Recovery of database 'videlity' (7) is 0% complete (approximately 12 more seconds) (Phase 2 of 3).
2006-04-24 10:22:10.87 spid8     Recovery of database 'videlity' (7) is 99% complete (approximately 0 more seconds) (Phase 2 of 3).
2006-04-24 10:22:10.87 spid8     2178 transactions rolled forward in database 'videlity' (7).
2006-04-24 10:22:10.89 spid8     Recovery of database 'videlity' (7) is 99% complete (approximately 0 more seconds) (Phase 3 of 3).
2006-04-24 10:22:11.00 spid8     Recovery of database 'videlity' (7) is 100% complete (approximately 0 more seconds) (Phase 3 of 3).
2006-04-24 10:22:11.00 spid8     1 transactions rolled back in database 'videlity' (7).
2006-04-24 10:22:11.01 spid8     Recovery is checkpointing database 'videlity' (7)
2006-04-24 10:22:11.06 spid2     Recovery complete.
2006-04-24 10:22:11.06 spid2     SQL global counter collection task is created.
2006-04-24 10:22:12.31 spid51    Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2006-04-24 11:19:52.02 spid53    Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2006-04-25 10:07:09.74 server    SQL Server terminating because of system shutdown.
0
 
dm98101Author Commented:
second:

2005-10-22 14:36:21.10 server    Microsoft SQL Server  2000 - 8.00.818 (Intel X86)
      May 31 2003 16:08:15
      Copyright (c) 1988-2003 Microsoft Corporation
      Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2005-10-22 14:36:21.15 server    Copyright (C) 1988-2002 Microsoft Corporation.
2005-10-22 14:36:21.15 server    All rights reserved.
2005-10-22 14:36:21.15 server    Server Process ID is 2252.
2005-10-22 14:36:21.15 server    Logging SQL Server messages in file 'c:\MSSQL\log\ERRORLOG'.
2005-10-22 14:36:21.17 server    SQL Server is starting at priority class 'normal'(2 CPUs detected).
2005-10-22 14:36:21.29 server    SQL Server configured for thread mode processing.
2005-10-22 14:36:21.29 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2005-10-22 14:36:21.34 server    Attempting to initialize Distributed Transaction Coordinator.
2005-10-22 14:37:06.18 spid2     Starting up database 'master'.
2005-10-22 14:37:06.34 server    Using 'SSNETLIB.DLL' version '8.0.818'.
2005-10-22 14:37:06.34 spid5     Starting up database 'model'.
2005-10-22 14:37:06.35 spid2     Server name is 'WWW18'.
2005-10-22 14:37:06.35 spid8     Starting up database 'msdb'.
2005-10-22 14:37:06.35 spid9     Starting up database 'pubs'.
2005-10-22 14:37:06.37 spid11    Starting up database 'videlity'.
2005-10-22 14:37:06.37 spid10    Starting up database 'Northwind'.
2005-10-22 14:37:06.49 spid5     Clearing tempdb database.
2005-10-22 14:37:06.54 server    SQL server listening on 192.168.3.18: 1433.
2005-10-22 14:37:06.54 server    SQL server listening on 127.0.0.1: 1433.
2005-10-22 14:37:06.57 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2005-10-22 14:37:06.57 server    SQL Server is ready for client connections
2005-10-22 14:37:06.79 spid5     Starting up database 'tempdb'.
2005-10-22 14:37:07.49 spid2     Recovery complete.
2005-10-22 14:37:07.49 spid2     SQL global counter collection task is created.
2005-11-01 14:06:54.36 spid85    Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2005-11-24 10:17:42.45 backup    Database backed up: Database: videlity, creation date(time): 2005/06/10(15:09:26), pages dumped: 31221, first LSN: 31977:1782:1, last LSN: 31977:1801:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\OTC2411.BAK'}).
2006-02-08 23:30:05.34 spid87    Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_msver'.
2006-02-13 19:21:11.71 backup    Database backed up: Database: videlity, creation date(time): 2005/06/10(15:09:26), pages dumped: 46105, first LSN: 32542:210560:1, last LSN: 32542:210591:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {'c:\OTC2411.BAK'}).
2006-02-15 15:58:06.25 spid86    Error: 15457, Severity: 0, State: 1
2006-02-15 15:58:06.25 spid86    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install..
2006-03-02 14:06:00.15 spid80    Error: 15457, Severity: 0, State: 1
2006-03-02 14:06:00.15 spid80    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LowfatspreadCommented:
are all the services running on the same server for this client?
(ideally sql server should run on its own server)

do they run anything else on the machine?

how is SQL server configured?

what is the size of the database ....
what backup strategy ios implemented?

do you recognise the 'XidImpl' transaction as one of your own?

is the client running there own <independant> (e.g. Query Analyser) processes againts the database?



0
 
dm98101Author Commented:
THSI IS FROM FILE CALLED: SQLAGENT1

2006-04-24 10:22:10 - ? [393] Waiting for SQL Server to recover databases...
2006-04-24 10:22:12 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86 unicode retail build) : Process ID
2006-04-24 10:22:12 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86 unicode retail build) : Process ID 3044
2006-04-24 10:22:12 - ? [101] SQL Server WWW18 version 8.00.818 (0 connection limit)
2006-04-24 10:22:12 - ? [102] SQL Server ODBC driver version 3.86.1830
2006-04-24 10:22:12 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is
2006-04-24 10:22:12 - ? [310] 2 processor(s) and 1024 MB RAM detected
2006-04-24 10:22:12 - ? [339] Local computer is WWW18 running Windows NT 5.2 (3790) Service Pack 1
2006-04-24 10:22:12 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2006-04-24 10:22:12 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2006-04-24 10:22:12 - + [260] Unable to start mail session (reason: No mail profile defined)
2006-04-24 10:22:12 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2006-04-24 13:13:56 - ! [LOG] Unable to read local eventlog (reason: The data area passed to a system call is too small)
2006-04-25 10:07:30 - ? [130] SQLSERVERAGENT stopping because of Windows shutdown...

0
 
dm98101Author Commented:
ALSO FROM: SQLAGENT2
2005-06-10 15:07:59 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86 unicode retail build) : Process ID
2005-06-10 15:07:59 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86 unicode retail build) : Process ID 5688
2005-06-10 15:07:59 - ? [101] SQL Server WWW18 version 8.00.818 (0 connection limit)
2005-06-10 15:07:59 - ? [102] SQL Server ODBC driver version 3.86.1830
2005-06-10 15:07:59 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is
2005-06-10 15:07:59 - ? [310] 2 processor(s) and 1024 MB RAM detected
2005-06-10 15:07:59 - ? [339] Local computer is WWW18 running Windows NT 5.2 (3790) Service Pack 1
2005-06-10 15:07:59 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2005-06-10 15:07:59 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2005-06-10 15:07:59 - + [260] Unable to start mail session (reason: No mail profile defined)
2005-06-10 15:07:59 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2005-06-10 15:11:29 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2005-06-10 15:11:32 - ? [098] SQLServerAgent terminated (normally)
0
 
dm98101Author Commented:
Yes all applications are running on same server.

Applications installed are:

Windows Server 2003 SP 1
MSSQL 2000      ver.  8.00.818   SP3
EZ trust antivirus
JBOSS
TOMCAT
MyODBC
HP Version Control Agent 2.1
HP system Management Homepage
HP Proliant Remote monitor service
HĂ… array configuration utility

DATABASE size is:  2,5 GB

How to check how is SQL server configured??????

There is no backup



0
 
dm98101Author Commented:
Here is what XidImpl is:

org.jboss.tm.XidImpl =  This object encapsulates the ID of a transaction. This implementation is immutable and always serializable at runtime.
0
 
Eugene ZCommented:
if there are deadlocks on sql server:
try to activate trace flags and then check sql server error log:

dbcc traceon (1205, 3605, -1)
go
dbcc tracestatus(-1)
go
--xp_readerrorlog 1
--DBCC TRACEOFF
more:
Trace Flags
http://www.sql-server-performance.com/rd_traceflags.asp
0
 
dm98101Author Commented:
how to solve that issue..is is possible to give more memory to MSSQL in configuration somewhere?????
0
 
dm98101Author Commented:
any patch? what to do?  please heeeeeelp
0
 
dm98101Author Commented:
I have tracing results from today when I have experienced a deadlock:
Here it is:

Deadlock encountered .... Printing deadlock information
2006-05-01 07:47:06.39 spid2    
2006-05-01 07:47:06.39 spid2     Wait-for graph
2006-05-01 07:47:06.39 spid2    
2006-05-01 07:47:06.39 spid2     Node:1
2006-05-01 07:47:06.39 spid2     KEY: 7:345768289:1 (1800113d2079) CleanCnt:1 Mode: X Flags: 0x0
2006-05-01 07:47:06.39 spid2      Grant List 0::
2006-05-01 07:47:06.39 spid2        Owner:0x90bffa0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
2006-05-01 07:47:06.40 spid2        SPID: 84 ECID: 0 Statement Type: EXECUTE Line #: 1
2006-05-01 07:47:06.40 spid2        Input Buf: RPC Event: sp_cursoropen;1
2006-05-01 07:47:06.40 spid2      Requested By:
2006-05-01 07:47:06.40 spid2        ResType:LockOwner Stype:'OR' Mode: S SPID:75 ECID:0 Ec:(0x2DDE7500) Value:0x8c8d860 Cost:(0/1DF8)
2006-05-01 07:47:06.40 spid2    
2006-05-01 07:47:06.40 spid2     Node:2
2006-05-01 07:47:06.40 spid2     KEY: 7:1407344078:1 (6801058069a5) CleanCnt:1 Mode: X Flags: 0x0
2006-05-01 07:47:06.40 spid2      Grant List 1::
2006-05-01 07:47:06.40 spid2        Owner:0x29252fe0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2006-05-01 07:47:06.40 spid2        SPID: 83 ECID: 0 Statement Type: EXECUTE Line #: 1
2006-05-01 07:47:06.40 spid2        Input Buf: RPC Event: sp_cursoropen;1
2006-05-01 07:47:06.40 spid2      Requested By:
2006-05-01 07:47:06.40 spid2        ResType:LockOwner Stype:'OR' Mode: S SPID:84 ECID:0 Ec:(0x518EB500) Value:0x3cb9e800 Cost:(0/205C)
2006-05-01 07:47:06.40 spid2    
2006-05-01 07:47:06.40 spid2     Node:3
2006-05-01 07:47:06.40 spid2     KEY: 7:1407344078:1 (450111f3ce56) CleanCnt:1 Mode: X Flags: 0x0
2006-05-01 07:47:06.40 spid2      Grant List 1::
2006-05-01 07:47:06.40 spid2        Owner:0x29252640 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:75 ECID:0
2006-05-01 07:47:06.40 spid2        SPID: 75 ECID: 0 Statement Type: EXECUTE Line #: 1
2006-05-01 07:47:06.40 spid2        Input Buf: RPC Event: sp_cursoropen;1
2006-05-01 07:47:06.40 spid2      Requested By:
2006-05-01 07:47:06.40 spid2        ResType:LockOwner Stype:'OR' Mode: S SPID:83 ECID:0 Ec:(0x030ED500) Value:0x29252400 Cost:(0/537C)
2006-05-01 07:47:06.40 spid2     Victim Resource Owner:
2006-05-01 07:47:06.40 spid2      ResType:LockOwner Stype:'OR' Mode: S SPID:75 ECID:0 Ec:(0x2DDE7500) Value:0x8c8d860 Cost:(0/1DF8)
0
 
Eugene ZCommented:
Optimize the query!

can you do not use cursors ? Do you have indexes:
Did you run DBCC DBReindex?...

check:

Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp

Exploring Deadlocks
http://www.sqlservercentral.com/columnists/rmarda/exploringdeadlocks.asp
-
also check
SET DEADLOCK_PRIORITY
http://msdn2.microsoft.com/en-us/library/ms186736.aspx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now