SQL Server Performance

sql locking up

Discussion in 'General Developer Questions' started by dbuser123, Dec 26, 2005.

  1. dbuser123 New Member

    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 ...
    If i let the deletion from query analyzer complete and then restart the services then everything is fine again !!
    Any help is appreciated.

    Thanks
  2. Luis Martin Moderator

    Run Profiler to trace all work, and find long queries or sp. May you have to optimize those queries.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. mmarovic Active Member

    Do you have an autoshrink set on your db? Do you have any maintenance jobs scheduled? What are db settings regarding initial size and increments both on data and log files?
  4. dbuser123 New Member

    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
  5. Luis Martin Moderator

    Well I don't recomend to all user login with sa. Who will be responsable for any problem?

    No maintainance plan is wrong.

    You have at least planing for: Integrity, Optimization, Index Rebuild and Update Statistic even when you have auto update statistics on.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  6. mmarovic Active Member

    quote:Originally posted by dbuser123

    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

    What about autoshrink option, is it on? Based on info you provided it looks like data file was expanded recently. What are log file(s) settings? Try to add another 3 GB file in default file group (probably primary) and follow-up how long it will run without problems this time. I guess problem might be caused by data file and/or log file growth.
  7. satya Moderator

    Can you also check the activity on TEMPDB while running the resource intensive queries?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. dbuser123 New Member

    No TempDB activity.
    As I mentioned before when this was happening
    as soon as i ran an update command and let it finish then everything was back to normal.
    one thing i noticed is when i run dbcc opentran i get this

    one other interesting thing i noticed is when i run some error...

    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
  9. dbuser123 New Member

    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.
  10. Luis Martin Moderator

    You can run profiler to find out locks.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  11. dbuser123 New Member

    i think im seeing a problem. The ldf file for the database is toooooooooooo huge. it is like 8207 MB !!!
  12. dbuser123 New Member

    Im sure i need to fix that. But can that be causing the problems im having ?
  13. ghemant Moderator

    Hi,
    may suggest to read article on Vyas's site

    http://vyaskn.tripod.com/watch_your_timeouts.htm
    and search other relavent threads in the forum

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  14. satya Moderator

    The problem could be there is no schedule to backup the transaction log or using too much of bulk inserts. 8GB of transaction log is not big enough to worry and you may need to only if there are any disk space concerns.

    In any case if you can maintain the schedule of trnasaction log then the size will be taken care of, also consider the other scheduled jobs and bulk insert jobs that can contribute size increase in Tlog.

    For information check thishttp://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 blog.

    quote:Originally posted by dbuser123

    Im sure i need to fix that. But can that be causing the problems im having ?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. dbuser123 New Member

    What I want to know is if the transaction log was causing the problem ??
  16. dbuser123 New Member

    one thing to add is all my SPs create a lot of temp databases for local processing..
  17. satya Moderator

    Check the disk contention during Transaction log or user database backup.
    Use PERFMON to capture disk related counters for further assessment. It is an usual behaviour all your queries using temp database to process and make sure there is no blocking is happened. You can take help of blocker script or use this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;271509 for further investigations.

    quote:Originally posted by dbuser123

    What I want to know is if the transaction log was causing the problem ??

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  18. Luis Martin Moderator

    What recovery model do you have?
    Also what kind of backup are you running?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  19. dbuser123 New Member

    This is an internal stress test server. The recovery model was set to Full.
    I think the main problem was that the log file was autogrowing at 10% unlimited growth !!

    for past 2 days it looks like it is running fine. i have now set the max to 300 mb growth. obviously i expect customers to have a backup plan..
  20. Luis Martin Moderator

    If recovery is full and log is to big, I suppose the backup plan is daily and no transaction log backup each, said 1 hour, at all.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page