uncommitted transactions in tempdb (SQL2005)

[itlee]

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.

itlee. MCP\Analyst\Programmer\SQL\.NET\VB\C#

 

[JayKusch]

Now ... the TempDB is totally recreated each time the SQL Server service is stopped/restarted or the physical server is rebooted/restarted the TempDB is created totally.

So I am a bit confused as to how anything remains after a recycle of the service and/or server.

Can you give us more details? A peek at the SP?



Thanks

J. Kusch

 

[mrdenny]

Not to mention that any in flight transactions are rolled back when the SQL Server service starts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[itlee]

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.


itlee. MCP\Analyst\Programmer\SQL\.NET\VB\C#