SQL Server Database Transaction Tutorials

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

• Introduction to Transactions - a short tutorial on what transactions are, the types of transactions, the concept of the ACID test, and SQL Server transaction syntax.
• Transaction Isolation Levels - explains concurrency problems and how we solve them with transaction isolation levels. Also talks about the new levels available in SQL Server 2005.
• The sys.dm_tran_locks View Into Transactions - sample queries that show how to retrieve lock information and the queries causing the locks.
• Tuning Locks through SET Statements - how DBAs can configure SQL Server options to manage locking and blocking queries.

SQL Server Transaction Log Articles

Transactions are logged in a separate data file called a transaction log. Walk into any datacenter, pull the power cords out of a SQL Server, and when it boots back up, the data will almost always be safe. Transaction logs are the key to that safety.

• Restoring Transaction Log Backups - explains how to restore logs to a certain point in time, rollback transactions after a certain time or up to a marked transaction.
• Recovery Models - shows the difference between full, bulk logged and simple, and how they affect transaction logging.
• Log Shipping Performance Issues - targeted at SQL Server 2000 users, this article explains how to deal with large numbers of transactions, needs for minimal delays in log shipping, and what happens if the secondary server is unavailable.

SQLServerPedia Blog Entries About Transactions

These blog entries point out short tips or helpful resources on other web sites about transaction logs, log backups, and more:

• Get Exact Transaction Log Information - Kevin Kline points out a simple query to get information from the undocumented function fn_dblog.
• Troubleshooting Transaction Log Backups - Brent Ozar runs into a transaction log backup problem while performance tuning a server.