How to start the SQL Profiler Automatically

Question

Post reply

How to start the SQL Profiler Automatically

srilu_bannu

Hall of Fame

Points: 3100
More actions
October 12, 2011 at 1:59 pm

#245198

Hi All,
I have a old sql database which was moved to the new server and i want to disable the old one after everyone starts using the new sql server database. I have been using SQL Profiler to see if anyone using the old database which helped me a lot. I want to schedule it in the morning to see who are using it? I came across this form below and tried the easiest method in it.
http://www.sqlservercentral.com/Forums/Topic576752-146-3.aspx?Update=1
The problem is:
I was not able to script it.
I have followed this to script it:
http://msdn.microsoft.com/en-us/library/ms180822.aspx
Can anyone tell me how to script and schedule the SQL Profiler Trace file.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Aluminum Ten Centuries Points: 1152 More actions · Answer 1

This article looks straight forward and even has an example procedure. Have you tried this?

http://support.microsoft.com/kb/283790

R_S Ten Centuries Points: 1160 More actions · Answer 2

Run sp_who to check if any connections are made to SQL Server

matt.newman Hall of Fame Points: 3910 More actions · Answer 3

Is there any reason not to create a pointer or alias for the first server using the SQL browser capabilities, to redirect connections to the second server so as all data gets updated in one central place, or are the database structures different and that not advised?

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 4

I have scripted the trace I have created. But when i try to run the script I am getting this error:

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070003(The system cannot find the path specified.).

Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1

Could not create a trace file.

(1 row(s) affected)

Can anyone please help.

SCRIPT:

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

/* Created by: SQL Server Profiler 2005 */

/* Date: 10/13/2011 09:50:40 AM */

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

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

set @DateTime = '2011-10-13 10:48:21.000'

set @maxfilesize = 5

-- 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, 0, N'C:\myfolder\mytrace', @maxfilesize, @Datetime

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, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 9, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 2, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 13, 12, @on

exec sp_trace_setevent @TraceID, 13, 1, @on

exec sp_trace_setevent @TraceID, 13, 9, @on

exec sp_trace_setevent @TraceID, 13, 6, @on

exec sp_trace_setevent @TraceID, 13, 10, @on

exec sp_trace_setevent @TraceID, 13, 14, @on

exec sp_trace_setevent @TraceID, 13, 11, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 7f73cc41-54f3-4dca-8737-c89fefea8412'

-- 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

adb2303 SSCertifiable Points: 7588 More actions · Answer 5

does this path exist? If not, change it to one that does exist, or create it... 'C:\myfolder\mytrace'

thanks, Andrew

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 6

I have created the path. Actually i have created the path first and the copied the the file name into the script.

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 7

I am sorry for the confusion.

I got it. I should have created the file on the server instead of the local server.

cindyaz Ten Centuries Points: 1229 More actions · Answer 8

adb2303 (10/13/2011)
does this path exist? If not, change it to one that does exist, or create it... 'C:\myfolder\mytrace'
thanks, Andrew

Check if the service account has read/write privilages to that path.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

currently the trace file is set to reach 5MB and not rollover, is this your desired action

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉