• State Suggested Answer
  • Locked Locked
  • Replies 7 replies
  • Answers 2 answers
  • Subscribers 27 subscribers
  • Views 8344 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SQL Locks

carl.fedak
over 6 years ago

Hello,

 

We are using MSSQL 2014, and RR 6.1.1.137.

Our database server has C (170GB), D (2.2 TB), E (360GB), F (5.6TB).

Currently all databases are set to simple recovery, and our schedule is set to 2 hours.

Unfortunately, at least once a day we have a period where our application (custom built .NET web app) throws a bunch of errors saying it cannot write to the database, we are getting locks.  These are happening only at scheduled backup times, and typically when that happens we have 2-3 retries of the backups.

This is, of course, causing client impact.

Our database is not particularly busy, I feel like maybe we are just doing something wrong or Rapid Recovery would never really work for live databases.  In which case I'm unsure how they could be selling it.

One suspicion, we do have some fairly long running transactions in the application.  These are not changeable at this time.  Could this be causing issues like I've mentioned where DB locks are occurring?

 

Looking for any tips at all.

Parents
  • 0 over 6 years ago
    It is a basic function, the product is not what is failing at this point. It is Microsoft VSS (but I still dont understand why it locks)

    The error you are getting is pretty straight forward. VSS needs disk access to pause for a very short time (miliseconds) If access to the disk can not be paused in 40 seconds, VSS throws this error.

    So you can try to increase the wait time (40 seconds) or decrease the pause time (there are limits) or figure out why the disk IO can not stop. If it is that job you mentioned, try running backups when this job is not active. There maybe a lot of things to dig into for this

    But all of that being said, none of this should lock the DB since VSS does not do anything. He asks for the disk IO to pause, since it does not he never even attempts the snapshot so I would not expect any impact on the SQL files.

    Is there any mention of the issue that causes the lock in sql logs?
Reply
  • 0 over 6 years ago
    It is a basic function, the product is not what is failing at this point. It is Microsoft VSS (but I still dont understand why it locks)

    The error you are getting is pretty straight forward. VSS needs disk access to pause for a very short time (miliseconds) If access to the disk can not be paused in 40 seconds, VSS throws this error.

    So you can try to increase the wait time (40 seconds) or decrease the pause time (there are limits) or figure out why the disk IO can not stop. If it is that job you mentioned, try running backups when this job is not active. There maybe a lot of things to dig into for this

    But all of that being said, none of this should lock the DB since VSS does not do anything. He asks for the disk IO to pause, since it does not he never even attempts the snapshot so I would not expect any impact on the SQL files.

    Is there any mention of the issue that causes the lock in sql logs?
Children
No Data