Error 1204 every 32 – 48 hours | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Error 1204 every 32 – 48 hours

Hi, I’m having a weird problem on one of my sql servers…
every 32 – 48 hours, the database becomes real slow, and the in the event viewer, the error 1204, severity 19 state 1 appears,
"The sql server cannot obtain a LOCK resource at this time…" To temporarily resolve the problem, i reindexed one of the heavily used tables. But i expect this problem to occur again within 48 hours. The db was running along until about last week. there were no changes made.
SQL Server 2000 Ent with sp3a, 6.5GB RAM. What is going on here? Help!! Raymond
It’s caused by lock escalation. Take a look at this: http://support.microsoft.com/default.aspx?scid=kb;en-us;323630&sd=tech I’ve seen it a couple of times from exceeding large queries – most recently a job a that ran a large query that returned approx 5 million rows to a temporary table. The problem was caused by a bad WHERE clause that used a UDF on a column. Once the UDF was removed the query ran without the error (and much faster). If you think you have one query that may be causing this look at the query and read up on SARG.
Also run Profiler to find slow queries and try to optimize it.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Are these the only reasons that’s causing the problem? Raymond
Refer to the KBA listed by Simon to resolve the issue.
If possible execute Update STATS manually on the tables that are frequently updated. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Like the rest of them are suggesting, look at Lock Escalation closely.
One tip on how/if lock escalation occurred in your SQL Server is to monitor for the "Lock Escalation" event class in the "Locks" event category using SQL Profiler and monitor for that event. It may also be a bug in SQL Server. Some of them were fixed by SP3a, but there is one more bug that is fixed in a hotfix post SP3a. Please look at the KB –http://support.microsoft.com/?id=837957 for more information. This QFE is going to be applicable if you are also having any other memory errors accompanying this error. Thanks,
— Sri Thanks,
— Sri
Ok, i’m monitoring for lock escalation, and i’m seeing a lot of them. But the only data i’m capturing from the profiler is the login name and time.<br /><br />What do i do next? Help!! <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />Raymond
When i do an sp_lock, sometimes it’s 200++ plus locks, and at times it shoots up to 50k, or even 100k! Is this normal? Raymond
If deadlocks become a common occurrence to the point that their rollbacks are causing excessive performance degradation, you may need to perform more in-depth investigation. Use trace flag 1204. And check the waittype, waittime, lastwaittype, and the waitresource columns of sysprocesses to see what activities each SPID is performing. Deadlocking can also occur when using distributed transactions. For information about resolving deadlocks with distributed transactions, see SQL Server Books Online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I’ve found out why there are suddenly so many lock escalations!
The existing clustered index was changed to a non-clustered one after some changes were made to the metadata by a colleague of mine. Thanks for the helpful suggestions! Raymond
]]>