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.
No comments:
Post a Comment