Forum Discussion

SharePointAdmin1030's avatar
SharePointAdmin1030
Copper Contributor
Sep 22, 2025

Azure SQL server rollback itself?

We have an Azure SQL server. It is a datasource of a Power App canvas app. 

Today I connected to it with SSMS v19.

First, I ran 'Begin tran' twice (is it a mistake?). 

Then 'Delete From dbo.table1 where ID=30' and another row with ID=31.

Then I verified these 2 rows are deleted by 'Select * from dbo.table1'

Finally, I ran 'Commit tran'

I verified again above 2 rows are deleted by 'Select * from dbo.table1'

 

However, there is no change in the Power App.

So I reopen the SSMS and connect to the DB again. This time when I ran 'Select * from dbo.table1', the 2 rows are showing up.

 

What could be the problem? Is it a bug in old version SSMS?

2 Replies

  • Arya1978's avatar
    Arya1978
    Copper Contributor
    Mar 23, 2026

    What you’re seeing isn’t a bug in SSMS—it’s about how SQL Server transactions work, especially with Azure SQL.
    Here’s what likely happened:

    Transaction Behavior

    • When you ran BEGIN TRAN twice, you didn’t actually start two independent transactions. SQL Server uses a transaction nesting counter. The first BEGIN TRAN starts a transaction; the second just increments the counter. Only when you run COMMIT TRAN enough times to bring the counter back to zero does the transaction truly commit.
    • If you ran BEGIN TRAN twice but only one COMMIT TRAN, the transaction wasn’t actually committed. SQL Server would keep the changes invisible to other sessions, and when you disconnected, the transaction was rolled back automatically.

    Why Rows Reappeared

    • In your SSMS session, you saw the rows deleted because uncommitted changes are visible to the session that made them.
    • Power Apps (and any other connection) couldn’t see the changes because they weren’t committed.
    • When you closed SSMS, SQL Server rolled back the uncommitted transaction, so the rows came back.

    How to Fix

    • Always balance BEGIN TRAN and COMMIT TRAN. If you start two, you need two commits.
    • Alternatively, don’t use manual transactions unless you need them. A simple DELETE without BEGIN TRAN will auto-commit by default.

    Best Practice

    • Use @@TRANCOUNT to check how many active transactions you have before committing.
    • Example:

                                SELECT @@TRANCOUNT;

    • If it’s greater than zero after your COMMIT, you still have an open transaction.

    So the issue wasn’t SSMS—it was the transaction nesting. Your deletes were never committed, so they were rolled back when you disconnected.