Does MS SQL database get locked during backup ?
Question
Tuesday, December 14, 2010 12:19 PM
Hello, we have a Microsoft SQL enterprise server. I've set up a maintenance job for one of our DB to occur every 6 hours. I am not wondering, whether the DB gets locked when the DB is being backed up ? May this cause timeouts for application that read from it... ? The DB is in FULL recovery mode and is only 0.5 GB big.
Thanks
Peter
All replies (5)
Tuesday, December 14, 2010 12:25 PM ✅Answered | 3 votes
Thats a Myth. Backup operations do not take locks on user objects. You need to look at other causes of timeout.
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq
Thursday, December 16, 2010 2:42 AM ✅Answered | 2 votes
This is correct – a backup will not block, however – your backup could be causing performance issues that would cause timeout errors. It all depends on how your IO system is configured, how many drives you have, where you are backing up, etc...
For example, if you are backing up to the same drive as the data file – then the system has to both read and write to the database file for user requests, and read/write the data to the backup file. This could saturate the IO subsystem and cause your users/application to timeout.
Another example would be if you are reading from a drive that doesn’t have a lot of spindles.
So, even though the backup does not block – it could be causing your timeout issues.
Jeff
"Balmukund" wrote in message news:df6e006a-2a93-4648-aaa0-fbcc1b96d761...
Correct.
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq
Tuesday, December 14, 2010 4:39 PM
Hello, thanks for your reply. So, during FULL backup of a MS SQL DB, the database is still accessible to write new stuff.
Tuesday, December 14, 2010 4:44 PM | 2 votes
Correct.Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq
Thursday, December 16, 2010 5:30 PM
Thanks guys for clarifying that.