locked
Does MS SQL database get locked during backup ? RRS feed

  • Question

  • 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

    Tuesday, December 14, 2010 12:19 PM

Answers

  • 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
    • Proposed as answer by onpnt Tuesday, December 14, 2010 12:26 PM
    • Marked as answer by Alex Feng (SQL) Monday, December 20, 2010 2:06 AM
    Tuesday, December 14, 2010 12:25 PM
  • 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
    Thursday, December 16, 2010 2:42 AM

All replies

  • 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
    • Proposed as answer by onpnt Tuesday, December 14, 2010 12:26 PM
    • Marked as answer by Alex Feng (SQL) Monday, December 20, 2010 2:06 AM
    Tuesday, December 14, 2010 12:25 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:39 PM
  • 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:44 PM
  • 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
    Thursday, December 16, 2010 2:42 AM
  • Thanks guys for clarifying that.

     

    • Marked as answer by Peter Nemec Thursday, December 16, 2010 5:30 PM
    • Unmarked as answer by Alex Feng (SQL) Monday, December 20, 2010 2:06 AM
    Thursday, December 16, 2010 5:30 PM