SQL Server - I/O is frozen on database situation

asked on June 21, 2022

We have a customer freezing the I/O on their databases every hour for a VSS backup. I read on stackoverflow that this is a perfectly safe operation (which sounds crazy to me since freeze means no movement and I/O means input & output, so in short this implies NO INPUT or OUTPUT)

LFDS, LFS, WF, etc all throw No process is on the other end of the pipe SQL connection errors both in the logs and general database errors on the front interfaces intermittently throughout the day.

Anyone else ever run into this situation? Why would the stackoverflow community agree that this is safe to do during the day?

https://dba.stackexchange.com/questions/108538/i-o-is-frozen-on-database-also-i-o-was-resumed-on-database-on-daily-basis-in-l

0 0

replied on June 21, 2022

I'm sure they mean "safe" in the sense that the client apps will detect the condition and reconnect/retry. Which they do based on your error message.

The UI freezing is also not unexpected. If the server tries to retrieve info from SQL and it has to wait, then it can't update the UI.

1 0

View 1 previous reply

replied on June 21, 2022

Ok, yea it doesn't seem safe to freeze I/O at any time on a production server. My experience with SQL is that as long as you are not backing up the raw MDF files, there is no need to freeze I/O or have any downtime during your backup runs. We have customers backing up all throughout the day without any interuption to service.

0 0

replied on June 21, 2022

The link you posted above seems to indicate that it's only an issue for backups done through Volume Shadow Service. Maybe your other customers have different means of backing up the database?

0 0

replied on June 21, 2022

To add to what Miruna said earlier, "safe" in a SQL database context usually means that something doesn't violate ACID guarantees for transactions, not that you won't see errors/warnings from transactions/connections failing for whatever reason.

0 0

replied on June 21, 2022

So it is safe for the backup system, but not the services that depend on the data. Yes it seems to be a VSS thing.

0 0

Question

Question

SQL Server - I/O is frozen on database situation

Replies

Sign in to reply to this post.