Microsoft 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
Error: 1204, Severity: 19, State: 4.
Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev the instance of the sql server database engine cannot obtain a lock centers Samples Retired content We’re sorry. The content you requested has been removed. You’ll be
Exec Sp_configure 'locks', 0
auto redirected in 1 second. MSDN Library MSDN Library MSDN Library MSDN Library Design Tools Development Tools and Languages Mobile and Embedded Development .NET Development Office the configuration option 'locks' does not exist, or it may be an advanced option. development Online Services Open Specifications 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 sql server max server memory Server 2014 SQL Server 2012 Topic Status: 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
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta
Sp_lock
Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills https://msdn.microsoft.com/en-us/library/aa337440.aspx and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top What does Error: 1204, Severity: 19, State: 4 mean? up vote 2 down vote favorite 1 I've found this http://dba.stackexchange.com/questions/126835/what-does-error-1204-severity-19-state-4-mean error in our SQL log: "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. Error: 1204, Severity: 19, State: 4." From last 5 days this exception is coming in our staging SQL Server. There is no other process running still it is publishing error every night. Select @@Version Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.3
26, 2014 by Timothy A Wiseman I was recently doing some testing that required a fairly large table. I created the test table, and set it up to insert about 30 million https://timothyawiseman.wordpress.com/2014/05/26/dealing-with-sql-server-lock-resource-errors/ 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 http://www.sqlpanda.com/2013/10/sql-server-error-1204.html the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. Naturally, having not encountered that before, I started by Googling the error and looking sql server 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 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) microsoft sql server 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 (I was doing this on my test instance of SQL which is on my personal machine) it would have also allowed other queries that were waiting for locks to be able to execute more freely, though whether that is necessarily good or not depends on the circumstances. I also got it to lock by
MSSQL DB2 Oracle PostgreSQL Hyper-V Linux Tool Box Resource Monday, October 28, 2013 0 SQL Server error 1204 When we see the error 1204 in the SQL Server error log, it indicate we run our of locks in the SQL server. The error message looks like this 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 We need to closely monitor number of the locks being used. If SQL server runs out of locks, the query would fail. Then we need to exam whether SQL server has sufficient memory to let the memory manager to manager the lock or there are query that may use excessive locks . In the later case, we may need to check exam the query plan to optimize the query whenever possible . How to check the number of locks SQL server is currently using? select request_session_id, resource_type,db_name(resource_database_id), resource_associated_entity_id, request_mode,request_type, request_status from sys.dm_tran_locks How to check the number of locks SQL server is configured? The run_value/config_value 0 means unlimited . SQL Server would dynamic decide the number of the locks can be used by user. The lock manager would not use more than 60% of available memory to the SQL server. select * from sys.configurations where name ='locks'; go sp_configure 'locks' How to check the lock memory is being used? SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Memory Manager%'; select * from sys.dm_os_memory_clerks where type like 'OBJECTSTORE_LOCK_MANAGER' DBCC MEMORYSTATUS would also show the the memory usage DBCC MEMORYSTATUS Reference http://technet.microsoft.com/en-us/library/aa337440(v=sql.105).aspx Posted by Po@sqlpanda at 11:21 PM Labels: MSSQL, TroubleShooting 0 comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Translate Search this blog Loading... About me Po@sqlpanda View my complete profile Labels ADDM (2) ADR (1) adrci (1) ASH