Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dannyb
Starting Member
4 Posts |
 Posted - 2009-12-30 : 09:12:06
|
| hello!I am running SQL Server 2008 and recently two standard queries failed with error:'The transaction log for database 'tempdb' is full'I looked into this and found that indeed the two logs were full at a combined usage of 1.6 TB. (yes this is a large database!) Since we use simple recovery mode, I believe usually this would mean that there are large uncommitted transactions. This was confirmed by the log_reuse_wait_desc column in sys.databases showing 'ACTIVE_TRANSACTION' for two of the larger databases. I therefore looked at what these transactions could be in the sys.dm_tran_database_transactions table and found 6 transactions open on tempdb with no start date, but with the following transaction IDs (936, 933, 931, 926, 938, 929). Most of the other fields were set to null. I have included one row of these incase it helps, but it is difficult to read!Does any one have any idea about how I can find out more about these ghost transactions? There is nothing else executing on the server right now. Does anyone also know how to stop them safely?Thanks!Dantransaction_id database_id database_transaction_begin_time database_transaction_type database_transaction_state database_transaction_status database_transaction_status2 database_transaction_log_record_count database_transaction_replicate_record_count database_transaction_log_bytes_used database_transaction_log_bytes_reserved database_transaction_log_bytes_used_system database_transaction_log_bytes_reserved_system database_transaction_begin_lsn database_transaction_last_lsn database_transaction_most_recent_savepoint_lsn database_transaction_commit_lsn database_transaction_last_rollback_lsn database_transaction_next_undo_lsn936 2 NULL 2 3 0 256 0 0 0 0 0 0 NULL NULL NULL NULL NULL NULL |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-30 : 09:55:04
|
| what does sp_who2 show you for those spids?Mike"oh, that monkey is going to pay" |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-30 : 09:56:25
|
| to get the text for the spids you can callDBCC INPUTBUFFER (spid)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dannyb
Starting Member
4 Posts |
Posted - 2009-12-30 : 10:32:41
|
| Hi, Thanks for your prompt replies!! I believe that the Transaction IDs are not SPIDs? I certainly cannot find them in the sp_who2 table. Normally the Transaction ids are quite big eg the one I made calling the sp_who2 was '450792650'.I am new at this so please let me know if I am missing something!-Dan |
|
|
|
timford
Starting Member
1 Post |
Posted - 2009-12-30 : 11:05:55
|
| If you're looking to identify the sql text associated with the transactions then I suggest the following: dispense with DBCC INPUTBUFFER. If you're hitting DMVs then you're at least on SQL 2005:SELECT DER.session_id, DTDT.transaction_id, DEST.text FROM sys.dm_tran_database_transactions DTDT INNER JOIN sys.dm_tran_session_transactions DTST ON DTDT.transaction_id = DTST.transaction_id INNER JOIN sys.dm_exec_requests DER ON DTST.session_id = DER.session_id CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DESTTimothy Ford, MCSD, MCPMicrosoft SQL Server MVP |
|
|
|
dannyb
Starting Member
4 Posts |
Posted - 2009-12-30 : 11:51:59
|
| Hi Tim - thank you for your help too!yes you are right I am on SQL server 2008. however when I run your code I get nothing because the sys.dm_tran_session_transactions table is empty. However I do see things in the sys.dm_exec_requests and I did not know about this table. I have matched them with the sp_who2 and it makes sense as all these SPIDs are dated the 24th, when this problem started. I have put links to the two table below and I hope you are able to read them. Cheers, Dansp_who2http://www.danielburrows.co.uk/sp_who2.pngsys.dm_exec_requestshttp://www.danielburrows.co.uk/sys.dm_exec_requests.png |
|
|
|
dannyb
Starting Member
4 Posts |
Posted - 2009-12-31 : 05:44:34
|
| Hello So just an update, in the end I decided to manually shrink the log files and then restart the database engine. As expected (and feared) the database went into recovery as it rolled back those wierd transactions. Luckly even thought they had been going since the 24th, the roll back only took 15 minutes! looks like it was right to say that they were just in a hung state. Anyway... weird error and i hope that i don't see any more of these transactions. I noticed when I restarted the server that updates were installed on that day, so I wonder if these were somehow residue from that. No sure how windows updates could place transactions on the server but its an odd coincidence. Happy New year!Dan |
|
|
|
|
|
|
|
|
Ghost transactions??
