SQL Server Performance

Server: Msg 1204, Level 19, State 1, Line 1

Discussion in 'SQL Server DTS-Related Questions' started by adesai, Jul 22, 2003.

  1. adesai New Member

    Hi everybody,

    I am a new joinee to this forum and this is my 1st post.

    I am getting the error message
    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.
    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
    amar.desai@in.iqara.net
  2. satya Moderator

    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
  3. adesai New Member

    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
  4. satya Moderator

  5. adesai New Member

    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

  6. satya Moderator

    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
  7. adesai New Member

    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
  8. satya Moderator

Share This Page