Press "Enter" to skip to content

Category: Transactions

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

Transaction Modes in SQL Server

Published 2020-08-12 by Kevin Feasel

I have a video and blog post out:

What I want to do in today’s post is to cover the different sorts of transaction modes and get into the debate about whether you should use explicit transactions or rely on auto-committed transactions for data modification in SQL Server. This came from an interesting discussion at work, where some of the more recent database engineers were curious about our company policy around transaction modes and understanding the whys behind it. I didn’t come up with the policy, but my thinking isn’t too far off from the people who did.

But before I get too far off course, let’s briefly lay out some of the basics around transactions.

Read on for a good deal of info on the different transaction modes, including a bit on why implicit transactions (as opposed to autocommit transactions) are a bad thing in SQL Server.

Comments closed

Transaction Isolation Levels in SQL Server

Published 2020-08-12 by Kevin Feasel

Dan Jackson walks us through the different transaction isolation levels in SQL Server and what they mean for us:

We will start with a definition and then evolve it: the isolation level specifies how much one transaction must be protected from resource or data modifications made by other transactions.

Consider the case where user A is trying to read a list of products out of a table, meanwhile user B comes along and changes some of the product information in the table. As part of their same transaction, user A comes back to try and read the product table, but it has changed. Do you want user A to read the new information or not?

Isolation levels allow you to decide what would happen in scenarios like the one I’ve just described and so it should come as no surprise that they are described in terms of which concurrency side effects they allow.

Read on for a description of typically-undesirable side effects and the isolation levels which prevent them.

Comments closed