2

I want to freeze the I/O of a SQL Server instance like the shadow copy does.

The result should look like this:

2020-01-19 23:11:40.90 spid73      I/O is frozen on database prod. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

2020-01-19 23:11:57.89 spid73      I/O was resumed on database prod. No user action is required.

But I want to decide when the I/O is resumed. I tried it myself with a backup, but it finished too fast.

I want to debug my application during this I/O freeze.

How can I achieve this behavio(u)r?

Edit - 2020-01-27 10:53

The purpose is that the SQL Server doesn't accept new connections during this time:

Exception:
Class name:    EMSError
Message:       [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Unit name:     OLEDBAccess
complete_stranger
  • 123
  • 6

1 Answers1

7

There are two undocumented DBCC commands you can use for this, on a test system of course.

DBCC FREEZE_IO('database_name');
DBCC THAW_IO('database_name')

Check out Erik Darling's blog post here, which is where I first heard about these commands: Wait Stats When VSS Snaps Are Slow

He's also using them to diagnose what behavior is like during these snapshot-related freezes.

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124