locked
While truncation of data from one of production table other can access the same table or not? RRS feed

  • Question

  • one of production database configured mirroring(2016 SQL Server) i want to delete data batch wise. while am deleting other can access this table or not? and what could be the log file grow? 
    Friday, June 29, 2018 7:51 AM

Answers

  • Dear Kittu4DBA,

    When we delete some data, this resource will be locked, but this does not mean that the whole table is locked, SQL Server will determine the most efficient locking method for the job, other people can still access this table.

    Deleting operation will be recorded in the transaction log, it will make the log file growing.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Kittu4DBA Friday, June 29, 2018 12:25 PM
    Friday, June 29, 2018 11:45 AM
  • one of production database configured mirroring(2016 SQL Server) i want to delete data batch wise. while am deleting other can access this table or not? and what could be the log file grow? 

    Just make sure whatever lock is coming it is not the table lock, I mean the table from which record is getting deleted should not be exclusively locked and this can happen when you try to delete too many records form the table. I guess you are already deleting in batches so things should be fine. In batch delete where lock is not put on complete table only the rows which qualify for delete would be locked for delete.

    The log file will grow as each delete record would be locked make sure your batch size is not too big to fill complete transaction log. If DB is in full recovery make sure you take frequent trn log backups. Also keep close watch on synchronization


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Kittu4DBA Friday, June 29, 2018 12:25 PM
    Friday, June 29, 2018 12:16 PM

All replies

  • Dear Kittu4DBA,

    When we delete some data, this resource will be locked, but this does not mean that the whole table is locked, SQL Server will determine the most efficient locking method for the job, other people can still access this table.

    Deleting operation will be recorded in the transaction log, it will make the log file growing.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Kittu4DBA Friday, June 29, 2018 12:25 PM
    Friday, June 29, 2018 11:45 AM
  • one of production database configured mirroring(2016 SQL Server) i want to delete data batch wise. while am deleting other can access this table or not? and what could be the log file grow? 

    Just make sure whatever lock is coming it is not the table lock, I mean the table from which record is getting deleted should not be exclusively locked and this can happen when you try to delete too many records form the table. I guess you are already deleting in batches so things should be fine. In batch delete where lock is not put on complete table only the rows which qualify for delete would be locked for delete.

    The log file will grow as each delete record would be locked make sure your batch size is not too big to fill complete transaction log. If DB is in full recovery make sure you take frequent trn log backups. Also keep close watch on synchronization


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Kittu4DBA Friday, June 29, 2018 12:25 PM
    Friday, June 29, 2018 12:16 PM