Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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)?

share|improve this question

migrated from stackoverflow.com Oct 22 '12 at 11:33

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
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? – Stephen Borg Oct 22 '12 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 '12 at 20:09
1  
@ifx The transaction log has to be zero'd, instant initialization doesn't apply. – Mark Storey-Smith Oct 22 '12 at 20:20
@MarkStorey-Smith - Good point, I hadn't realised that – ifx Oct 22 '12 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. – Stephen Borg Oct 24 '12 at 7:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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