SQL Script to set up Black Box in SQL Server

Leave a comment (1) Go to comments

Simplest Script to create a quick trace, which will capture all the SQL Server statements and activities.

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to trace, which will capture all the SQL Server statements and activities. 
-- Be extra cautious as this will take significant space which depends on SQL Server activities
-- This trace will be created in default sql server Data Directory, this case c:\Program files, as we install SQL Serever 
-- =============================================*/
-- Compatibility  :     2000+                   */
-- ******************************************** */


--  Create a Trace file, which will call all SQL Server events and Activities */
declare @rc int, @traceid int
exec @rc = sp_trace_create @traceid output, 8
select traceid = @traceid, error = @rc
exec sp_trace_setstatus @traceid, 1
-- -- Select the TraceId, which is initiated by SQL Server, this number will be used to stop the trace
select * from ::fn_trace_getinfo(@traceid)

-- We are using 2, as a traceID which was started in last statement, 
sp_trace_setstatus 2, 0
sp_trace_setstatus 2, 2

This trace will be created in default sql server Data Directory, this case c:\Program files\,


Note :

  1. Be extra cautious as this will take significant space which depends on SQL Server activities.
  2. sp_trace_setstatus, is a system stored procedure, which is used to Modifies the current state of the specified trace. Here is a quick syntax for that
sp_trace_setstatus [ @traceid = ] trace_id, [ @status= ] status

where, @traceid, is the ID of the trace to be modified and @status, specifies action to be taken

Applicable trace status

Status Description
0 Stops the specified trace.
1 Starts the specified trace.
2 Closes the specified trace and deletes its definition from the server.
EOF - SQL Script to set up Black Box in SQL Server, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: