SQL Server cannot obtain a LOCK resource at this time
-
For the last 2 months, I've been having trouble with locks on my old sql server. The following error ocurrs about 4-10 times per day
DESCRIPTION: Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
I'm having trouble putting my finger on it. I've done a couple of things:
1) More than once, I've create a trace file on my local machine, monitoring the events on the old server:
Lock:deadlock, Lock: deadlock chain, Lock: Timeout, and SQL:BatchCompleted.
The trace file created by those events really didn't tell me anything meaningful. I never get a full deadlock type transaction. It seems to always fix itself -- I just continue to get the e-mails that there was a problem. The trace showed me the sql statements, but there wasn't anything unusual about it that I could recognize. The statements were quite varied -- so I couldn't nail down a specific application or statement causing the problem.
The other thought I had was that my equiment is simply too old and over-run. Don't laugh-- it's Dual 450 processor with 2GB RAM. It was running W2k, upgraded from NT 4.0. It's really a secondary box so it's not used too much. The most active application on it is Epolicy Orchestrator (Mcaffee).
2) So, this weekend, I formatted the server and installed W-2003. I reinstalled SQL, restored Master DB and got it all the databases back up. It's certainly an improvement from where I was at, but when I got in this morning I noticed that I got another one of those dreaded e-mails from the system. Obviously, there is still something wrong.
Any ideas or input would be appreciated. I'm about ready to give in and call Microsoft.
-
It may be a memory issue. Do a search for "locks option" in BOL.
-
Hi Perry,
Have you seen this?
http://support.microsoft.com/default.aspx?scid=kb;en-us;323630&sd=tech
What Microsoft have to say about it..
Plus found this from similar source:
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.
Hope this helps.
Michael
-
Perry,
Did you find a resolution to this? I am periodically getting this as well with an external application and the vendor directed me to the same MS Knowledge Base Article. I am able to minimally track it. But since it is an external system I am not sure if there is something I can do.
Kindest Regards,Donald Mayer
Oswego Health -
Perry,
I have had issues with Oracle with database files being locked by different applications (mainly backupexec....) and this having a very BAD effect on Oracle. I noticed McAffey in your rundown. I am wondering if maybe McAffey isn't accessing a file that SQL server is trying to write to to check it for viruses as the file is constantly changing. Can you turn McAffee off for a small period to test this?????
Michael
-
I never did solve the problem. Last month I even got a new server that is a dual-Pentium 3.0 Gh. -- a considerable upgrade from what I had. Since we have the box on a SANS, we were able to simply unplug the old box, and plug in the new box....do some driver updates and we were up and running again. With this increased power, I expected the problem to disappear, but it did not.
I haven't looked at the MS article yet, but I will soon. This is not a high priority at the moment, so I'm not pushing the issue yet. I think that a fresh install of MS SQL will do the trick -- without the Master DB restore. The problem with this is having to fix all the DB links and restoring all the users etc. It'll be a pain in the butt to do. Before I do that, however, I'm going to make work pay for a call into MS about it. I hope to do that in the next 2-3 weeks. I'll let you know what comes of my conversation with MS.
-
We have free e-mail support with Microsoft on SQL server (due to our "Software Assurance" license agreement), so I finally set up a ticket with them.
They were able to figure out what the problem was. The locks must have been set incorrectly. I don't know how or when it was changed, but it was not set like it should have been. He had me run the following script and everything has been running great since then. This is the default configuration.
USE master
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'locks', '0'
GO
RECONFIGURE WITH OVERRIDE
GO
-
Hi all, I noticed that an issue with moving servers and not using the master database was the problem with copying the users / passwords onto the new box. Just had that problem with upgrading from sql 7 to 2000 (couldn't get the upgrade to work) and we needed the master database as we didnt' know 3 passwords... a friend sent me this link. Is an excellent stored procedure that creates an sql script that when you run it on the new server creates the user with the old password. I works from 7 to 2000 databases as well as 7 to 7 and 2000 to 2000. It is well worth the copy and paste just in case...
http://episteme.arstechnica.com/eve/ubb.x/a/tpc/f/12009443/m/804002042731
Enjoy
Michael
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply