Would it be possible to mark an tab that has a open (not commited / rolled back) transaction e.g. by changing the title, showing an Icon or using a red/yellow blinking background color (no, not really 😁) ?Reason one:
A few days ago I tested some stuff to simulate blocking situations and executed an explicit BEGIN TRANSACTION. Somehow I forgot to COMMIT it after I was done with my tests (which was no problem, since I touched not really something). Some hours later I wanted to test a really big procedure (~2-3 hours runtime) and reused the window with the still open transaction (just deleted the old code without reading it again), which results in much locks on many tables (only on a test-copy of the database) and a full transaction log file on the TempDB (never tought, that this could be possible) which made the whole server unusable.
To make it worser I started the master.dbo.sp_WhoIsActive procedure (by Adam Machanic) in the same SSMS window (the big procedure was done) as soon I saw the TempDB-Transaction-Log mails flooding my inbox, but sp_WhoIsActive did not come back (I guess because of the TempDB). So I did the next mistake and pressed the Stop button, which resulted in a 1-hour-rollback of the whole big procedure...Reason two:
Sometimes I execute a BEGIN TRANSACTION, run a few other commands that would be executed successfull, until I run onto an error and the whole transaction was rolled back (of course not for every error and of course without telling me something about the silent rollback). This could be ok, but when it is an uncritical error, which I want to ignore, so I run some more commands, believing, that I'm still in my explicit transaction and could roll back everything -> data garbage that could not be rolled back... Solution:
- use the existing Post-Execution trigger to check, if the current session has still an open transaction
- if no remove the open-transaction-hint and exit procedure. The hint could be visible at this time e.g. because the last executed command was the COMMIT
- if yes, show it
- enable a timer that checks regularly (e.g. once per minute), if the transaction is still open, remove the hint if not. Necessary, since someone could have killed the session, the PC could have been in standby, the server could have crashed ...
- when I change the server (by using your connection combobox or the SSMS alter connection button) or disconnect the session, remove the hint
PS: to solve the TempDB problem I executed an ALTER DATABASE ADD FILE command (the SSMS-GUI could not be used, since it uses the TempDB too).
Edited by moderator
Monday, May 22, 2017 6:30:41 PM
| Reason: Not specified