To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
The following hotfix was created before the release of Microsoft
SQL Server 2000 Service Pack 3.
Contact Microsoft Product Support Services to
obtain the fix.
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the
tab in the Date and Time tool in Control Panel.
Date Time Version Size File name
07/01/2002 4:50 PM 8.00.652 7,285 kilobytes Sqlservr.exe
Because of file dependencies, the most recent hotfix or feature
that contains the files may also contain additional files.
If you want the functionality of this fix, you must apply a Sqlservr.exe build
later than or equal to 8.00.652, and, you must run the Sp2_qfe_serv_uni.sql
file that is included in this fix. For more information, see the Readme.txt
file that is included in the fix files.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.This problem was first corrected in Microsoft SQL Server
2000 Service Pack 3.
Here is a definition and the syntax for the fn_get_sql
system table-valued function.
Returns the SQL text referred to for the specified handle.
= ] SqlHandle
The binary handle value. SqlHandle is binary(20)
with no default.
Collapse this tableExpand this table
|Column name||Data type||Description|
|dbid||smallint||Database ID. NULL in the case of ad-hoc SQL statements.
|objectid||int||ID of the database object. NULL in the case of ad-hoc SQL
|number||smallint||Number in procedure grouping if grouped. 0 for entries that are not procedures. NULL in the case of ad-hoc
|encrypted||bit||Indicates if the object is encrypted:|
0 = Not encrypted
1 = Encrypted
|Text||text||SQL Text. NULL in the case of encrypted objects.|
is a system table-valued function that returns the SQL text for
the specified SQLHANDLE. You can obtain a valid SQLHANDLE from the sql_handle
column of the sysprocesses
If you pass a handle that no longer
exists in cache, fn_get_sql
will return an empty result set. If you pass an invalid handle,
the batch will abort, and you receive the following error
Server: Msg 569, Level 16, State 1,
Procedure fn_get_sql, Line 12 The handle passed to fn_get_sql was invalid.
SQL Server cannot cache some Transact-SQL statements, such as bulk
operation statements and statements with string literals larger than 8 KB.
Handles to those statements are not retrievable through the fn_get_sql
The text column is filtered for text that may
contain passwords. Review the "Limiting Traces" topic in SQL Server Books
Online for details about the security-related stored procedures that are not
Only members of the sysadmin
fixed server role can run the fn_get_sql
The information returned by the fn_get_sql
function is similar to the DBCC INPUTBUFFER command. Use the fn_get_sql
function in situations where DBCC INPUTBUFFER is limited, such
- When events have more than 255 characters.
- When you have to return the highest current nesting
level of a stored procedure. For example, you have two stored procedures that
are named sp_1 and sp_2. If the sp_1 stored procedure calls the sp_2 stored procedure, and you get the handle from the sysprocesses system table while sp_2 is running, the fn_get_sql function will return information about sp_2. Additionally, the fn_get_sql function returns the whole stored procedure at the highest
current nesting level.
Database administrators can use the fn_get_sql
function to help diagnose problem processes. After an
administrator identifies a problem server process ID (SPID), the administrator
can retrieve the SQLHANDLE for that SPID, and then call the fn_get_sql
function with the handle and use the start and end offsets to
determine the SQL text of the problem SPID. For example:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle)
You can also use the fn_get_sql
function to continuously monitor the server. For example, a
client tool that periodically retrieves the SQLHANDLE and the statement start
and end offsets from the sysprocesses
system table. The tool maintains a cache of SQL text, with the
SQL handle as the unique key and the results of the fn_get_sql
function as the value. For each row in the sysprocesses
system table rowset, the tool looks up the text in it's cache
based on the SQLHANDLE. If the text is not in the tool's cache, the tool then
calls the fn_get_sql
function to get the text and save it in its cache.
Trace flag 2861
Trace flag 2861 instructs SQL Server to keep zero cost plans in
cache, which SQL Server would typically not cache (such as simple ad-hoc
queries, set statements, commit transaction and others).
- If trace flag 2861 is turned on, the fn_get_sql function can return the SQL text for activities that have zero
cost plans. If trace flag 2861 is turned off, the fn_get_sql function cannot return the SQL text for the activities with zero
- By default, trace flag 2861 is turned off when you
apply this fix.
The number of objects in the procedure cache increase when trace
flag 2861 is turned on. Because the additional objects are so small, you will
see a small increase in memory, which is taken up by the procedure cache.
SQL Server 2000 has an efficient algorithm to find any existing
execution plan for any specified SQL statement. However, because of the
increased number of objects stored in the procedure cache, it is possible that
the time it takes for the relational engine to search for an existing plan may
degrade and may adversely affect the performance of your system.
Typically, on systems where the database size is much larger than the memory
size, the system is under some expected memory pressure. If the memory pressure
is such that memory is required for other objects, the lazywriter process will
deallocate objects in the procedure cache. This will bind the size of the
procedure cache and will minimize the potential adverse affect of this change.
However, on systems where the memory size is larger than the
database size, the system is generally not under memory pressure. Therefore,
objects are not deallocated from the procedure cache because of memory needs,
and the procedure cache size can grow to a point where it will adversely affect
If you note an adverse affect on system performance,
follow these steps:
- Turn trace flag 2861 off.
- Run the DBCC FREEPROCCACHE command from Query Analyzer.
You do not have to restart SQL Server.