Ms Sql Server Error 1204
Contents |
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums the instance of the sql server database engine cannot obtain a lock resource at this time Blogs Channel 9 Documentation APIs and reference Dev centers Samples error: 1204, severity: 19, state: 4. Retired content We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 exec sp_configure 'locks', 0 second. MSDN Library MSDN Library MSDN Library MSDN Library Design Tools Development Tools and Languages Mobile and Embedded Development .NET Development Office development Online Services Open Specifications
The Configuration Option 'locks' Does Not Exist, Or It May Be An Advanced Option.
patterns & practices Servers and Enterprise Development Speech Technologies Web Development Windows Desktop App Development TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is not being maintained. MSSQLSERVER_1204 Other Versions SQL Server 2014 SQL Server 2012 Topic Status: sql server max server memory Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2). Details Product Name SQL Server Event ID 1204 Event Source MSSQLSERVER Component SQLEngine Symbolic Name LK_OUTOF Message Text 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. Explanation SQL Server cannot obtain a lock resource. This can be caused by either of the following reasons: SQL Server cannot allocate more memory from the operating system, either because other processes are using it, or because the server is operating with the max server memory option configured. The lock manager will not use more than 60 percent of the memory available to SQL Ser
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » Notification Services » Administration » SQL Server cannot obtain a LOCK resource at... SQL Server cannot obtain a LOCK sp_lock resource at this time Rate Topic Display Mode Topic Options Author Message Perry CitrowskePerry Citrowske Posted Monday, October 11, 2004 1:39 PM Valued Member Group: General Forum Members Last Login: Wednesday, June 17, 2009 1:38 PM Points: 63, Visits: 33 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 dayDESCRIPTION: Error: 1204, Severity: 19, State: 1The SQL Server cannot obtain a LOCK resource at https://msdn.microsoft.com/en-us/library/aa337440.aspx 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 http://www.sqlservercentral.com/Forums/Topic141154-92-1.aspx 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 serverand 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. Post #141154 Don MillhouseDon Millhouse Posted Monday, October 11, 2004 2:34 PM Old Hand Group: General Forum Members Last Login: Sunday, October 16, 2016 8:52 AM Points: 320, Visits: 29 It may be a memory issue. Do a search for "locks option" in BOL. Post #141162 Michael GirdlerMichael Girdler
26, 2014 by Timothy A Wiseman I was recently doing some testing that required a fairly large table. I created the test table, https://timothyawiseman.wordpress.com/2014/05/26/dealing-with-sql-server-lock-resource-errors/ and set it up to insert about 30 million rows. I got an error that read: 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 sql server check for long-running transactions. Naturally, having not encountered that before, I started by Googling the error and looking up Error 1204 and Microsoft's Support Article. The support article helpfully suggested setting locks to 0 using sp_configure. But that is the default value and mine was already set that way. So, I created a simplified setup that would ms sql server reliably produce the error based on Jeff Moden's Tally Table Article. if OBJECT_ID('BigTallyTable', 'U') is not NULL drop table dbo.BigTallyTable create table BigTallyTable (n BigInt primary key) insert into dbo.BigTallyTable(n) select top (30000000) row_number() over (order by s1.name) as n from master.dbo.syscolumns s1, master.dbo.syscolumns s2 And I started playing with it. Since this all tied to locks, one thing I did was break it up into batches: declare @maxValue int, @maxperround int, @last int set @maxValue = 30000000 --30 Million set @maxPerRound = 500000 set @last = 0 while @last < @maxValue begin if @maxPerRound > @maxValue - @last set @maxPerRound = @maxValue - @last insert into dbo.BigTallyTable(n) select top (@maxPerRound) row_number() over (order by s1.name) + @last as n from master.dbo.syscolumns s1, master.dbo.syscolumns s2 Print 'Finished a round with @last = ' + cast(@last as varchar) + ' and @maxPerround = ' + cast(@maxPerRound as varchar) set @last = @last + @maxPerRound end This avoided the error and generated the results I was looking for. On a multi-user system