SQL Server Performance Forum – Threads Archive
Server: Msg 1204, Level 19, State 1, Line 1
Hi everybody, I am a new joinee to this forum and this is my 1st post. I am getting the error messageError: 1204, Severity: 19, State: 1
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.
When i execute the DTS Task. This DTS Task contains something around 35,00,000 records. It is a plain simple select * from Source DB and Insert into Destination DB. The task is getting executed till 33,76,000 records after that the DTS is throwing and error. Kindly let me know how shall i resolve this problem.
The source and the destination database are on the same server. And there are no users/application actually pointing to the destination database as i have setup a fresh database. The server capacity is also high with 1 GB RAM.
Do let me know how shall i crack this problem. Regards,
Amar Desai
[email protected]
What is the current value for locks? Also during this operation run Select from master..syslocks to see which table is running up the locks and and the query. TO reduce the locking run the statement in batches if it is affecting large number of rows.
Another option is to specify a hint like table or page lock so that the number of locks can be reduced. HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
id dbid page type spid
0 4 0353
040352
070357
070361
070360
070356
070354
070355
080363
080351
080358
714548-1363
557505880663 The query cannot be used in batches because there is no unique number as a column in the database. Regards,
Amar
How about the current value for locks?
Run SP_CONFIGURE and see the value/
Check this MSDN link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_6gxg.asp] for more information.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
sp_configure locks query gives me. locks5000214748364700 Memory allocation is dynamically configured. The query which i am using for source db is "select * from Det_Usage with (NOLOCK)" and the destination db is also on the same server only difference is the name of the table. Regards,
Amar
How about the indexes on the source table?
Have you used the same query in QA and see query execution plan. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hello, We are not using indexes on the source table. When i executed the same query from the QA it is throwing me the same error after it tries inserting 33,70,000 records. Even DTS is inserting 33,70,000 records and then giving the error
"Error: 1204, Severity: 19, State: 1 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. " Regards,
Amar
To resolve the issue go thru this KBA http://support.microsoft.com/default.aspx?scid=/servicedesks/bin/kbsearch.asp?Article=323630] _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>