0

Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this happens it is not related to the amount of traffic that our application has at that time.

We have tried to replicate the issue on our test servers by

  • increasing the activity on the database (Inserts, Selects, Updates, Deleting),
  • increasing the connections to the database simultaneously,
  • increasing latch time,
  • generating temporary locks on a particular table

But could not arrive to a conclusion were this is coming from.

Is there any type of configuration, or background running tasks of SQL Server, that may cause this (Backups, Transaction Log related stuff etc)?

Is it possible to trace locks, by a date range (For example : Taking a snapshot of the current locks present in the database for a particular time)?

1 Answer 1

0

Whilst not answering your question re. tracing locks by date range, all sorts of things can have this effect on the database.

I would first check whether the problem is environmental. Use Windows Perfmon to see what the system resources are doing at the time. For example, I have seen this type of thing occur when SQL Server checkpoint operations takes an overly long time to complete. The aforementioned issue can normally be solved by configuring a "recovery interval" of 1 minute or so.

To be sure of what's going on though, I would setup Windows Perfmon counters to monitor CPU usage, memory usage, and disk queue lengths of any disks on which your DB / Log files may be residing. In my experience (High OLTP) disk queue lengths should be no more than 2 for any disk. You can normally configure a separate server to log these counters to disk every 30 seconds or so in order for the monitoring not to adversely affect your DB server's performance. The number of concurrent connections and locks can also be monitored in this way.

5
  • After going through our logs on disks, we noticed that the database locking problem happens during a transaction log auto-growth. We noticed that during the issue, the log file grows out of proportion. Could we assume, that such growth is the culprit? Oct 22, 2012 at 12:14
  • @Steven - Definitely. Log file growth and data file growth can lock out all operations. Firstly you need to ensure that your log file and data file by default have enough free space to avoid growing needlessly. Secondly, Windows supports "instant file growth" which will erradicate the blocking - check it out here
    – ifx
    Oct 22, 2012 at 20:09
  • 1
    @ifx The transaction log has to be zero'd, instant initialization doesn't apply. Oct 22, 2012 at 20:20
  • @MarkStorey-Smith - Good point, I hadn't realised that
    – ifx
    Oct 22, 2012 at 20:32
  • Hi guys, thanks for your help. Our recovery model was set to full, so when the backups failed the auto growth went up without proportion. The auto growth in this case was the culprit (or at least the most possible cause), but we are taking special attention now, that the backups do not fail - so that the LDF if cleared and decreased in size. Oct 24, 2012 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.