Timeout expired. All pooled connections were in use and Max pool size was reached

ArticleDirectory

• Use with statusonly
• A. Use kill to terminate a session
• B. Use the kill session ID with statusonly to obtain the progress report.
• C. Use kill to terminate an isolated Distributed Transaction

Error message:

Server Error in '/xxxx' application.

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and Max pool size was reached.

Solution:

(1) execute the storage process sp_who in the SQL Server Manager window to view all active connections.

(2) Use kill to terminate a process. The usage of kill is as follows.

-- Run the following script to terminate all connections:

-- Author: Eman Lee

-- Date: 11/11/2008

Use [Master]

Go
Declare @ spid sysname, @ MSG varchar (100), @ cmd varchar (1000)
Declare table_cur cursor
Select spid from Master. DBO. sysprocesses where db_name (dbid) = 'database name'
Open table_cur
Fetch next from table_cur into @ spid
While @ fetch_status = 0
Begin
If @ fetch_status =-2
Continue
Select @ MSG = 'Kill '+ @ spid
Print @ msg
Select @ cmd = 'Kill '+ @ spid
Exec (@ cmd)
Print''
Fetch next from table_cur into @ spid
End
Deallocate table_cur
Go

Kill (TRANSACT-SQL)

Terminate a user process based on a session ID (spid in SQL Server 2000 or earlier) or a work unit (uow. If the specified session ID or uow has a lot of work to undo, the kill statement may take some time to complete, especially when it involves rolling back long transactions.

In Microsoft SQL Server 2000 and later versions, kill can be used to terminate a normal connection, which internally terminates the transaction associated with the given session ID. If you are using Microsoft Distributed Transaction Processing Coordinator (ms dtc), this statement can also be used to terminate all isolated and questionable distributed transactions.

Transact-SQL syntax conventions

Syntax

Kill {session ID | uow} [with statusonly]

Parameters

Session ID

The session ID of the process to terminate.Session IDIs a unique integer (Int). During the connection, the session ID value is bound with the connection. When the connection ends, the integer is released and can be reassigned to the new connection.

Use killSession IDYou can terminate general non-distributed transactions and distributed transactions associated with the specified session ID.

Uow

ID of the unit of work (uow) that identifies a distributed transaction ).UowYesSYS. dm_tran_locksDynamic Management ViewRequest_owner_guidGuid obtained in the column. You can also get it from the error log or through the ms dtc MonitorUow. For more information about monitoring distributed transactions, see the ms dtc documentation.

Use killUowYou can terminate an isolated distributed transaction. These transactions are not associated with any real session ID, but with the virtual session ID = '-2. It makes it easier to identify isolated transactions by queryingSYS. dm_tran_locks,SYS. dm_exec_sessionsOrSYS. dm_exec_requestsThe session ID column in the dynamic management view.

With statusonly

Generate a specified rollback because of an earlier kill statement.Session IDOrUowProgress Report. Kill With statusonly does not terminate or roll backSession IDOrUow, This command only displays the current rollback progress.

Remarks

The kill command is usually used to terminate a process that blocks other important processes by locking them or is executing a query that is using necessary system resources. System processes and processes running extended stored procedures cannot be terminated.

Use kill with caution, especially when running important processes. You cannot cancel your processes. Other processes that cannot be canceled include:

• Awaiting command
• Checkpoint sleep
• Lazy writer
• Lock Monitor
• Signal handler

Use @ spid to display the session ID value of the current session.

To obtain the Report of the Active session ID value, you can querySYS. dm_tran_locks,SYS. dm_exec_sessionsAndSYS. dm_exec_requestsIn the dynamic management viewSession_idColumn. You can also viewSp_whoReturned by the system stored procedureSpidColumn. If the rollback of a specific spid is in progressSp_whoResult setCMDThe column indicates killed/rollback.

When a specific connection has a lock on the database resource and blocks other connection processes,SYS. dm_exec_requestsOfBlocking_session_idColumn orSp_whoReturnedBLKThe session ID of the blocked connection is displayed in the column.

The kill command can be used to solve problematic distributed transactions. These transactions are unresolved distributed transactions that are generated by unscheduled restart of the database server or ms dtc coordinator. For more information about problematic transactions, see the "two-phase commit" section in the transactions using tags (full recovery mode.

Use with statusonly

Only the current killSession ID|UowWhen the session ID or uow is rolling back, kill with statusonly will generate a report. The progress report specifies the number of rollback completed (percentage) and the estimated remaining time (in seconds). The format is as follows:

Spid | uow <XXX>: Transaction rollback in progress. Estimated rollback completion: <YY> % estimated time left: <ZZ> seconds

If you run killSession ID|UowWith statusonly statement, the session ID or uow rollback is completed, or no session ID or uow is rolling back, killSession ID|UowWith statusonly will return the following error:

"MSG 6120, level 16, status 1, 1st rows"

"Unable to obtain the status report. The rollback operation of process id <session ID> is not in progress ."

Repeat the same kill without the with statusonly OptionSession ID|UowStatement to obtain the same status report. However, this is not recommended. If the rollback is completed before running the new kill statement and the session ID is re-allocated to the new task, repeat killSession IDStatement may terminate a new process. Specifying with statusonly will prevent this situation.

Permission

RequiredSysAdminOrProcessadminA fixed server role member.

Example A. Use kill to terminate a session

The following example shows how to terminate a session ID.53.

Copy code

Kill 53; go

B. Use the kill session ID with statusonly to obtain the progress report.

The following example generates the status of the rollback process for a specific session ID.

Copy code

Kill 54; Kill 54 with statusonly; Go -- this is the progress report. Spid 54: Transaction rollback in progress. Estimated rollback completion: 80% estimated time left: 10 seconds.

C. Use kill to terminate an isolated Distributed Transaction

The following example shows how to terminateUowIsD5499C66-E398-45CA-BF7E-DC9C194B48CF(Session ID =-2 ).

Copy code

Kill 'd5499c66-E398-45CA-BF7E-DC9C194B48CF ';

See

Kill stats job (TRANSACT-SQL)
Kill query notification subqueries (TRANSACT-SQL)
Functions (TRANSACT-SQL)
Shutdown (TRANSACT-SQL)
@ Spid (TRANSACT-SQL)
SYS. dm_exec_requests (TRANSACT-SQL)
SYS. dm_exec_sessions
SYS. dm_tran_locks (TRANSACT-SQL)
Sp_lock (TRANSACT-SQL)

Sp_who (TRANSACT-SQL)

From: http://msdn.microsoft.com/zh-cn/vbasic/ms173730.aspx