• State Suggested Answer
  • Locked Locked
  • Replies 7 replies
  • Answers 2 answers
  • Subscribers 27 subscribers
  • Views 8315 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
    In the event viewer on the SQL machine, we get an event 3197 (I/O is frozen on database XXXXXXXX. No user action is required." for each database, then 2-3 seconds later 3198 saying the I/O was resumed.

    In the successful cases we get 18264 ("Database backed up. Database: XXXXXXX...") messages.

    In the unsuccessful cases we get VSS event 12340 ("Volume Shadow Copy Error: VSS waited more than 40 seconds for all volumes to be flushed. "). The process will retry it seems like 3 times before a full failure.

    For additional context, it is a physical machine, and this issue is ongoing despite reboots etc

    I dont really know much about VSS, but I agree with the spirit Emte that it would seem like this should be a basic function of the rapid recovery appliance.
Reply
  • 0 over 6 years ago
    In the event viewer on the SQL machine, we get an event 3197 (I/O is frozen on database XXXXXXXX. No user action is required." for each database, then 2-3 seconds later 3198 saying the I/O was resumed.

    In the successful cases we get 18264 ("Database backed up. Database: XXXXXXX...") messages.

    In the unsuccessful cases we get VSS event 12340 ("Volume Shadow Copy Error: VSS waited more than 40 seconds for all volumes to be flushed. "). The process will retry it seems like 3 times before a full failure.

    For additional context, it is a physical machine, and this issue is ongoing despite reboots etc

    I dont really know much about VSS, but I agree with the spirit Emte that it would seem like this should be a basic function of the rapid recovery appliance.
Children
No Data