a) Same as Sleeping, except Dormant also indicates that the SPID has

been reset after completing an RPC event. The reset cleans up

resources used during the RPC event. This is a normal state and the

SPID is available and waiting to execute further commands.

b) Same as "sleeping", except a "DORMANT" SPID was reset after

completing an RPC event from remote system (possibly a linked server).

This cleans up resources and is normal; the SPID is available to

execute. The system may be caching the connection. Replication SPIDs

show "DORMANT" when waiting.

Linked server connections from SQL Server 2000 clients rely on

connection pooling. After each batch is completed against the linked

server, the client instance sends the sp_reset_connection suffix

command to clean up the metadata information

After the sp_reset_connection stored procedure is performed, the SPID

goes into Dormant status on the remote linked server instance. You can

see the status of the SPID when you query the sysprocesses system

table.SELECT * FROM MASTER.DBO.SYSPROCESSES WHERE Status='Dormant'

If there are no additional queries to the same linked server, the

client instance of SQL Server disconnects the dormant connection to

the remote linked server instance after 5 minutes of inactivity.

However, the Audit Logout event is not produced, even though the

connection is disconnected successfully

When using linked servers there is no guaranteed that you get the same

connection nor same SPID between the execution of 2 consecutive

batches.

There is no way to force this behavior. By default SQL Server does not

use pooling when using linked servers.

Manu