FIX: Executing Sp_replcounters with INSERT..EXEC May Cause Server to Stop Responding

• Microsoft SQL Server version 7.0

SYMPTOMS

Executing the replication stored procedure sp_replcounters using the Transact-SQL INSERT..EXEC syntax may cause SQL Server to stop responding (hang). Depending on the existing activity against SQL Server, either a complete hang may occur, where new connections will fail, or it will hang so that existing queries cannot execute.

It is possible for the server to become only partially hung where some work may continue. However, in these cases internal processes in the server, such as Lazywriter, may not get scheduled. In these situations, users may consume all of the memory allocated to SQL Server and the following type of errors may then be encountered when new connections are made to the server:

ods Error: 17803, Severity: 17, State: 17
ods Insufficient memory available..
ods Error: 17803, Severity: 17, State: 14
ods Insufficient memory available..

WORKAROUND

If you need to save the results from sp_replcounters into a table, use a INSERT with SELECT against the sysperfinfo table. The sysperfinfo table contains information about all SQL Server performance counters including replication.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: spinlock DBTABLE hang hangs hung tsql t-sql

Keywords          : SSrvStProc kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 8, 1999