Transaction Log back up and ASP timeout errror | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transaction Log back up and ASP timeout errror

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
You may consider increasing the login timeout and query timeout to 0 (meaning infinity wait) on the web application side.
Check the disk contention in that case, as the Transaction log backup should not stop other processess to access the database. http://www.sql-server-performance.com/q&a34.asp fyi. 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.
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
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.
Can this be a SQL service Pack or MDAC issue?
..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.
]]>