Use of SQL Server's built-in SQLDiag tool Program

SQLDiag is a built-in diagnostic collection sharing program of SQLServer. [SQLServer2005] default location: C: ProgramFilesMicrosoftSQLServer90ToolsBinnSQLDiag.exe you can execute SQLDiag in any path because this path has been added to the path system variable, execute sqldiag -? Or sqldiag? The parameter description is displayed.

SQLDiag is a built-in diagnostic collection sharing program of SQL Server. [SQL Server 2005] default position: C: \ Program Files \ Microsoft SQL Server \ 90 \ Tools \ Binn \ SQLDiag.exe you can execute SQLDiag in any path because this path has been added to the path system variable and sqldiag -? Or sqldiag /? The parameter description is displayed.

SQLDiag is a built-in diagnostic collection sharing program of SQL Server,

[SQL Server 2005]
Default location: C: \ Program Files \ Microsoft SQL Server \ 90 \ Tools \ Binn \ SQLDiag.exe
You can execute SQLDiag in any path because this path has been added to the path system variable and sqldiag -? Or sqldiag /? The parameter description is displayed.
Use the forward slash (/) to match sqldiag parameters of SQL 2005 "/"

SQLdiag
You can collect the following types of diagnostic information:
Windows performance record
Windows Event History File
SQL Server Profiler Tracing
SQL Server Blocking Information
SQL Server Configuration Information

General use:
For example:
Sqldiag/o c: \ temp \ sqldiag
(/O specifies the directory of the output file)
At the bottom of the page
2008/07/01 00:34:42. 82 SQLDIAG
Collection started. Press Ctrl + C to stop. indicates the Collection is in progress
At this time, press Ctrl + C to stop collecting collected information and store it in the specified C: \ temp \ sqldiag \ directory. Several log_xx.trc files are from C: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ LOG \ path copied

Advanced collection: Modify and specify xml counters to collect Performance Counter information.

[SQL Server 2000]
Default location: C: \ Program Files \ Microsoft SQL Server \ MSSQL \ Binn \ SQLDiag.exe
Switch the directory to this location and execute sqldiag -? Or sqldiag /? The parameter description is displayed.
"-" Is required for SQL 2000 sqldiag and parameter execution "-"
Example: sqldiag-E-O c: \ temp \ sqldiag. log
(-O specifies the output information to the specified file)

To allow SQL 2000 to obtain SQL Trace (*. trc) data, you can use the following [Sample Code] Or KB281671 file Sample SQL to add a Stored Procedure named trace_blackbox to the master.
KB281671 INF: a pre-stored program to create a blackbox tracking SQL Server 2000
Then, trace_blackbox 1 is executed at the scheduled time to start trace_blackbox. During execution, all online SQL commands of this Instance are recorded at the beginning of C: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ blackbox. trc: Execute trace_blackbox 0 as necessary to disable trace_blackbox. These trc files will be copied by sqldiag and renamed to the specified location.

[Caution]
If SQLDiag is executed and trace_blackbox is still open, the file cannot be copied. Therefore, SQLDiag. trc will be empty.

[Samlp Code]
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [trace_blackbox] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [trace_blackbox]

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create procedure trace_blackbox @ on int = 2
/* If no argument is passed to the @ on parameter then get the current blackbox trace status.
If @ on is zero then stop and delete the blackbox trace.
If @ on is one then create and start the blackbox trace.
*/
Declare @ traceid int, @ blackboxstatus int, @ dir nvarchar (80)
Set @ traceid = 0
Set @ blackboxstatus = 0
Set nocount on
SELECT @ traceid = traceid FROM: fn_trace_getinfo (0)
Where property = 1 and value = 8
IF @ on = 0 and @ traceid> 0
Begin
Select @ blackboxstatus = cast (value as int) FROM: fn_trace_getinfo (0)
Where traceid = @ traceid and property = 5
IF @ blackboxstatus> 0 exec sp_trace_setstatus @ traceid, 0 -- stop blackbox trace
Exec sp_trace_setstatus @ traceid, 2 -- delete blackbox trace definition
End
IF @ on = 1
Begin
IF @ traceid <traceid = "0" blackboxstatus = "0" traceid = "traceid" property = "1" value = "8" blackboxstatus = "cast (value" traceid = "@ traceid "property =" 5 "> 0 and @ blackboxstatus> 0
Begin
Select @ dir = cast (value as nvarchar (80) FROM: fn_trace_getinfo (0)
Where traceid = @ traceid and property = 2
Select 'the blackbox trace is running and The trace file is in the following directory .'
Select @ dir + '. trc'
End
ELSE select 'the blackbox trace is not running .'
Set nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON