Set Up And Schedule a Server Side Trace

Question

Set Up And Schedule a Server Side Trace

Viewing 15 posts - 16 through 30 (of 38 total)

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

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

CavyPrincess (12/2/2010)
Grant Fritchey (12/2/2010)
Writing to file is much, much faster than writing to a database, so yes, it does make a difference. Also, is that database on the same server you're monitoring? That can be a problem, adding overhead where none should exist.
But of course it's on the same server 🙂 Since I'm going to change process around anyway, I'll change it to create the flat files and import then into the database afterwards. I was just being lazy to save a step as it hasn't yet caused a problem. I certainly don't want to be the cause of problems, so thank-you for your response.
Cindy

Of course. Happy to help. Also, tracking down Brad McGehee's excellent book will help even more.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jack Corbett SSC Guru Points: 184388 More actions · Answer 2

ALZDBA (12/2/2010)
I haven't noticed it would not cleanup the trace def in systraces after the end time.
Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing 😉
So in my instances they get cleaned up.
edited
You can find my setup script in the attachement it was to long to just post in the reply :blink:

I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Jack Corbett (12/2/2010)
ALZDBA (12/2/2010)
I haven't noticed it would not cleanup the trace def in systraces after the end time.
Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing 😉
So in my instances they get cleaned up.
edited
You can find my setup script in the attachement it was to long to just post in the reply :blink:
I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.

Ha! I was just sitting down to start testing this when I saw your post. Thanks. It's what I thought, but it never hurts to be sure.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

Great Stuff Grant.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Good stuff as always, Grant! Thanks for taking the time to write articles like this. This one should be in every DBA's quick reference guide. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

Jeff Moden (12/2/2010)
Good stuff as always, Grant! Thanks for taking the time to write articles like this. This one should be in every DBA's quick reference guide. 🙂

Thanks Jeff, but you & both know after you've done it once or twice, it's hard to forget. Hopefully it helps new people get a leg up a little quicker.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

CirquedeSQLeil (12/2/2010)
Great Stuff Grant.

Thanks Jason.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

jason.spangler SSChasing Mays Points: 634 More actions · Answer 8

I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.

Thanks to the peoples that did the testing!

Jack Corbett SSC Guru Points: 184388 More actions · Answer 9

jason.spangler (12/3/2010)
I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.
Thanks to the peoples that did the testing!

No worries. That's what SSC is all about , testing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 10

Jack Corbett (12/3/2010)
jason.spangler (12/3/2010)
I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.
Thanks to the peoples that did the testing!
No worries. That's what SSC is all about , testing.

Oh indeed ...

Don't just take anything for granted.

Test it - Test IT !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 11

ALZDBA (12/3/2010)
Jack Corbett (12/3/2010)
jason.spangler (12/3/2010)
I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.
Thanks to the peoples that did the testing!
No worries. That's what SSC is all about , testing.
Oh indeed ...
Don't just take anything for granted.
Test it - Test IT !

Absolute 100% Agreement!

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

rob mcnicol SSC Eights! Points: 834 More actions · Answer 12

great article, thanks.

what tweaks to this process are required to run a server-side trace on analysis services? when i export my Script Trace Definition, the 'For SQL...' options are greyed out. i am only able to export 'For Analysis Services...' which produces an xmla file.

what do i do with that xmla file to schedule the trace?

my task at hand is that i have to create an audit of access to a cube. any thoughts on whether this is a sufficiently robust mechanism to do this? if not, can anyone suggest alternatives?

thanks

rob

SalvageDog SSC Veteran Points: 205 More actions · Answer 13

Hello,

Thanks for a great article.

I have a very intermittent problem, occurs only two or three times a month. Would there be any issues with running a server side trace all day, other than creating a lot of .trc files? I'm especially concerned about an all-day trace using too many resources and affecting server performance.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 14

Here is the another code snippest to write Server Side Trace

http://abhijitmore.wordpress.com/2010/03/11/creating-custom-trace/

Abhijit - http://abhijitmore.wordpress.com

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 15

rob mcnicol (1/31/2011)
great article, thanks.
what tweaks to this process are required to run a server-side trace on analysis services? when i export my Script Trace Definition, the 'For SQL...' options are greyed out. i am only able to export 'For Analysis Services...' which produces an xmla file.
what do i do with that xmla file to schedule the trace?
my task at hand is that i have to create an audit of access to a cube. any thoughts on whether this is a sufficiently robust mechanism to do this? if not, can anyone suggest alternatives?
thanks
rob

I am frankly unsure. I've never run a trace on SSAS, so I don't know exacty how it works.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software