SQL Server Performance

Transaction Log back up and ASP timeout errror

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by Ranjit72, Jun 5, 2006.

  1. Ranjit72 New Member

    Hi all,
    I have a typicall issue with one of our Web apps and MS SQL 2000.The user complains that intermittenly there is a "MS OLE DB Timeout error" in their web app.The underlaying SQL table has roughly 500K records.The server is fairly decent one with Win2000, SP 4. Compac x86 family 6 model 10 stepping with 1gb RAM.

    Upon monitoring event log there is a maintainence plan for the specific database in question which backs up transaction log every 3 hrs, I believe the time out in ASP pages happens during that period. Is there any reason why this coincedence? If so what do I need to do. I am fairly certain that the web group will resist any change to their SQL statements/quires.

    I apologize if I posted in the wrong group.
    ANy help and suggestion will be appriciated.

    Thank you
    Ranjit
  2. smy New Member

    You may consider increasing the login timeout and query timeout to 0 (meaning infinity wait) on the web application side.
  3. satya Moderator

  4. Ranjit72 New Member

    SMY, I already did both your suggestions, thank you though.

    Satya,I cannot move the Log files to a different drive as it is a standalone server(tried to convince the client for a RAID, but it's their choice).Anyways, it appears to be that sometimes even though the Backup job is not running the error seems to happen. Is there anyway we can trace this scenario? Or what else should I be looking at to find the reason for getting timed out. There are roughly 600K records, There is an identity col which is indexed and is the first col starting from Left.Most of the columns are varchar(100) and has roughly 30 cols.

    Thank you for your help. Your suggestions are much appreciated.

    --Ranjit
  5. mmarovic Active Member

    From your explanation it looks like database model is far from optimal and index design too. Having also in mind that db is small, I think that dev. team who designed and developped the app was inexperienced.

    If you need quick fix, run profiler and post here the worst running queries. Also if there is autoshrink on the production database set, switch it off.
  6. ranjitjain New Member

    Can this be a SQL service Pack or MDAC issue?
  7. satya Moderator

    ..and also may be service pack issue on SQL & Windows too, as you;re saying during the backup log it is happening try to perform the Tlog backup every hour instead of 3 hours interval.

    Satya SKJ
    Microsoft SQL Server MVP
    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.

Share This Page