LOCK ERRORS!!!
-
I am all too familiar with this error on our SQL Server 2000 Production Server:
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.
Error: 1204, Severity: 19, State: 1
We are trying to run a daily update that suddenly as of Friday... it won't run and just gives us this error non stop. Nothing has changed in the database except I used: sp_configure locks and it's still giving me the error. This is really not good! Please someone help!
!!!! -
Please check the following article from microsoft as it applies to 2005 as well.
How to resolve blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;323630&sd=tech
Hope this helps.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
I did read that article. I ran sp_configure locks, 0 and I won't know if the job fails again until this evening when they try again. So far... no errors. Good sign. This job has been running as normal up until Wednesday morning. Very odd....
-
Sorry... as far as the article, I did try those solutions yesterday and it actually made it worse...
-
Have you tried something like http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=42 or one of the dozens of other monitoring type scripts to get an idea of whats going on when this occurs?
I've gone so far as to use one of those scripts/procs in combo with osql and a loop to output activity to a file every few seconds e.g.
osql -E -Sxyz -iMonitorLoop.sql -oSQLActivity.out -w2000
MonitorLoop.sql
WHILE 1=1
BEGIN
EXEC master.dbo.sp_Activity3
WAITFOR DELAY '00:00:05'
END
GO
-
Actually I reset sp_configure locks, o and rebooted the server and so far... so good. I will read the link and keep these suggestions close. Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply