Timeout occurs when accessing a SQL table

TKL CE Shiraishi, Takehiro 21 Reputation points

The following symptoms are occurring in SQL Server 2008.
Please tell us the cause / countermeasures.

When accessing a specific table existing in SQL Server, the access times out due to the following error.

"Fatal Error: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. [Database Ado: Exec]"

The access frequency to the corresponding table is 00 minute every hour, and the tables are processed in the following order.

  1. TRUNCATE the table
  2. INSERT data into the table

SQL Server 2008 R2

As a condition for this phenomenon to occur, when access to a table unrelated to this table occurs frequently before,
The access timeout to the table in question has occurred this time.
However, this phenomenon may occur even in such a situation.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,579 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points

    It sounds like a blocking issue. I experienced something like this recently (SQL Server 2019). Few tables were somehow engaged in a transaction which was still running. Any selects from these tables never finished (updates, inserts resulted in some errors I don't remember which right now). So your first task would be to figure out if you have long running transactions involving these tables.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 92,956 Reputation points

    The timeout is something that happens in the client API, it is not an SQL error. By default, most client API give up after 30 seconds if they have not seen any results from SQL Server by then. When you get this error, you must be respond with IF @@trancount > 0 ROLLBACK TRANSACTION, to make sure that any transaction is rolled back.

    It sounds like the issue you are seeing is due to blocking. Which be due to that an earlier timeout was not handled correctly.

    0 comments No comments

  2. AmeliaGu-MSFT 13,936 Reputation points Microsoft Vendor

    Hi TKLCEShiraishiTakehiro-2361,

    Additionally, this issue may also be a performance problem, and require you to pursue it as such. If the query execution time can be diminished, the query timeout or cancel would not occur. It is important that the application is able to handle the timeout or cancel scenarios should they arise, but you may also benefit from examining the performance of the query. Please refer to Troubleshoot query time-out errors and Troubleshoot slow-running queries which might be helpful.
    And You can use the following query to check if there is blocking problem when inserting data to table:

    WITH cteBL (session_id, blocking_these) AS   
    (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s   
    CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '    
                    FROM sys.dm_exec_requests as er  
                    WHERE er.blocking_session_id = isnull(s.session_id ,0)  
                    AND er.blocking_session_id <> 0  
                    FOR XML PATH('') ) AS x (blocking_these)  
    SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these  
    , batch_text = t.text, input_buffer = ib.event_info, *   
    FROM sys.dm_exec_sessions s   
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id  
    INNER JOIN cteBL as bl on s.session_id = bl.session_id  
    OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t  
    OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib  
    WHERE blocking_these is not null or r.blocking_session_id > 0  
    ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;  

    Please refer to Understand and resolve SQL Server blocking problems for more details.
    Best Regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. TKL CE Shiraishi, Takehiro 21 Reputation points

    To everyone who gave me the advice,
    Thank you for your reply. I would like to proceed with problem solving based on the advice.

    0 comments No comments