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
  • May I know the purpose ?, the request seems strange please note if you do so you would see users complaining. – Shanky Jan 27 at 9:48
  • @Shanky I clarified my intention. – complete_stranger Jan 27 at 9:56
  • The error message is different and needs to be fixed, when are you getting this message – Shanky Jan 27 at 10:07
  • The error message is a result of the I/O freeze. And that's what I want to fix. I don't know how you do things, but I want to get as close to reality as possible. Sure, I could close the port in the firewall etc. but I want to recreate this scenario. – complete_stranger Jan 27 at 10:13
  • I would normally vote to close this question as off-topic, as it is more related to programming and would be better suited for stackoverflow.com. There an answer to the question Compress-Archive Error: Cannot access the file because it is being used by another process was posted as: A good method to access files being used by another process is by creating snapshots using Volume Shadow Copy Service. and contains an example using PowerShell. – John aka hot2use Jan 27 at 10:53
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.