September 7, 2009 at 12:42 am
Hi all,
I having the error message "Timeout Expired" when trying to insert a record into database.
May I know what is the cause of the problem and what can I do to avoid this?
Thanks 🙂
September 7, 2009 at 1:06 pm
how many rows you were inserting ? 1 or 1 milion ?
September 8, 2009 at 9:09 am
A couple of options. Run sp_who2 to see if your insert is being blocked. You can also run Profiler to check for Blocking/Deadlocks or you can enable the 1204 trace flag (SQL Server 2000) or 1222 (SQL SErver 2005/2008) to have deadlock information posted to the event log.
I'd start with sp_who2 and work from there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2009 at 8:30 pm
Are you getting error while inserting record from application or database itself.
In case of deadlock, you should see deadlock victim error message in database. Probably it could be some other issues, may be related to blocking.
Swarndeep
http://talksql.blogspot.com
September 13, 2009 at 9:47 pm
Actually I having 2 cases with timeout expired error, both using application to do insertion and selection:
Case A:
When insert a record into a table which 1,000,000 records.
- Getting timeout expired error. even using query analyzer, takes > 10 min to get the result
Anyway to improve this?
September 17, 2009 at 2:13 pm
You need to consider on optimize the query and indexes.
Make sure the maintenance tasks are running regularly as per schedule, like, rebuilding indexes, de-fragmenting etc.
Swarndeep
http://talksql.blogspot.com
September 17, 2009 at 7:35 pm
Swarndeep (9/17/2009)
You need to consider on optimize the query and indexes.Make sure the maintenance tasks are running regularly as per schedule, like, rebuilding indexes, de-fragmenting etc.
If a field with auto increment running no is the index, will it result "timeout expired" error when the database records getting larger?
Is rebuilding indexes means re-create the index?
De-fragmenting done on database or pc?
September 17, 2009 at 10:08 pm
setlan1983 (9/17/2009)
Swarndeep (9/17/2009)
You need to consider on optimize the query and indexes.Make sure the maintenance tasks are running regularly as per schedule, like, rebuilding indexes, de-fragmenting etc.
If a field with auto increment running no is the index, will it result "timeout expired" error when the database records getting larger?
Is rebuilding indexes means re-create the index?
De-fragmenting done on database or pc?
It is no necessary that auto increment is always an Index, though even if this is index, it is not necessary that it is the right index you need. You might have index requirement on other columns that are used mostly as search criteria. Run Query Execution plan, search for table scan or index scan. Your target should be to avoid table scan and index scan on large tables. No SQL queries do not time out, even if query takes two hours to complete. Timeout is mostly configured at Application layer, not database layer.
Rebuilding the index is same as re-creating the index.
De-Fragmentation is different on Database and PC. On PC, it is the data blocks which are organized during defrag and at database level, it's index pages which are defrag which organizing.
Follow these links to know more about dbcc commands that can help. Whatever I shared with you is my best guess, but reality could be something else, that can be better judged by analyzing statistics etc.
http://msdn.microsoft.com/en-us/library/aa258821(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms175008.aspx
Swarndeep
http://talksql.blogspot.com
September 17, 2009 at 10:27 pm
For Case A I would say you have some blocking going on, definitely see who is doing it.
For Case B I would say indexing and tuning sounds right. Your where clause is probably not hitting any indexes so it is doing a table scan.
CEWII
September 18, 2009 at 1:32 am
Elliott W (9/17/2009)
For Case A I would say you have some blocking going on, definitely see who is doing it.For Case B I would say indexing and tuning sounds right. Your where clause is probably not hitting any indexes so it is doing a table scan.
CEWII
For case B, the primary key and index for TableA is an auto increament number field which I also think not good. But somehow the db is not designed by me, is there anyway to imporve the db performance without changing the db design?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply