I had a customer call in this week after struggling to resolve an issue for a couple of days, Their Transaction Log file had grown to 400+GB and was filling the drive to the point that had to keep adding space to the drive to keep operations online. This was for a core internal system. There are no staff DBAs here, but plenty of very sharp sysadmins, developers, network folks, etc.
Note: I have written about the most common cause of this issue here, but this was not the resolution in this case. The embedded video is far and away number one in hits of all my SQL videos, showing just how common log file growth issues are.
I like the way Kevin does this. He frames the story, takes you through his actions, and gives you a chance to understand his troubleshooting process. Most problems become an order of magnitude easier to solve if you have a reasoned-through process (and enough practice to follow when three levels of management are staring at your screen during a crisis).
With SQL Server 2017, a new era was heralded with SQL server being available to deploy on Linux (and Linux based container) systems. While all functionality of the SQL Server engine were brought over as is to SQL Server on Linux, some of the functionality which depended on Windows system processes such as distributed transactions (which relies on MSDTC service) were not brought over immediately.
Well, now your wait is over.
Of course, no one reads the documentation. And even if they do, they certainly don’t continue on to the 4th paragraph of the “General Remarks” section. That’s like going to the second page of Google search results.
If one did trudge on through the docs, they would find this gem:
Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.
Well that’s interesting! Let’s check it out.
Four paragraphs? Where’s the tl;dr, Microsoft Docs?
Read the whole thing even though Josh just made a joke about nobody reading the whole thing and I followed along with it.
READ COMMITTED: One step up (and the default for SQL Server). A query in the current transaction can’t read data modified by someone else that hasn’t yet committed. No dirty reads. BUT….data could be changed by others between statements in the current transaction, so the data may not look the same twice. READ COMMITTED uses shared locks to prevent dirty reads, but that’s about all you get. You still get non-repeatable reads and phantom reads here (more on phantom reads below).
Click through for the full list.
At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes:
- 0 = the VLF is not active (i.e. it can be (re)activated and overwritten)
- (1 = not used and no-one seems to remember what it used to mean)
- 2 = the VLF is active because at least one log record in it is ‘required’ by SQL Server for some reason (e.g. hasn’t been backed up by a log backup or scanned by replication)
A few weeks ago I learned about a new VLF status code that was added back in SQL Server 2012 but hasn’t come to light until recently (at least I’ve never encountered it in the wild). I went back-and-forth with a friend from Microsoft (Sean Gallardy, a PFE and MCM down in Tampa) who was able to dig around in the code to figure out when it’s used.
Read on to uncover the mysteries of the VLF status of 4.
I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs.
12 CREATE TABLE dbo.demoNOLOCK (someguid uniqueidentifier NOT NULL PRIMARY KEY);INSERT dbo.demoNOLOCK (someguid) SELECT TOP (1000000) NEWID() FROM sys.all_columns t1, sys.all_columns t2;
Next I prove that there a million rows.
1 SELECT COUNT(*) FROM dbo.demoNOLOCK;
Now without inserting or deleting any rows, I’m going to shuffle them.
1 UPDATE dbo.demoNOLOCK SET someguid = NEWID();
And if while this is happening, I count the rows in a different session, I have to wait for that query to finish.
Read on to see what happens when someone gets the idea of running the select query with NOLOCK.
You can use Distributed transactions which specifies the start of a SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC). This is only applicable to SQL Server and no Azure. Transaction-level snapshot isolation does not support distributed transactions.
Is a user-defined transaction name used to track the distributed transaction within MS DTC utilities. transaction_name must conform to the rules for identifiers and must be <= 32 characters.
Is the name of a user-defined variable containing a transaction name used to track the distributed transaction within MS DTC utilities. The variable must be declared with a char, varchar, nchar, or nvarchar data type.
Jeanne also includes the important comment that nested transactions don’t really work the way you’d expect them to. I probably could have ended that last sentence early with, “don’t really work.”
If you do not enable implicit transactions, and you don’t start an explicit transaction, you are in the default “autocommit” mode.
This mode means that individual statements are automatically committed or rolled back as whole units. You can’t end up in a place where only half your statement is committed.
Our question is really about whether there are unseen problems with this default mode of autocommit for single-statement units of work.
By force of habit, I wrap data modification operations in an explicit transaction. They let me test my changes before committing and the time you’re most likely to spot an error seems to be right after hitting F5.
With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction.
I will show you what this looks like with an example. Please note the code will not work for you because I dropped the foreign key constraint within the person.person table just so I could show you the example.
Arun mentions that the serializable isolation level prevents phantom reads. So do the repeatable read and snapshot isolation levels.
The statement “There is zero logging when DURABILITY=SCHEMA_ONLY” is not factually correct, its more like a minimally logged operation. What is surprising is the fact that logged as advertised for the in-memory engine should result in far fewer log records than the equivalent workload for the legacy engine, clearly this is not the case in this particular example and something I need to dig into somewhat deeper. Also note that the version of SQL Server being used is SQL Server 2016 SP1 CU3, which should be stable. One final point, in order to make sure that fn_dblog and fn_dblog_xtp produced clean results for me each time, I took the quick and dirty option of re-creating my test database each time.
This post definitely ranks in the “Microsoft did this right” category.