February 12, 2008 at 12:32 pm
My users complain about the below error some time of the day. This happens when they try to do any UPDATE s on the database.
Msg 1204, Level 19, State 4, Line 1
[font="Arial"]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.[/font]
Its a 2 Node Cluster on Windows 2003 Server. When I move the Cluster Resources off to the other node , it works perfectly. What could be the areas I need to explore to get this fixed.
February 12, 2008 at 12:53 pm
- can you provide some sqlserver version info ? (+ sp + cu )
- how much memory is available for sqlserver ?
- did you set a min or max memory using sp_configure ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2008 at 1:01 pm
Hi,
Its got 8 GB RAM, 8 Processors running SQL 9.00.3042.00 (SQL 2005 SP2). Cant detect any deadlocks just normal row locks. Checked locks configuration, memory config. All set to AUTO.
February 12, 2008 at 10:33 pm
Is the shared disk resource failing over properly? I would fail over to the 'bad' node and watch for disk i/o issues; see if you come across any Latch IO Waits. Before troubleshooting the database, I would make sure there aren't hardware or OS problems when the node fails over.
February 13, 2008 at 9:09 am
Hi,
Yes the disks are constantly hitting 100 mark. But then why it happens only in some databases ? I can run the UPDATE on another database on the same box without any problem. Its database specific and the worst thing is EM cannot detect any deadlocks. Its puzzling cause when I cange the resources over to the other node it starts working again. What kind of locks gets released when a node changeover takes place ?
February 13, 2008 at 10:46 am
- What's the size of the database ?
- what's the fragmentation level of the objects in the database?
- do you rebuild indexes often ?
- did you perform full database maintenance after migration to sql2005 ?
(dbreindex all objects, sp_updatestats, dbcc updateusage(0) with count_rows)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply