| Author |
Topic |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 2010-04-20 : 05:16:51
|
| i have created a trace in sql profiler for finding out errors alone. like foreign key conflict, sntax errors, user errors like that. I have passed the error information to table and not to trc file. will it affect performance. since i have included only Error Log under Error& warning events i think it wont affect performance. any suggestions pls and also for trace table i have created a trigger which sends mail to admin. thanks in advanceIam a slow walker but i never walk back |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-20 : 05:50:06
|
| Don't run traces via the profiler GUI to a table. It's got the highest overhead and worst impact of all possible trace options. If you're tracing a busy production server, don't use the profiler GUI at all, rather use a server-side trace and write to file on a fast local drive.Yes, it means that the errors won't go immediately to an admin, but is it required that an admin be contacted immediately?--Gail ShawSQL Server MVP |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2010-04-21 : 02:14:00
|
hi thanks,the prob is that not all errors get logged in sql server error log file. so i have used this approach. i have used sp_altermessage to log the error but it throws error. so i have used this approach of Sql server side trace and using a job for each day.quote:
Originally posted by tkizer
http://weblogs.sqlteam.com/tarad/archive/2008/08/06/SQL-Profiler-best-practices.aspxInstead of tracing for exceptions, we instead have the application log the event and then email us.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Iam a slow walker but i never walk back |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2010-04-21 : 02:25:00
|
Thanks i have applied sql server tracequote:
Originally posted by GilaMonster
Don't run traces via the profiler GUI to a table. It's got the highest overhead and worst impact of all possible trace options. If you're tracing a busy production server, don't use the profiler GUI at all, rather use a server-side trace and write to file on a fast local drive.Yes, it means that the errors won't go immediately to an admin, but is it required that an admin be contacted immediately?--Gail ShawSQL Server MVP
Iam a slow walker but i never walk back |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2010-04-21 : 06:07:37
|
| hi everyone,After creating server side trace and logging the errors to file, and usingSELECT * FROM ::fn_trace_gettable('C:\ErrorTrace.trc', DEFAULT) but the problem is Textdata column shows like "Invalid object name '%.*ls'.", "Incorrect syntax near '%.*ls'.". but what i need is the table name where the error occured. any solution. Thanks in advanceIam a slow walker but i never walk back |
|
|
|
stobe
Starting Member
1 Post |
Posted - 2011-02-17 : 07:44:42
|
quote:
Originally posted by tkizer
http://weblogs.sqlteam.com/tarad/archive/2008/08/06/SQL-Profiler-best-practices.aspxInstead of tracing for exceptions, we instead have the application log the event and then email us.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
That is not a well written article on best practice. All it does is state some rules as though they are commandments and makes no effort to justify or explain them. I know DBAs who are very good at quoting best practice, but when pressed can not explain the reasons behind the rule. For example, I'm sure that rule #2 is very sensible, but after reading your best practices I am none the wiser as to why logging to tables is worse than logging to a file, especially when adhering to rule #1 by running the trace on a separate machine. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-17 : 11:40:54
|
| You are a lot nice than I am Tara |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2011-02-28 : 16:29:01
|
quote:
Originally posted by russell
You are a lot nice than I am Tara
Agreed. +1000 to Tara for courtesy. |
|
|
|
|
Sql profiler