What happen if SQL server crashes before commit

If SQL Server crashes before a transaction commits, all uncommitted changes are automatically rolled back during the Undo phase of crash recovery. SQL Server achieves this through Write-Ahead Logging, where every modification is recorded in the transaction log before being applied to data pages. During startup recovery, SQL Server analyzes the log, reapplies committed transactions through Redo, and removes incomplete transactions through Undo. This mechanism guarantees Atomicity and Durability, ensuring that the database never ends up in a partially committed state, even during power failures, server crashes, storage failures, or unexpected shutdowns. For large enterprise systems such as banking, trading, and e-commerce platforms, this recovery model is one of the fundamental reasons SQL Server can maintain transactional consistency under failure conditions.

If SQL Server crashes before a transaction commits, SQL Server guarantees that none of the uncommitted changes become permanent.

During startup, SQL Server performs Crash Recovery, which consists of:

Ø  Analysis Phase – Identifies active transactions at the time of the crash.

Ø  Redo Phase – Reapplies committed transactions that may not yet be written to data files.

Ø  Undo Phase – Rolls back all uncommitted transactions.

This behavior is possible because of SQL Server's Write-Ahead Logging (WAL) architecture and ACID properties. Any transaction that did not reach COMMIT is treated as incomplete and is automatically rolled back during recovery.

Let’s see the example

We have account table and adding some data into this table

CREATE TABLE dbo.Account (     AccountID       INT IDENTITY(1,1) PRIMARY KEY,     AccountNumber   VARCHAR(20) NOT NULL UNIQUE,     CustomerName    VARCHAR(100) NOT NULL,     AccountType     VARCHAR(20) NOT NULL, -- Savings, Current     Balance         DECIMAL(18,2) NOT NULL DEFAULT(0),     MobileNumber    VARCHAR(15) NULL,     EmailAddress    VARCHAR(100) NULL,     OpenDate        DATE NOT NULL DEFAULT(GETDATE()),     IsActive        BIT NOT NULL DEFAULT(1),     CreatedDate     DATETIME2 NOT NULL DEFAULT(SYSDATETIME()) );  INSERT INTO dbo.Account (     AccountNumber,     CustomerName,     AccountType,     Balance,     MobileNumber,     EmailAddress ) VALUES ('SB100001','Amit Sharma','Savings',25000.00,'9876543210','amit@gmail.com'), ('SB100002','Priya Singh','Savings',50000.00,'9876543211','priya@gmail.com'), ('CA100001','Rahul Verma','Current',125000.00,'9876543212','rahul@gmail.com'), ('SB100003','Neha Gupta','Savings',15000.00,'9876543213','neha@gmail.com'), ('CA100002','Vikas Kumar','Current',200000.00,'9876543214','vikas@gmail.com');

 

Now we are doing some transaction

BEGIN TRANSACTION; UPDATE Account SET Balance = Balance - 10000 WHERE AccountNumber = 'SB100001';  UPDATE Account SET Balance = Balance + 10000 WHERE AccountNumber = 'SB100002';  -- SQL Server crashes here -- COMMIT never executed

See here both record are update. If we want to see the uncommitted data, we can see it using nolock table hint.

See there our transaction is not yet committed.

Now we are going to stop the SQL server

In service see

Server is running. Now we are going to stop this service.

Now it is stopped.

See it in SQL server instance it is not running

Getting this error.

I mean my SQL server instance is closed or crashed. Still, I did not commit my transaction. Now we are going to start the server and see.

Server has restarted. Run the Query and see the result.

During the recovery it was rollbacked.

Because every change is first written to the Transaction Log.

This is called: Write Ahead Logging (WAL)

Rule: Log First à Data Later

Never: Data FirstàLog Later

Internal Architecture

Application à Transaction Manager à Log Manager àTransaction Log (.ldf) à Buffer Pool à Data File (.mdf)

Every modification generates log records.

 

When SQL Server restarts, Database Recovery Starts the bellow recovery process

Ø  Analysis

Ø  Redo

Ø  Undo

Analysis: SQL Server scans the transaction log and identifies the below

Ø  Committed Transactions

Ø  Uncommitted Transactions

Ø  Dirty Pages

Ø  Checkpoint Information

Redo Phase: Replay Committed Transactions. This ensures durability.

Undo: Rollback Uncommitted Transactions