August 6, 2012 at 11:15 am
Dear All,
I'm running a Script which gets data from another Database which has data over 4000000 and I get the following error message:
Msg 1204, Level 19, State 4, Procedure "procedure name", Line 8 The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
I have tried to run the script at when there are fewer activities on the Server but I still get the same error message. Would anyone let me know what I should do to for the script to work?
Thank you in advance!
August 6, 2012 at 11:29 am
It'll really depend on what kind of locking is going on in the database.
If, for example, there's some very long process that's got a few rows locked, and you need a table lock for your process, or a schema lock, then you may not be able to get it done if you allow concurrent access to the database at all.
I usually see this kind of thing when issuing commands to alter the database, while there are users accessing it. In those cases, the only real choice is to set the database to Single User (usually with rollback immediate), during scheduled downtime, and issue the command then.
There is a lock timeout setting, details here: http://msdn.microsoft.com/en-us/library/ms189470.aspx. You might want to look at that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 1:04 pm
Msg 1204, Level 19, State 4, Procedure "procedure name", Line 8
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
August 6, 2012 at 1:06 pm
Thank you for your reply!
I set the lock time out to be set to 1800, by running the following script, but I still got the same error. I also tried to set the Database to a Single User Mode then run the script but still no luck.
SET LOCK_TIMEOUT 1800
GO
August 6, 2012 at 1:08 pm
What is the stored procedure? Maybe if you post that and some DDL for the tables it is referencing, we can help optimize it so that it does not cause this problem.
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply