How can we check for the long running trans?

Step 1:- Log in the dataserever using isql

isql -U<user_name> -S<server_name> -w999

Step 2:- Check the long running trans as below
If there is any row in syslogshold, see the starttime 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.

cmd:

use master goselect * from syslogsholdgoselect getdate()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 314 332908 0x0005146c000a 0x000000000000 Jun 12 2011 3:02PM $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
cmd:

use master

go
select * from sysprocesses where spid=<blocking spid>
go
sp_who "<blocking spid>"
traceon(3604)
go
dbcc sqltext(<blocking spid>)
go
sp_showplan <blocing spid>,null,null,null
go
sp_lock <blocking spid>
go
example : (I have colllected the detail for 843 , you need to find out detail for all the blocking spids)

1> select * from sysprocesses where spid=843

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 ------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- ----------- ----------- ----------- ------- ------ ----------- ----------- ---------------------------------------------------------------- ------------ ------------- ------ ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------ ------ ----------- -------------------------- --------------- 843 283443444 4 lock sleep 546 nyggmgrotc RESETREP 23822 INSERT 3 0 22 627 4 405 16390 $user_transaction 15143 512 0 EC2 HIGH ANYENGINE 637764804 5 439 NULL 0 0 0 Jun 12 2011 11:24AM 10.152.115.100 (1 row affected)
1> sp_who "843"
2> go
fid spid status loginame origname hostname blk_spid dbname cmd block_xloid ------ ------ ------------ ------------------------------ ------------------------------ ---------- -------- ------------------------------ ---------------- ----------- 0 843 lock sleep lcprusr lcprusr nyggmgrotc 627 summitdb INSERT 0 (1 row affected)(return status = 0)
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> dbcc sqltext(843)
2> go
SQL Text: 320110714CONV_AVGAUTO CARRY
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> sp_showplan 843,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 843
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    843        1686 Ex_intent                      589764633           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         589764633      199169      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                      635720842           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         635720842    22607248      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                      667720956           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         667720956    46143296      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1076770418           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row-blk                    1076770418    13586806      2 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1076770418    13807873      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1108770532           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1108770532    18465042      2 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1108770532    33477240      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent-blk                 1732772755           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1732772755    13475329      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1764772869           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1764772869    19673089      0 summitdb
        Non Cursor Lock
(16 rows affected)
(return status = 0)

Step 4:- With above detail you need to send the mail to APP Team.