Understanding Orphaned Workspaces in Power BI and How to recover them

What is a Transaction?

A transaction is a unit of work with one or more activities that manipulate data,A transaction has, or at least should have, four main properties known collectively as the ACID properties.

• A stands for Atomicity
• C for Consistency
• I for Isolation
• D for Durability

Atomicity

A transaction either complete in its entirety or not take place at all. In practice, this is not always the default behavior in SQL Server, and in order to achieve true atomicity, you need to add your own error handling code. This is where TRY CATCH is coming. We will talk about this later.

Consistent

should transition the database from one consistent state to another in terms of adhering to the data model, constraints, and triggers.

Isolated

This means that intermediate inconsistent states of the data are supposed to be visible only to the transaction that made the changes, but not to other transactions.

Durable

This means that when you commit the transaction and get an acknowledge from the database that the transaction committed successfully, you can rest assured that the transaction’s changes are durable. This means that the changes can survive a crash of the SQL Server process, such as a result of a power failure event.

Transactions in SQL Server 

SQL Server allows you to either explicitly define the transaction’s boundaries yourself or to let it define those implicitly for you.

To explicitly mark the beginning of a transaction, use the BEGIN TRANSACTION statement (or BEGIN TRAN for brevity). 

To end the transaction and commit its work, use the COMMIT TRANSACTION statement (supported alternatives: COM-MIT TRAN, COMMIT WORK and just COMMIT). 

To end a transaction and roll back its work, undoing all of its changes, use the ROLLBACK TRANSACTION statement (supported alterna-tives: ROLLBACK TRAN, ROLLBACK WORK and just ROLLBACK).

You can query a function called @@TRANCOUNT to know whether you’re currently in an open transaction or not. If you’re in an open transaction the function returns a value greater than zero, otherwise, it returns zero. I provide more details about this function later under the topic Nesting of transactions.

Example:

Well you get confused on XACT_ABORT option determines the effect of run-time errors raised by T-SQL statements. When this option is OFF (the default in most cases), some errors cause an open transaction to roll back and the execution of the code to be aborted, whereas other errors leave the transaction open.
To get a more reliable and consistent behavior, I consider it a best practice to set this option to ON, and this way all errors cause an open transaction to be rolled back and the execution of the code to be aborted. 

The NOCOUNT option suppresses messages indicating how many rows were affected by data manipulation statements. When it’s OFF (the default), those messages can degrade query performance due to the network traffic that they generate, plus this causes trouble for client applications that perceive those as query results.

Useful Links

XACT_ABORT

NOCOUNT

TRANSACTIONS