Press "Enter" to skip to content

Category: Transactions

Why Use XACT_ABORT?

Published 2025-04-14 by Kevin Feasel

Josephine Bush answers a team member’s question:

This came up one day at my work when a developer was using it. I hadn’t used it before and thought I’d better check it out. It’s off by default in SQL server, but why would you use it?

Click through for the tl;dr version, followed by a more thorough explanation. I wouldn’t set XACT_ABORT on by default, but there have been plenty of instances in which I’ve wanted to handle a lengthier series of operations as an all-or-nothing job, and this is a great way to do it.

Leave a Comment

Comparing Oracle and PostgreSQL Transaction Systems

Published 2025-02-25 by Kevin Feasel

Laurenz Albe performs a comparison:

The transaction system is a core component of a relational database. It provides services that facilitate developing applications that guarantee data integrity. The SQL standard regulates some of the features of database transactions, but leaves many details unspecified. As a consequence, the transaction systems of relational databases can differ substantially. These days, many people are trying to get away from Oracle database and move to PostgreSQL. To migrate an application from Oracle to PostgreSQL, it is crucial to understand the differences between the respective transaction systems. Otherwise, you can end up with nasty surprises that may jeopardize performance and data integrity. So I thought it would be useful to compile a comparison of the features of transactions in Oracle and PostgreSQL.

Read on for that dive into how the two products compare.

Comments closed

Comparing Transaction State and Transaction Count Functions

Published 2024-08-15 by Kevin Feasel

Sergey Gigoyan talks transactions:

When working with transactions, identifying active transactions often becomes quite important. In SQL Server, there are XACT_STATE and @@TRANCOUNT system functions that allow one to get information about active user transactions. However, the question about the differences between these functions is one of the most common questions among database development. Understanding the details of each function and their differences will help developers make the correct choice between these functions to solve a specific problem related to transactions.

Read on to learn more about each.

Comments closed

Switching All SQL Server Databases to Simple Recovery Model

Published 2024-04-29 by Kevin Feasel

Vlad Drumea doesn’t need no steenkin’ transaction log backups:

This brief post contains a script that can help switch a whole SQL Server instance, model and all user databases, to SIMPLE recovery.

The script is useful in case of dev/test/QA/UAT instances that have been left by accident to use the default FULL recovery model, yet do not have or need transaction log backups.

Read on for the script. It also shrinks the transaction log file after the switch-over.

Comments closed

Setting a Fixed Size for Transaction Log VLFs

Published 2023-11-21 by Kevin Feasel

Paul White was assured that there would be no math:

This is all very familiar, of course, but it is also dumb. Why on earth should we have to worry about internal formulas? It seems ridiculous to have to provision or grow a transaction log in pieces just to get a reasonable VLF outcome.

Wouldn’t it be better to be able to specify a fixed size for VLFs instead?

Starting with SQL Server 2022, there is now a way though it is undocumented and unsupported for the time being at least.

Read on to see what that option is, how it works, and what the limitations are. Looking at the side effects, I’d say this is probably not something you want to jump on right now.

Comments closed

Tracking Implicit Transactions with sp_whoisactive

Published 2022-09-21 by Kevin Feasel

Erik Darling is on the hunt:

But anyway, you should avoid implicit transactions as much as possible. They most often show up in the Microsoft JDBC driver queries that only people who hate you use.

When I first started to realize how bad they are, I wrote a bunch of checks into the Blitz scripts (I’ll cover those next week) that check for them.

I also opened an issue to add the check to sp_WhoIsActive, because it’s rather popular, I hear.

Click through to see how you can track them now. Also, Erik has been showcasing community procedures like sp_WhoIsActive, sp_PressureDetector, sp_QuickieStore, and sp_HumanEvents all month and he’s doing great work there.

Comments closed

Distributed Transactions in T-SQL

Published 2022-05-31 by Kevin Feasel

Kevin Wilkie explains what distributed transactions are and why you probably don’t want to use them:

In the version of transactions that we going to discuss today, we’re going to discuss doing transactions on multiple servers!

A Distributed transaction is defined by HazelSet to be “a set of operations on data that is performed across two or more data repositories”. In even simpler terms, it’s a command run against data on more than one server.

Click through for the warnings about what might possibly go wrong.

Comments closed

Savepoints in Transactions

Published 2022-05-23 by Kevin Feasel

Kevin Wilkie continues a series on transactions in SQL Server:

All right, now that everyone’s back with us, we’ll talk more about everyone’s favorite – transactions. When they deal with transactions, most people only know how to begin one, then either commit it or roll it back. But there’s so much more you can do with a transaction!

This time I want to focus on savepoints for transactions. Yes, the same term you’ve been using in games for years can be used in the workplace!

I think I have actually made use of savepoints in production code…maybe twice? It always seems like whenever I might actually make use of one (rather than simply rolling it all back and starting over) that there’s some limitation which makes them not useful.

Comments closed