“Hey, can you run this for me in prod?”
There are many reasons the wording in this request is a red flag/indicator of broken processes, but let’s put those aside for a moment.
The dev that asked you to run this query may have a legitimate reason.
Let’s say you check the estimated execution plan, and the query seems to be low-impact, and unlikely to cause blocking. You run the query. Yep, it only modified a few rows and ran in less than a second. A few minutes later an alert indicates that the query has become a long running statement and is still active.
It turns out there were two BEGIN TRAN statements in the t-sql, and a COMMIT was only executed once. The transaction is still open – uncommitted!
In the future you can guard against this scenario by just checking @@TRANCOUNT after the commit and the time of the commit. If @@TRANCOUNT has a non-zero value, a transaction is still open.
Placing the following two commented lines in SQLfile.sql or pasting the second line at the end of the query can help:
--BEGIN TRAN
[paste query to be executed here, and uncomment the above line if the query modifies data and lacks a BEGIN TRAN]
After successfully running the t-sql, commit the transaction with the below statement by selecting and executing the remainder of the line after the two dashes:
--COMMIT;SELECT @@TRANCOUNT [Open Transactions], Getdate() [timestamp]
A zero value in the Open Transactions column indicates this session has committed the transaction, and the timestamp lets you know when the transaction was committed, just in case you return to this session later on.
