December 24, 2008 at 11:00 am
Hi all, forgive me if this has been posted before.. I'm short on time.
I have a server which runs... like 8 instances currently
On 3 of those instances we just started recieving a checkdb error stating that
"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."
If you run a dbcc checkdb ('master') on those instances today, the same error occurs. Checkdb is successful on the other instances on the same server.
I know it's a locking error, which is double-weird because DBCC OPENTRAN shows no open transactions.
Does anyone have a notion what the root cause might be?
I have a weird feeling that it has something to do with the resource database and patching which has occurred recently.
Below is the error text:
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object "spt_monitor".
DBCC results for 'spt_values'.
There are 2346 rows in 16 pages for object "spt_values".
DBCC results for 'MSreplication_options'.
There are 3 rows in 1 pages for object "MSreplication_options".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
Msg 1204, Level 19, State 4, Server RONFDN801Q\DPTDEV04, Line 1
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 l
long-running transactions.
DBCC results for 'mssqlsystemresource'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
Craig Outcalt
December 24, 2008 at 11:08 am
I would say the root cause is shortage of memory., or an explicit limit has been specified for the number of locks that can be taken out. Each lock uses a small amount of memory, so on a busy instance the amount of memory available for lock resources can be used up. As the error says try re-running at a quiter time.
---------------------------------------------------------------------
December 24, 2008 at 12:57 pm
That's worth looking into, but the error happens /any/ time the dbcc's run and /only/ on master and /only/ on 3 out of 8 instances.
These Instances are on a server with 64 GB of ram (on IA64) and if I add up the max server memory on all the instances it comes to 30GB (so we're half built).
The 3 problem instances are all new instances within 2 or 3 weeks and each only has 1 or 2 non system databases on them (just the db's that run our maintenance jobs and monitoring tools).
Before this weekend thier DBCC's worked fine.
Maybe the OS is running out of RAM since (I think) the lock manager runs outside the "max server memory" setting. I will look at that, but I'm not convinced it's memory starvation since it's the same 3 databases on 3 instances ONLY... and we have 30GB free, in theory.
Craig Outcalt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply