Post reply

Unexpected Database Growth ??

  • August 6, 2012 at 4:55 am

    #1521956

    we are not seeing any blocking and we are not seeing any queries at that particular time 11:10:22:270AM.

  • August 6, 2012 at 5:09 am

    #1521959

    remove this condition and try

    WHERE s.spid > 50

    can you post the top 10 result with login time order by.

    Regards
    Durai Nagarajan

  • August 6, 2012 at 5:13 am

    #1521964

    anthony.green (8/6/2012)

    You have all the information you need in DBCC OPENTRAN, it gives you the SPID, so all you need to do now is query the DMV's to find out what the transaction is doing and where it came from etc.

    Try this:

    select * from sys.dm_tran_session_transactions

  • August 6, 2012 at 5:31 am

    #1521967

    by using the below query i am unable to find any queries during the time.

    select * from sys.dm_tran_session_transactions.

    How can i kill that process . Wintel team can help on this ??

  • August 6, 2012 at 5:40 am

    #1521970

    when i run the below query i found orphaned transaction .

    select req_transactionUOW

    from master..syslockinfo

    where req_spid = -2

    now my server is in live ,Directly can i kill that transaction , it will effect any performance issue or db goes to suspact mode ??

    kill 'AEA20E3C-50B0-40CA-A70F-CB024793B410'like this

  • August 6, 2012 at 5:53 am

    #1521972

    when i try to run the query

    select * from sysprocesses where spid=-2 we are not seeing any rows .

  • August 6, 2012 at 6:49 am

    #1521998

    hi Guys,

    Any update on this ...........

  • August 6, 2012 at 6:54 am

    #1522000

    Dont kill the UOW, first find the host which initiated the DTC transaction and see if it is still active on that host, if it is terminate it, if not then kill the UOW.

    If the UOW is still active on the originiating machine, even killing the UOW wont terminate it, we found this out the hardway when we killed a UOW that was -2 but still active, restarted SQL and it never came backup as the DB went into recovery but as the transaction was ongoing it wouldnt recover.

  • August 6, 2012 at 7:00 am

    #1522008

    Hello Antony,

    when this is specified : LSN : (250855:13949:24)

    is it not a backup related session?

    then in that case do we have to check the orginating server/

    Regards
    Durai Nagarajan

  • August 6, 2012 at 7:01 am

    #1522009

    No thats just a LSN number, that would be the marker in the transaction log where the transaction started

  • August 6, 2012 at 7:06 am

    #1522016

    do you have more details or document on req_transactionUOW relates..

    Regards
    Durai Nagarajan

  • August 6, 2012 at 7:58 am

    #1522059

    first find the host which initiated the DTC transaction and see if it is still active on that host, if it is terminate it, if not then kill the UOW.

    how to find which server initiated and which transaction ??

  • August 6, 2012 at 8:00 am

    #1522062

    you have the spid from dbcc opentran, now look in sys.dm_exec_sessions for that spid

  • August 6, 2012 at 9:08 am

    #1522101

    Thanks to all.

    We abort the indoubt transaction from MSDTC transaction list . After that i run a checkpoint on that database.

    I shrink the LDF file. Now the space is released .

    Thanks

    Lavanya

  • August 6, 2012 at 9:14 am

    #1522107

    Thanks Lavanya for sharing the solution.

    Thanks Antony for leading to this solution.

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply