Step 1:- Log in the dataserever using isql
isql -Usa -S<SID> -X -w6000
Step 2:- Check the long running trans as below
If there is any row in syslogshold, see the start time
column and current time,
If there is big diff, it means you have log runningg trans
in dataserver and you need to esclate to APP team.
for particular db holds
select * from syslogshold where
dbid=db_id("<DB_NAME>")
go
select db_id("<DB_NAME>")
go
EXAMPLE :
1> select * from syslogshold
2> go
dbid reserved spid
page xactid masterxactid starttime
name
xloid
------ ----------- ------ ----------- --------------
-------------- --------------------------
-------------------------------------------------------------------
-----------
4 0
627 43859290 0x029d3d5a0005
0x000000000000 Jun 12 2011 9:34AM
$REORG REBUILD dmASSET_HIST ID=621764747 1254
2 0
606 332908 0x0005146c000a
0x000000000000 Jun 12 2011 3:02AM
$ins
628
(2 rows affected)
1> select getdate()
2> go
--------------------------
Jun 12 2011 4:00PM
(1 row affected)
Step 3:- Now we need to find the detail of blocking spid as
below
1> select * from sysprocesses where spid=606
2> go
spid kpid enginenum status
suid hostname program_name hostprocess cmd cpu
physical_io memusage blocked dbid uid
gid
tran_name
time_blocked network_pktsz fid
execlass priority affinity id stmtnum linenum
origsuid
block_xloid
clientname
clienthostname
clientapplname
sys_id
ses_id loggedindatetime ipaddr
------ -----------
----------- ------------ ----------- ---------- ---------------- -----------
---------------- -----------
-----------
----------- ------- ------ ----------- -----------
----------------------------------------------------------------
------------ ------------- ------
------------------------------
---------- ------------------------------ ----------- ----------- -----------
-----------
-----------
------------------------------ ------------------------------
------------------------------ ------
-----------
-------------------------- ---------------
606 283443444 4 lock sleep 546 nyggmgrotc RESETREP 23822 INSERT 3
0 22 627
4 405 16390
$user_transaction
15143 512 0
BS4 HIGH ANYENGINE 637764804 5 439 NULL
0 0
0 Jun 12 2011 11:24AM
10.152.115.100
(1 row affected)
1> sp_who "606"
2> go
fid spid status
loginame
origname hostname blk_spid
dbname cmd block_xloid
------ ------ ------------ ------------------------------
------------------------------ ---------- --------
------------------------------ ---------------- -----------
0 606 lock sleep lcprusr lcprusr nyggmgrotc 627
tempdb INSERT 0
(1 row affected)
(return status = 0)
1> dbcc traceon(3604)
2> go
NOTE: 3604 is used to send output back to the client instead
of writing to error log
1> dbcc sqltext(606)
2> go
SQL Text: 320110714CONV_AVG
AUTO CARRY
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1> sp_showplan 606,null,null,null
2> go
QUERY PLAN FOR
STATEMENT 5 (at line 439).
STEP 1
The type of
query is INSERT.
The update mode
is direct.
TO TABLE dmASSET_HIST
Using I/O Size 2 Kbytes for data pages.
(return status = 0)
1> sp_lock 606
2> go
The class column will
display the cursor name for locks associated with a cursor for the current user
and the cursor id for other
users.
fid spid loid
locktype
table_id page row
dbname
class context
------ ------ ----------- ----------------------------
----------- ----------- ------ ---------------
------------------------------ ----------------------------
0 606
1686 Ex_intent
589764633 0 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_row
589764633 199169 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_intent
635720842 0 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_row
635720842 22607248 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_intent
667720956 0 0 tempdb
Non Cursor Lock
0 606
1686 Ex_row
667720956 46143296 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_intent
1076770418 0 0 tempdb
Non Cursor Lock
0 606
1686 Ex_row-blk
1076770418 13586806 2 tempdb
Non Cursor
Lock
0 606
1686 Ex_row
1076770418 13807873 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_intent 1108770532 0 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_row
1108770532 18465042 2 tempdb
Non Cursor
Lock
0 606
1686 Ex_row 1108770532 33477240 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_intent-blk
1732772755 0 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_row
1732772755 13475329 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_intent 1764772869 0 0 tempdb
Non Cursor
Lock
0 606
1686 Ex_row
1764772869 19673089 0 tempdb
Non Cursor
Lock
(16 rows affected)
(return status = 0)
Step 4:- With above detail you need to send the mail to APP
Team.
Take the approval from them and kill the open transaction .
Causes of Long-Running Transactions ?
Some of the causes for a long-running transaction include:
An incorrectly written update, insert, or delete statement
that runs for many hours.
Commands that create Cartesian products or include user
input are common mistakes in coding.
An application error that starts a transaction but never
completes it.