SQL Server Profiler trace with T SQL vs GUI

I have created a trace to find out

 ApplicationName,

Databasename,

HostName,

Loginname,

NTDomianName,

NTusernbame,

Servername,

SessionLoginanme,

Starttime,

Textdata

Filter Per database(If required)

And Created a template with

SQL:StmtStarting---this will give all SQL related statements (DDL,DDL)

RPC(remote proceedure):completed,

SP:completed  (SP)

I used GUI, and when i use that my C drive was getting Filled out soon, the reason is when we open a trace files to run via GUI that causes paging and fills out space in C drive quickly

If we script out what we choose and make little modification with T SQL life will be easy to monitor

USE below:

/****************************************************/

/* Created by: SQL Server 2008 R2 Profiler          */

/* Date: 08/18/2015  10:27:12 AM         */

/****************************************************/

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

set @DateTime = '2015-08-18 11:00:05.000'------Stop Time

set @maxfilesize = 2048----2GB max File size

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create

@TraceID output,

2, ----roll over files

N'E:\mssql\August18th',

@maxfilesize,

@Datetime,

@filecount = 'max_rollover_files'

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 7, @on

exec sp_trace_setevent @TraceID, 10, 8, @on

exec sp_trace_setevent @TraceID, 10, 64, @on

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 26, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 35, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 43, 7, @on

exec sp_trace_setevent @TraceID, 43, 8, @on

exec sp_trace_setevent @TraceID, 43, 64, @on

exec sp_trace_setevent @TraceID, 43, 1, @on

exec sp_trace_setevent @TraceID, 43, 10, @on

exec sp_trace_setevent @TraceID, 43, 26, @on

exec sp_trace_setevent @TraceID, 43, 11, @on

exec sp_trace_setevent @TraceID, 43, 35, @on

exec sp_trace_setevent @TraceID, 43, 12, @on

exec sp_trace_setevent @TraceID, 43, 6, @on

exec sp_trace_setevent @TraceID, 43, 14, @on

exec sp_trace_setevent @TraceID, 41, 7, @on

exec sp_trace_setevent @TraceID, 41, 8, @on

exec sp_trace_setevent @TraceID, 41, 64, @on

exec sp_trace_setevent @TraceID, 41, 1, @on

exec sp_trace_setevent @TraceID, 41, 10, @on

exec sp_trace_setevent @TraceID, 41, 26, @on

exec sp_trace_setevent @TraceID, 41, 11, @on

exec sp_trace_setevent @TraceID, 41, 35, @on

exec sp_trace_setevent @TraceID, 41, 12, @on

exec sp_trace_setevent @TraceID, 41, 6, @on

exec sp_trace_setevent @TraceID, 41, 14, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, 'DBNAME'

exec sp_trace_setfilter @TraceID, 35, 0, 1, NULL

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go

to check the status of trace running :

 SELECT * FROM ::fn_trace_getinfo(NULL)

--EXEC sp_trace_setstatus @traceid = 2, @status = 0--stop

--EXEC sp_trace_setstatus @traceid = 2, @status = 2--close 

Paging issue with using GUI:
http://www.sqlservercentral.com/Forums/Topic4497-5-1.aspx
http://mssqlwiki.com/2011/07/16/my-c-drive-gets-full-when-i-open-the-profiler-trace/

This tmp file is used by the SQL Server Profiler to buffer the actual output trace files. Once the SQL Server Profiler is closed, these files are deleted automatically.