SQL Profiler - Server side Trace

Question

SQL Profiler - Server side Trace

Minnu

SSCertifiable

Points: 6451
More actions
May 21, 2014 at 2:14 am

#307624

Hi Team,
Created a SQL Profiler - Server side trace and inlcuded the code in a stored procedure.
Trace file size : 50 MB, with rollover, and no max files.
but after certain time, it is stopped automatically.
I want to exeucte the trace infinitely, please suggest..

Viewing 13 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Not hard. Let's start with the code you already have for the trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Hi,

Stored procedure executed, and two trace files with 50 MB created, then it stopped automatically.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Ok, so let's start with the code that you're running, or are you expecting someone to write a whole profile trace script for you without any idea what your existing trace script looks like?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Hi Gail Shaw,

Found that SQL Server restarted, hence Server side trace stopped.

In other environemnt, am executing server side trace directly without stored procedure, but it is running after sql server restart also.

Is there any difference to run server side trace with stored procedure, and with out stored procedure.

Pleases confirm.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

A trace is a trace, doesn't matter whether it's called from a proc or an agent job or ad-hoc code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnu SSCertifiable Points: 6451 More actions · Answer 6

Agree,

But the same code is running in other enviornment from last 3 days without stored procedure.

In other environemnt also sql server got restarted 2 times, still the code is running.

the same is not happening in one environment with stored procedure.

???

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Since you've given me nothing to go on (no trace script, no details of where/when/how the trace gets started), good luck figuring it out. A trace doesn't survive SQL restarting, so something, somewhere must be restarting the trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lempster SSCoach Points: 15651 More actions · Answer 8

Gail beat me to it. 🙂 What you describe is not possible. You could have a trace restart after a SQL Server restart if it is initiated by a stored procedure and that stored procedure is set as a startup stored procedure, but if the trace is initiated by an ad-hoc T-SQL script, the only way I can think of having that start automatically after a SQL Server restart is by including the script in a SQL Agent job. Even then, the job would have be set to run on a repeating schedule.

Changing tack slightly, why do you want a Profiler trace to run indefinitely? From SQL 2008 (R2?) onward, you can use Extended Events in place of Profiler - much more lightweight in terms of resource impact which I'd have thought would be an important consideration of you are going to run a trace 'indefinitely'. (A plug-in for SSMS is required to use Extended Events in 2008R2, but in 2012 it is present out-of-the-box).

Regards

Lempster

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

You don't need a plugin to implement extended events in 2008, there's just no GUI. You'll have to write queries to start it, stop it, set it up an consume the XML output. It's not a huge deal, but can be a bit of work.

I'm with everyone else. Something restarted the trace after the server restart. Trace just isn't stored in the server in the same way extended events are.

"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

Lempster SSCoach Points: 15651 More actions · Answer 10

Hi Grant,

Plug-in might be the wrong term then, but I'm sure there is something available that brings the Extended Events GUI into SSMS. Maybe it's just the Extended Events node that appears..?

...ah yes, I think it is available here: http://extendedeventmanager.codeplex.com/

Regards

Lempster

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Yeah, it's a plug-in, but all it does it provide a GUI, it's not required to use Extended Events. The underlying feature is there without that, just has to be interacted with via scripts.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lempster SSCoach Points: 15651 More actions · Answer 12

Yes, sorry I wasn't very clear. I know that moving from Profiler to Extended Events is quite a jump so I wanted to point out a GUI add-in is available for 2008, but my post read as though Extended Events is not available at all. Apologies!

Regards

Lempster