I 've a query that links many tables. So when executes a stored procedure it gives...
Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.
What can I do for this.. May I have to increase the time out of the SQL server. I'm using SQl 2008. Could you please help me to resolve this..
When one transaction holds a lock on a data resource and another transaction requests an incompatible lock on the same resource, the request is blocked and the requester enters a wait state. By default, the blocked request keeps waiting until the blocker releases the interfering lock. To get lock information, including both locks that are currently granted to sessions and locks that sessions are waiting for, query the dynamic management view (DMV) sys.dm_tran_locks as:
SELECT -- use * to explore other available attributes
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) AS dbname,
resource_description AS res,
resource_associated_entity_id AS resid,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks;
In the output of the query you will get the spid which is waiting for a shared/exclusive lock.You can get the involved spid's by observing rows with same res and resid values.
Further to get more information you can execute the following code to obtain connection, session, and blocking information about the processes involved in the blocking chain.
-- Connection info:
SELECT -- use * to explore
session_id AS spid,
connect_time,
last_read,
last_write,
most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN(--spid found in above query);
-- Session info
SELECT -- use * to explore
session_id AS spid,
login_time,
host_name,
program_name,
login_name,
nt_user_name,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(--spid found in above query);
-- Blocking
SELECT -- use * to explore
session_id AS spid,
blocking_session_id,
command,
sql_handle,
database_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
--SQL text of the connections involved in the blocking chain:
SELECT session_id, text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id IN(--spid found in above query);
Once you get involved spid's you can either KILL the spid using KILL <spid> command or set lock_timeout in your stored procedure using command: SET LOCK_TIMEOUT <milliseconds>;
Timeouts are never in sql server, but always in the client calling them. So, if you can not tune the query (which may be the case) change the timeout in the application you use to issue the query.
Using SQL Server Management Studio
To configure the remote query timeout option
