Thomas Franz
  • Posts: 21
  • Joined: 6/29/2015
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).
Thomas Franz
  • Posts: 21
  • Joined: 6/29/2015
Thanks - change was implemented in SSMSBoost v3.0 Beta5 (beside the timer to check for session-external kills / disconnects).

Nice to have: show the time when the open-transaction-status was checked the last time (= finish time of the last executed statement in this statement) plus maybe the time when the transaction was started (start time of the first "occurence")
SSMSBoost
  • Posts: 287
  • Joined: 6/30/2012
Hello Thomas,
We hope you like the feature, because it was implemented exclusively on your request 😉
We purposely did not add any timer events, as far as checking if there is an open transaction is a real SQL query (SELECT @@TRANCOUNT). SSMS does it internally and we simply re-use their native function.
We check if there is an open transaction after execution of SQL batch and, as well, before user tries to switch connection using OUR preferred connections control. Yes - this feature can be extended and made much more comfortable, but it will also add more calls to your database, and sometimes it is deadly, if your server has come deadlock-issues.
We suggest you and other users to evaluate current implementation and tell us your impressions after a few weeks or months..
Thomas Franz
  • Posts: 21
  • Joined: 6/29/2015
Yes, it is very nice / useful. Thank you very much.
  • You cannot post new topics in this forum.
  • You cannot reply to topics in this forum.
  • You cannot delete your posts in this forum.
  • You cannot edit your posts in this forum.
  • You cannot create polls in this forum.
  • You cannot vote in polls in this forum.