Step 1:- Log in the dataserever using isql

isql -Usa -S -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("")

go

select db_id("")

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

 DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

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.