xp_trace_setqueryhistory (T-SQL)

Specifies the enabling or disabling of query history using the SQL Server Profiler extended stored procedures. Query history consists of a trace of the last 100 events that occurred before a high-severity SQL Server exception. Query history traces SQL:BatchStarting, RPC:Starting and Exception events and captures all data columns. When an exception of severity 17 or greater occurs, the query history is written to the file Blackbox.trc in \Mssql7\Log.

Syntax

xp_trace_setqueryhistory [[@local_variable =] value]

Arguments

@local_variable: Is a local variable of bit data type.
value: Is whether query history is enabled (1) or disabled (0). When query history is enabled, SQL Server captures the last 100 SQL Server events. If value is not specified, SQL Server returns the current value for xp_trace_setqueryhistory.

Return Code Values

0 (success) or >1 (failure)

Result Sets

If value is specified, xp_trace_setqueryhistory returns this message:

The command(s) completed successfully.

If value is not specified, xp_trace_setqueryhistory returns this result set.

Column name	Description
QueryHistoryConfigValue	Whether the query history trace is configured to start on start up: 1 = True 0 = False
QueryHistoryRunValue	Whether the query history trace is running: 1 = True 0 = False

Permissions

Execute permissions for xp_trace_setqueryhistory default to members of the sysadmin fixed server role but can be granted to other users.

DECLARE @local_variable	xp_trace_flushqueryhistory
Monitoring with SQL Server Profiler	System Stored Procedures (SQL Server Profiler Extended Procedures)
SET @local_variable