Post reply

uncommitted transactions in tempdb (SQL2005)

  • August 6, 2008 at 7:53 am

    #191995

    I was testing a stored procedure which contained a explicit transaction (BEGIN TRAN) unfortunatly the sp errored and did not rollback the transaction and now the tempdb has 2 open transactions (i ran it twice before I realised) according to Activity Monitor, but DBCC OPENTRAN returns "No active open transactions".

    I have restarted the dbengine and then the pc, but still they are there and it is stopping me from running the sp again with the error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

    Does anyone know how to fix?

    Thanks,

    Lee.

  • August 6, 2008 at 7:56 am

    #853825

    No backups, I suppose...

    -- Gianluca Sartori

  • August 6, 2008 at 8:01 am

    #853830

    forgot to mention that I had tried to restore a backup of the db that the sp was running in, but that didnt work.

    I restored using the wizard with "overwrite" and recovery set to "leave db rady to use and rollback uncommitted trans"

    I get the error:

    "TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Restore failed for Server 'servername\MSSQL2005'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: RESTORE cannot process database 'MSCD_TEST' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • August 6, 2008 at 8:08 am

    #853835

    I just tried running the back using t-sql instead of the wizard, which did restore the db, but still the tempdb has uncommitted transactions! even after a restart of the dbengine

    Lee.

  • August 6, 2008 at 8:38 am

    #853862

    I can run other transactions, but i run the sp that caused the prob I still get the error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

    how can I rollback this transaction?

    I also stopped the dbengine and deleted the tempdb files, restarted dbengine (which created new tempdb) and still the trans are there!

  • August 6, 2008 at 9:14 am

    #853904

    Ok I found what was causing the "The current transaction cannot be committed ...." error, my sp calls another which in turn calls another sp and its the 3rd one that is erroring and then the first sp cannot rollback.

    But still I have the 2 open transactions in tempdb, but they dont seem to be causing me problems (i hope)

    Lee.

Viewing 6 posts - 1 through 5 (of 5 total)

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