none
SQL Locking up

    Pregunta

  • Posted - 12/25/2005 :  10:54:44      
    My ADO based C++ application works fine for sometimes 15+hrs just fine. Im using SQL 2000. All of a sudden I dont know what happens all update operatins and insert operations start failing with 80040e31, timeout expired errors.
    It seems as though the whole database is locked up.

    And after 1hr or sometime 2 hrs everything is back to normal without any intervention.

    if i intervene and i stop all my app services. i run sp_who and there are no connections to the database. I restart my application services and still have those 80040e31 errors come up.

    From the query analyzer im able to perform operations. like for example
    there is a table with just 8 rows.

    if i do a
    delete from table1 where col=1
    it takes 1.30 minutes to delete 8 rows...

    Actually this is the problem. so an update also i think takes more than 30s and that is why it times out.
    so probably if i increase the timeout to 3 minutes everything will be fine. I dont want to do that. rather i want to find out why the updtes are timing out ...
    Any help is appreciated.

    Thanks
    domingo, 25 de diciembre de 2005 22:25

Respuestas

  • Yes.
    If your transactionlog is growing with 800MB (default 10% growth rate) you might experience some delay I suppose :-) 

    Your changes to the transactionlog size and growth may prevent your blocking problem too.

    jueves, 29 de diciembre de 2005 13:03

Todas las respuestas

  • Try checking for locking when this behaviour is occurring.
    Use sp_lock to get an overview of the locks.  Maybe there are some transactions that aren't being committed.  Are there many foreign keys linked to the table?
    lunes, 26 de diciembre de 2005 11:52
  • I checked. there are no transactions on that table.  no foreign keys at all !!

    but if i delete all the rows and then restart my services. the services start running fine again with no problems for hours together.

    lunes, 26 de diciembre de 2005 13:40
  • Could you execute the query like this and post the results.

    SET STATISTICS IO ON
    SET STATISTICS TIME ON

    delete from table1 where col=1

    And also post the query plan here by using the next batch.
    SET SHOWPLAN_TEXT ON
    GO
    delete from table1 where col=1




    martes, 27 de diciembre de 2005 7:35
  • one interesting thing i noticed. Update/Insert is locked for all the tables in that database at that time. But once i run an update query in the query analyzer and let it run to finish and it takes like 1.30 minutes then the whole database seems to reset and every other update works fine. after that all my app services are back to normal without even restarting them , because all the updates work fine !!
    i dont know if it makes a difference but all my queries use username=sa.

    database size: 7030MB
    available space:693MB
    no maintainance plan
    it is set to automatically grow file by 10%
    Auto Update statistics set
    Torn Page set
    auto create stats set

    martes, 27 de diciembre de 2005 12:44
  • Maybe some other process is using up your CPU time at that moment?

    martes, 27 de diciembre de 2005 12:47
  • nope the cpu is totally free
    martes, 27 de diciembre de 2005 13:36
  • Could you execute the query as mentioned above and post the results?
    martes, 27 de diciembre de 2005 15:13

  • SQL Server parse and compile time:
       CPU time = 7 ms, elapsed time = 7 ms.

    SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

    (1 row(s) affected)

    Table 'Items'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

    SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

      |--Clustered Index Update(OBJECT:([testdb].[dbo].[Items].[PK_Items]), SET:([Items].[Col1]=[Expr1004]), DEFINE:([Expr1004]=If ([Items].[col1]<>NULL) then [Items].[col1] else 0|2), WHERE:([Items].[col2]=1))

    miércoles, 28 de diciembre de 2005 20:43
  • one other interesting thing i noticed is when i run this

    dbcc opentran

    Server: Msg 7969, Level 16, State 2, Line 1
    No active open transactions.
    Transaction information for database 'TestDB'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    i ran this when the hanging was happening

    miércoles, 28 de diciembre de 2005 20:46
  • Is there any kind of SQL logging i can setup to see if something unusual is happening at that time. it looks like there are no locks held but it is something on SQL which is blocking any and every update statement to run and it blocks it for 2 minutes +
    and it is table independent and is database wide.
    other databases on the same server are fine.
    one other thing is let us say i run the upate statement itself in a begin tran/rollback tran even then everything is reset and app services start working fine. but if i stop the statement before it is completed everything is locked up.
    miércoles, 28 de diciembre de 2005 21:39
  • You could run Performance Monitor to check Processor Time, Processor Queue Length, Disk Queue Length... etc.  There are some scripts to check for blocking processes but since it is blocking updates on all tables this wouldn't help you much.

    Profiler might give you more information about which batches are being executed against the database at that moment.

     

    miércoles, 28 de diciembre de 2005 21:56
  • im not sure if this is related . The ldf file for the database is toooooooooooo huge. it is like 8207 MB !!!

    anyways i have taken care of that. i have set it to a max of 200mb and grow by 5 mb.

    but any ideas on that if it could cause the problems im seeing ?

    miércoles, 28 de diciembre de 2005 23:27
  • What is your backup schedule?
    jueves, 29 de diciembre de 2005 7:22
  • This is a test environment so there is no backup done.

    i just cleaned up the log. But one thing i want to know is. can this tlog increasing may be a cause for SQL locking up ??

    jueves, 29 de diciembre de 2005 12:56
  • Yes.
    If your transactionlog is growing with 800MB (default 10% growth rate) you might experience some delay I suppose :-) 

    Your changes to the transactionlog size and growth may prevent your blocking problem too.

    jueves, 29 de diciembre de 2005 13:03
  • Did this issue ever get resolved? I am experiencing a similar issue with SQL Server 2000 in that an update of 2 tables (as a single transaction) takes up to 2 minutes (or fails) on the first attempt, and will succeed immediately on a second attempt. I've discovered via Profiler and Query Analyzer that the insert (of the second table) is being blocked by a connection sitting in an "awaiting command" state. Curiously, whenever this transaction fails (after the 2 minute timeout), it succeeds within 1-2 seconds on the second attempt.
    lunes, 23 de enero de 2006 22:14
  • Could you check the Query Plan?
    martes, 24 de enero de 2006 18:07