Transacton log backup

Question

What happen with the active transaction log when I stop SQL Server? Under the stop process all transactions will write to the db? Or will put when the db will starting...

Is it possible when I stop the SQL Server that not all transactions will be written into the db? Uncommitted transaction will stay in the transaction log and / or transaction log backup only?

Answer 1

If you assume the following:

• 1 - Clients are actively using the SQL Server, sending inserts, updates and deletes.
• 2 - Power loss causes the SQL Server to crash.

On startup, the server will do a brief recovery check on all of the databases and will rollback any transactions that were "in-flight" at that time. Note that any committed transactions will be preserved, only the ones where SQL Server had not done an acknowledge back to the client.

Caveats:

• If you have delayed durability turned on, then you may lose more than that.
• If you suffer disk/file corruption as a result of the power loss / server interruption then that will also hurt you here.

When the database comes up, it will be in a transactionally consistent state, depending on the size of the transaction that was in flight, the server may have to roll back quite a bit of data, but it will be transactionally consistent.

Answer 2

if transactions are running and service restart/SHUTDOWN option is used, shuts down SQL Server by:

1. Disabling logins (except for members of the sysadmin and serveradmin fixed server roles).
2. Waiting for currently running Transact-SQL statements or stored procedures to finish. To display a list of all active processes and locks, run sp_who and sp_lock, respectively.
3. Inserting a checkpoint in every database.
4. Using the SHUTDOWN statement minimizes the amount of automatic recovery work needed when members of the sysadmin fixed server role restart SQL Server.

SQL server maintains DB consistency and shuts down SQL server safely. In this case recovery time will be less.

But when you issue SHUTDOWN WITHNOWAIT then SQL server immediately stop the SQL service and shut down the system without performing checkpoints in every database and without attempting to terminate all user processes.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/shutdown-transact-sql?view=sql-server-2017

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services?view=sql-server-2017