Understanding I/O Freeze in SQL Server 2022's TSQL Snapshot Backups

SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL Snapshot backups enables instantaneous restores, independent of the size of data, for a database, group, or server backups, including point-in-time recovery.

When you use this feature, it freezes I/O. You’ll see a record like this in your error log when you execute the command ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This blog post will show you that the I/O freeze is just for write operations. You can continue to read from the database during this frozen state.

Getting things started

To get things started, let’s start a docker container running the latest version of SQL Server 2022.

Note: I will demo this in a docker container. This process works just the same on Windows SQL Servers.

docker run `
    --env 'ACCEPT_EULA=Y' `
    --env 'MSSQL_SA_PASSWORD=S0methingS@Str0ng!' `
    --name 'sql1' `
    --publish 1433:1433 `
    --detach mcr.microsoft.com/mssql/server:2022-latest 

Next, we’ll want to set up our environment. I’m using dbatools to coordinate this process. You can run the SQL directly in SSMS. In the code below, I’m initializing some variables for reuse in the script. I’m using the cmdlet Connect-DbaInstance to build a persistent database connection. This connection is reused throughout this process and will have the same SPID inside SQL Server. This is crucial for snapshot backups to work when running in a single script like this. Some tools, such as Invoke-SqlCmd connect to the instance and execute their query, then disconnect. If I used Invoke-SqlCmd to freeze a database, group, or server when the query completes, the cmdlet would close the connection, and the database, group, or server would immediately thaw. Then, when I try to run the subsequent commands to take the snapshot backup, they will fail since the database is thawed. Using the persistent connection of Connect-DbaInstance solves this problem. I can connect and execute the freeze, return control to the script, and execute the next steps in the snapshot backup process.

$SqlUser = 'sa'
$SqlPassword = ConvertTo-SecureString 'S0methingS@Str0ng!' -AsPlainText -Force
$SqlCredential = New-Object System.Management.Automation.PSCredential ($SqlUser, $SqlPassword)
$SqlInstance = Connect-DbaInstance -SqlInstance localhost -TrustServerCertificate -NonPooledConnection -SqlCredential $SqlCredential

Creating our database

With our connection built, let’s create a test database and table and insert some rows.

#Create a database
$Query = 'CREATE DATABASE TestDB1'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query

#Create a table
$Query = 'CREATE TABLE t1 (c1 int);'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Database 'TestDB1'

#Insert three rows. No more, no less. Three.
$Query = 'INSERT INTO t1 VALUES (1);
          INSERT INTO t1 VALUES (2);
          INSERT INTO t1 VALUES (3);'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Database 'TestDB1'

Freezing the database

Now, let’s freeze the database to suspend I/O…but does this suspend all I/O, read and write? We’ll dig deeper into that in a second.

$Query = 'ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

VERBOSE: Database 'TestDB1' acquired suspend locks in session 59.
VERBOSE: I/O is frozen on database TestDB1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
VERBOSE: Database 'TestDB1' successfully suspended for snapshot backup in session 59.

Note: At this point we would also take a storage-based snapshot. In this blog I’m focusing on the I/O freeze. I’ll cover the storage-based snapshots in another blog post.

With the database frozen, let’s be sure none of the database pages for our database are in memory, and we can use DBCC DROPCLEANBUFFERS to help with that.

$Query = 'DBCC DROPCLEANBUFFERS'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
VERBOSE: DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Accessing our data while the database is ‘frozen’

Now, we have a frozen database and no pages in memory. We can then attempt to read from the database, and as you can see in the output below, we can read data from our newly created table. So even though the database is frozen…its accessible for read I/O. We’ll look at write I/O a little bit later on in this post.

$Query = 'SELECT * FROM t1'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Database 'TestDB1'

c1
--
 1
 2
 3

Checking the status of our frozen database

Is the database actually frozen? Let’s check the status. As indicated in the output below, you can see that our TestDB1 and the field is_write_io_frozen : True tells us this database is frozen for write I/O. Ah, there’s a hint :)

$Query = 'SELECT * FROM sys.dm_server_suspend_status'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query

db_id               : 5
db_name             : TestDB1
suspend_session_id  : 59
suspend_time_ms     : 80545
is_diff_map_cleared : True
is_write_io_frozen  : True

Trying to insert data into our frozen database

So we know that we can read from our frozen database. But what about writes? When we try to run the INSERT query below, it gets blocked when the database is frozen. And if you look at the process status with sp_who2, you’ll find this process is SUSPENDED.

$Query = 'INSERT INTO t1 VALUES (4);
          INSERT INTO t1 VALUES (5);
          INSERT INTO t1 VALUES (6);'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose -Database 'TestDB1' 

While the query above is SUSPENDED, if you open another window and try to read from the same table, this additional query will get blocked too. But this is because the INSERT above has a lock on the table. Now this is just a function of the datbase since our INSERT query has a lock on the table. Other read workload in the database will proceed as normal.

$Query = 'SELECT * FROM t1'
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Database 'TestDB1'

Taking the BACKUP WITH METADATA_ONLY backup

Now, let’s take a snapshot backup of the database. Once this finishes successfully, the database is then thawed, the INSERT above will succeed, and the blocked SELECT statement will finish. To get everything moving, open another PowerShell window, create a new connection, and take your snapshot backup with "BACKUP DATABASE TestDb1 TO DISK='$BackupFile' WITH METADATA_ONLY".

$SqlUser = 'sa'
$SqlPassword = ConvertTo-SecureString 'S0methingS@Str0ng!' -AsPlainText -Force
$SqlCredential = New-Object System.Management.Automation.PSCredential ($SqlUser, $SqlPassword)
$SqlInstance = Connect-DbaInstance -SqlInstance localhost -TrustServerCertificate -NonPooledConnection -SqlCredential $SqlCredential

$BackupFile = "TestDB1_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE TestDb1 TO DISK='$BackupFile' WITH METADATA_ONLY"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

VERBOSE: I/O was resumed on database TestDB1. No user action is required.
VERBOSE: Database 'TestDB1' released suspend locks in session 59.
VERBOSE: Database 'TestDB1' originally suspended for snapshot backup in session 59 successfully resumed in session 73.
VERBOSE: Processed 0 pages for database 'TestDB1', file 'TestDB1' on file 1.
VERBOSE: BACKUP DATABASE successfully processed 0 pages in 0.013 seconds (0.000 MB/sec).

Wrapping things up

To bring things to a close, understanding the intricacies of I/O freeze in SQL Server 2022’s TSQL Snapshot Backups is essential to the successful use of this new feature. As demonstrated in this post, the I/O freeze, triggered by the ALTER DATABASE SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON command, specifically targets write operations, allowing read operations to continue. Further, to use the benefits of TSQL Snapshot Backups, you must maintain a persistent connection with tools like Connect-DbaInstance to hold the database frozen to perform subsequent operations in your scripts. This new feature enables instantaneous restores and point-in-time recovery for database backups on systems running SQL Server 2022.

I want to thank Bob Ward for help with this blog post. This blog is inspired by questions we received during our session Are Snapshots Backups? which we co-presented this year at PASS Summit in Seattle.