Timeout occurs when accessing a SQL table

Question

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

phenomenon:
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

environment:
SQL Server 2008 R2

supplement:
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.

Answer 1

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.

Answer 2

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.

Answer 3

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,
Amelia

---

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.

Answer 4

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