Error: 1204 Severity: 19 State: 1

I am relatively new to SQL and I need help please!
On a 4GB table, with the largest table having 9 million
rows and recently started getting Error 1204 Severity 19
State 1,
I know it indicates a nonconfigurable resource limit has
been exceeded, but exactly what does nonconfigurable
resource mean?
What is happening on the server is blocking, not
deadlocking, but I'm concerned how do I fix this before it
gets worse. I run DBCC Checkdb every night and have
defragged the indexes on the larger tables. Once a week
I've been running DBCC reindex as well.
Increase the amount of locks? The min set now is 5000 and
the max is 214748367.
Thanks in advance for help,
Josephine.Go thru this KBA
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D323630
--
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
> I am relatively new to SQL and I need help please!
> On a 4GB table, with the largest table having 9 million
> rows and recently started getting Error 1204 Severity 19
> State 1,
> I know it indicates a nonconfigurable resource limit has
> been exceeded, but exactly what does nonconfigurable
> resource mean?
> What is happening on the server is blocking, not
> deadlocking, but I'm concerned how do I fix this before it
> gets worse. I run DBCC Checkdb every night and have
> defragged the indexes on the larger tables. Once a week
> I've been running DBCC reindex as well.
> Increase the amount of locks? The min set now is 5000 and
> the max is 214748367.
> Thanks in advance for help,
> Josephine.|||Look at here:
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D323630
--
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
> I am relatively new to SQL and I need help please!
> On a 4GB table, with the largest table having 9 million
> rows and recently started getting Error 1204 Severity 19
> State 1,
> I know it indicates a nonconfigurable resource limit has
> been exceeded, but exactly what does nonconfigurable
> resource mean?
> What is happening on the server is blocking, not
> deadlocking, but I'm concerned how do I fix this before it
> gets worse. I run DBCC Checkdb every night and have
> defragged the indexes on the larger tables. Once a week
> I've been running DBCC reindex as well.
> Increase the amount of locks? The min set now is 5000 and
> the max is 214748367.
> Thanks in advance for help,
> Josephine.|||Don't look at min and max for sp_configure. Look at configured and running. For locks, it should be 0 (auto).
The updated books online has some rather details info on 1204:
Error 1204
Severity Level 19
Message Text
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.
Explanation
This error occurs when there are not enough system locks to complete the current command. SQL Server then
attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the
lock limit is determined by the available memory. When statically configured, the lock limit is determined by
the sp_configure setting.
If you continue to encounter this problem, make sure your statistics are up to date, you have sufficient
indexes to run your query efficiently, and that the transaction isolation level for your application is not
more restrictive than necessary.
Action
Either execute the command again when activity on the server is low, or have the system administrator increase
the number of locks by executing sp_configure from the master database.
To view the current configuration:
sp_configure locks
GO
This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run
sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:
sp_configure locks, 10000
GO
RECONFIGURE WITH OVERRIDE
GO
Stop and restart Microsoft® SQL ServerT so the changes can take effect. Locks are allocated at system startup.
If the number of locks cannot be increased at the current time, and the single action requires more locks than
the server is currently configured for, you may be able to reduce the number of locks required for the
operation. For example, try the following:
a.. For large UPDATE statements, break the updates into smaller units that will affect only a subset of
records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 9999
GO
to several UPDATE statements:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 4999
GO
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 5000 AND 9999
GO
a.. For a maintenance type of task or for a global update, consider putting the database into single-user mode
(if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will
not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).
b.. For a large bulk copy operation, the entire operation is treated as a single transaction. When you use
the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of
rows specified. At the end of each small transaction, the system resources held by that transaction are freed,
so fewer locks are needed.
See Also
Understanding and Avoiding Blocking
bcp Utility
BULK INSERT
Errors 1000 - 1999
Setting Configuration Options
sp_configure
Starting, Pausing, and Stopping SQL Server
UPDATE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
> I am relatively new to SQL and I need help please!
> On a 4GB table, with the largest table having 9 million
> rows and recently started getting Error 1204 Severity 19
> State 1,
> I know it indicates a nonconfigurable resource limit has
> been exceeded, but exactly what does nonconfigurable
> resource mean?
> What is happening on the server is blocking, not
> deadlocking, but I'm concerned how do I fix this before it
> gets worse. I run DBCC Checkdb every night and have
> defragged the indexes on the larger tables. Once a week
> I've been running DBCC reindex as well.
> Increase the amount of locks? The min set now is 5000 and
> the max is 214748367.
> Thanks in advance for help,
> Josephine.|||Thank you Tibor, that was helpful. I did the sp_configure and the locks are at 0, so they are configured dynamically. The RAM available right now with no blocking is 22MB. So that may be something to look into. Transaction Isolation Level? I'm not familiar with it. Unfortunately, all this is going on through an application that our company didn't write. I have to figure out how the query is generated through the application to isolate how much data is being processed. One thing that makes me think the app is really a mess is I have in the app error logs "Insert Statement conflicted with Foreign Key Constraint". By the way, the same people who wrote the app, created the database.
Turning on Profiler has killed the server, resource wise for me to determine the root. I don't think the application accesses the database well, it is only update 100 rows an hour..but I'm not getting much help from their support department as they think this is a database issue. I also think there may be some user error going on. Like trying to process multiple times or something. I guess what I'm looking for, is assurance that I'm not missing doing something from my end. Any thoughts? I'm checking into the RAM issue for sure. Thanks, Josephine.
>--Original Message--
>Don't look at min and max for sp_configure. Look at configured and running. For locks, it should be 0 (auto).
>The updated books online has some rather details info on 1204:
>Error 1204
>Severity Level 19
>Message Text
>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.
>Explanation
>This error occurs when there are not enough system locks to complete the current command. SQL Server then
>attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the
>lock limit is determined by the available memory. When statically configured, the lock limit is determined by
>the sp_configure setting.
>If you continue to encounter this problem, make sure your statistics are up to date, you have sufficient
>indexes to run your query efficiently, and that the transaction isolation level for your application is not
>more restrictive than necessary.
>Action
>Either execute the command again when activity on the server is low, or have the system administrator increase
>the number of locks by executing sp_configure from the master database.
>To view the current configuration:
>sp_configure locks
>GO
>This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run
>sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:
>sp_configure locks, 10000
>GO
>RECONFIGURE WITH OVERRIDE
>GO
>Stop and restart Microsoft=AE SQL ServerT so the changes can take effect. Locks are allocated at system startup.
>If the number of locks cannot be increased at the current time, and the single action requires more locks than
>the server is currently configured for, you may be able to reduce the number of locks required for the
>operation. For example, try the following:
> a.. For large UPDATE statements, break the updates into smaller units that will affect only a subset of
>records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
>UPDATE employees
>SET salary =3D salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 9999
>GO
>to several UPDATE statements:
>UPDATE employees
>SET salary =3D salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 4999
>GO
>UPDATE employees
>SET salary =3D salary * 1.05
>WHERE employee_id BETWEEN 5000 AND 9999
>GO
>a.. For a maintenance type of task or for a global update, consider putting the database into single-user mode
>(if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will
>not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).
>
> b.. For a large bulk copy operation, the entire operation is treated as a single transaction. When you use
>the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of
>rows specified. At the end of each small transaction, the system resources held by that transaction are freed,
>so fewer locks are needed.
>See Also
>Understanding and Avoiding Blocking
>bcp Utility
>BULK INSERT
>Errors 1000 - 1999
>Setting Configuration Options
>sp_configure
>Starting, Pausing, and Stopping SQL Server
>UPDATE
>
>-- >Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
>news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
>> I am relatively new to SQL and I need help please!
>> On a 4GB table, with the largest table having 9 million
>> rows and recently started getting Error 1204 Severity 19
>> State 1,
>> I know it indicates a nonconfigurable resource limit has
>> been exceeded, but exactly what does nonconfigurable
>> resource mean?
>> What is happening on the server is blocking, not
>> deadlocking, but I'm concerned how do I fix this before it
>> gets worse. I run DBCC Checkdb every night and have
>> defragged the indexes on the larger tables. Once a week
>> I've been running DBCC reindex as well.
>> Increase the amount of locks? The min set now is 5000 and
>> the max is 214748367.
>> Thanks in advance for help,
>> Josephine.
>
>.
>|||Look into the RAM issue. Except for that, locking, blocking and deadlocking problems are application problems
that need to be addresses by the application developers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1c3dc01c4524a$d63e2130$a501280a@.phx.gbl...
Thank you Tibor, that was helpful.
I did the sp_configure and the locks are at 0, so they are
configured dynamically. The RAM available right now with
no blocking is 22MB. So that may be something to look
into. Transaction Isolation Level? I'm not familiar with
it. Unfortunately, all this is going on through an
application that our company didn't write. I have to
figure out how the query is generated through the
application to isolate how much data is being processed.
One thing that makes me think the app is really a mess is
I have in the app error logs "Insert Statement conflicted
with Foreign Key Constraint". By the way, the same people
who wrote the app, created the database.
Turning on Profiler has killed the server, resource wise
for me to determine the root.
I don't think the application accesses the database well,
it is only update 100 rows an hour..but I'm not getting
much help from their support department as they think this
is a database issue. I also think there may be some user
error going on. Like trying to process multiple times or
something.
I guess what I'm looking for, is assurance that I'm not
missing doing something from my end. Any thoughts?
I'm checking into the RAM issue for sure.
Thanks,
Josephine.
>--Original Message--
>Don't look at min and max for sp_configure. Look at
configured and running. For locks, it should be 0 (auto).
>The updated books online has some rather details info on
1204:
>Error 1204
>Severity Level 19
>Message Text
>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.
>Explanation
>This error occurs when there are not enough system locks
to complete the current command. SQL Server then
>attempts to obtain a LOCK block to represent and control
the desired lock. When dynamically configured, the
>lock limit is determined by the available memory. When
statically configured, the lock limit is determined by
>the sp_configure setting.
>If you continue to encounter this problem, make sure your
statistics are up to date, you have sufficient
>indexes to run your query efficiently, and that the
transaction isolation level for your application is not
>more restrictive than necessary.
>Action
>Either execute the command again when activity on the
server is low, or have the system administrator increase
>the number of locks by executing sp_configure from the
master database.
>To view the current configuration:
>sp_configure locks
>GO
>This reports the minimum, maximum, current run, and
configuration values. To increase the number of locks, run
>sp_configure again, specifying the number of locks to be
configured. For example, to configure 10,000 locks:
>sp_configure locks, 10000
>GO
>RECONFIGURE WITH OVERRIDE
>GO
>Stop and restart Microsoft® SQL ServerT so the changes
can take effect. Locks are allocated at system startup.
>If the number of locks cannot be increased at the current
time, and the single action requires more locks than
>the server is currently configured for, you may be able
to reduce the number of locks required for the
>operation. For example, try the following:
> a.. For large UPDATE statements, break the updates into
smaller units that will affect only a subset of
>records at a time. For example, you could use the primary
key, changing the single UPDATE statement from:
>UPDATE employees
>SET salary = salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 9999
>GO
>to several UPDATE statements:
>UPDATE employees
>SET salary = salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 4999
>GO
>UPDATE employees
>SET salary = salary * 1.05
>WHERE employee_id BETWEEN 5000 AND 9999
>GO
>a.. For a maintenance type of task or for a global
update, consider putting the database into single-user mode
>(if it is feasible to keep other users out of the
database). Single-user mode does not set locks, so you will
>not run out of locks, and the operation will run somewhat
faster (because you save the locking overhead).
>
> b.. For a large bulk copy operation, the entire
operation is treated as a single transaction. When you use
>the batch parameter (-b), the bcp utility will treat the
operation in small transactions with the number of
>rows specified. At the end of each small transaction, the
system resources held by that transaction are freed,
>so fewer locks are needed.
>See Also
>Understanding and Avoiding Blocking
>bcp Utility
>BULK INSERT
>Errors 1000 - 1999
>Setting Configuration Options
>sp_configure
>Starting, Pausing, and Stopping SQL Server
>UPDATE
>
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Josephine" <anonymous@.discussions.microsoft.com> wrote
in message
>news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
>> I am relatively new to SQL and I need help please!
>> On a 4GB table, with the largest table having 9 million
>> rows and recently started getting Error 1204 Severity 19
>> State 1,
>> I know it indicates a nonconfigurable resource limit has
>> been exceeded, but exactly what does nonconfigurable
>> resource mean?
>> What is happening on the server is blocking, not
>> deadlocking, but I'm concerned how do I fix this before
it
>> gets worse. I run DBCC Checkdb every night and have
>> defragged the indexes on the larger tables. Once a week
>> I've been running DBCC reindex as well.
>> Increase the amount of locks? The min set now is 5000
and
>> the max is 214748367.
>> Thanks in advance for help,
>> Josephine.
>
>.
>