Running SQL Trace

Question

Running SQL Trace

sergei.gorelik

Valued Member

Points: 63
More actions
April 21, 2010 at 12:41 pm

#221567

My security team would like to run SQL traces on one of our database servers. How can I restart a trace after a reboot? Is there any other way, other then a start up stored procedure?
thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

To start it automatically after a reboot? The only way I know is through the start up script which you don't want to run.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sergei.gorelik Valued Member Points: 63 More actions · Answer 2

Grant Fritchey (4/21/2010)
To start it automatically after a reboot? The only way I know is through the start up script which you don't want to run.

I was afraid of that... thanks for the answer 🙂

homebrew01 SSC Guru Points: 55553 More actions · Answer 3

I made a version of Lowell's trace, and put it in a job. Then the job is scheduled to start whenever SQL restarts.

http://www.sqlservercentral.com/Forums/Topic735841-146-1.aspx

I changed the path, so that it creates a new file name based on the current date, otherwise I was getting an error because it was trying to use a file name that already existed.

SELECT @path = '\\Server\Trace_Files\ServerName\'

SET @path = @path + N'MyDMLtrace_SQL_x' + convert(varchar(25),getdate(),12)--system appends .trc automatically for the filename

Trace files can get big, so you might need a routine to archive or delete old ones.

Lowell SSC Guru Points: 323547 More actions · Answer 4

am i reading this wrong?

i thought a server side trace starts when you run the command EXEC sp_trace_setstatus @traceid, 1, and runs continuously. rebooting or stopping/starting the server doesn't affect the trace, it continues capturing what it was scripted to do until the the trace is stopped;

you can create, start and stop a trace without rebooting the server.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55553 More actions · Answer 5

Maybe I jumped through hoops that I didn't need to ?!

Lowell SSC Guru Points: 323547 More actions · Answer 6

homebrew01 (4/21/2010)
Maybe I jumped through hoops that I didn't need to ?!

well, I've never had to restart the default trace or a c2 trace; i could be wrong, but that's what i thought....someone will enlighten me, i'm sure.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 7

Lowell (4/21/2010)
am i reading this wrong?
i thought a server side trace starts when you run the command EXEC sp_trace_setstatus @traceid, 1, and runs continuously. rebooting or stopping/starting the server doesn't affect the trace, it continues capturing what it was scripted to do until the the trace is stopped;
you can create, start and stop a trace without rebooting the server.

If you want a trace to restart after the server has been rebooted or sql stopped/restarted, you will need to use a startup procedure. sp_trace_setstatus just starts/stops/closes & deletes it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 8

sergei.gorelik (4/21/2010)
My security team would like to run SQL traces on one of our database servers. How can I restart a trace after a reboot? Is there any other way, other then a start up stored procedure?
thanks

Okay, I just thought of another way. Make a job that starts the trace. For the schedule, set the "Schedule Type" to "Start automatically when SQL Server Agent starts". For the job step, have all the code necessary to create and start the trace.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 9

Lowell (4/21/2010)
am i reading this wrong?
i thought a server side trace starts when you run the command EXEC sp_trace_setstatus @traceid, 1, and runs continuously. rebooting or stopping/starting the server doesn't affect the trace, it continues capturing what it was scripted to do until the the trace is stopped;
you can create, start and stop a trace without rebooting the server.

As far as I know, if you stop the server, the trace stops and has to be restarted.

Not the default trace. That's different. But it doesn't capture DML.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 10

WayneS (4/21/2010)
sergei.gorelik (4/21/2010)
My security team would like to run SQL traces on one of our database servers. How can I restart a trace after a reboot? Is there any other way, other then a start up stored procedure?
thanks
Okay, I just thought of another way. Make a job that starts the trace. For the schedule, set the "Schedule Type" to "Start automatically when SQL Server Agent starts". For the job step, have all the code necessary to create and start the trace.

That's true. I forgot that you can schedule the jobs to start with SQL Agent.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

homebrew01 SSC Guru Points: 55553 More actions · Answer 11

Grant Fritchey (4/21/2010)

As far as I know, if you stop the server, the trace stops and has to be restarted.
Not the default trace. That's different. But it doesn't capture DML.

That's what I've found too, in my minimal experience. So I set the trace to re-start when SQL restarts. (per my earlier post)

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 12

homebrew01 (4/21/2010)

Grant Fritchey (4/21/2010)
As far as I know, if you stop the server, the trace stops and has to be restarted.
Not the default trace. That's different. But it doesn't capture DML.
That's what I've found too, in my minimal experience. So I set the trace to re-start when SQL restarts. (per my earlier post)

I had to know for sure, so I just validated it. I started a trace with no stop time, shut down the server, restarted the server, the trace was gone.

That's in keeping with my understanding of how it works, but now I feel better about my understanding.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323547 More actions · Answer 13

Grant Fritchey (4/22/2010)
I had to know for sure, so I just validated it. I started a trace with no stop time, shut down the server, restarted the server, the trace was gone.
That's in keeping with my understanding of how it works, but now I feel better about my understanding.

i did the same as grant, and I feel better now; i had seen some (what i thought were shenannigans ) some stuff on our test server with traces disappearing, and thought one of the other developers dropped my example traces;

I learned yet another thing here on SSC; another reason i love this place.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 14

Lowell (4/22/2010)
Grant Fritchey (4/22/2010)
I had to know for sure, so I just validated it. I started a trace with no stop time, shut down the server, restarted the server, the trace was gone.
That's in keeping with my understanding of how it works, but now I feel better about my understanding.
i did the same as grant, and I feel better now; i had seen some (what i thought were shenannigans ) some stuff on our test server with traces disappearing, and thought one of the other developers dropped my example traces;
I learned yet another thing here on SSC; another reason i love this place.

I was pretty sure it dropped them until you were pretty sure it didn't. I had to go & double-check what I knew. A regular occurrence here on SSC. Another is when I find that what I knew is utterly wrong. I love this place.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning