| Author |
Topic |
|
ap7256
Starting Member
9 Posts |
 Posted - 2008-11-24 : 14:05:32
|
| I am running an instance of SQL Profiler and saving trace data into a table. The profiler is running fine but from time to time I am getting a message "Failed to save trace data to table".Could someone assist me to fix the issue pleasethanks |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-11-25 : 04:37:14
|
| I'm not sure why you get this error but instead of saving the trace to a table, you can save it to a file. When you are finished, you can use "fn_trace_gettable" to import the rows from the trace file. |
 |
|
|
ap7256
Starting Member
9 Posts |
Posted - 2008-11-25 : 13:48:06
|
| I am running the profile constantly. Would it be better to save data into a file and copy the data from the file into a table let's say every 10 min? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-25 : 13:49:36
|
| Can you tell us reason why you are running profiler constantly? what are you tracing exactly? |
|
|
|
ap7256
Starting Member
9 Posts |
Posted - 2008-11-25 : 14:07:16
|
| Ok, I am trying to isolate so called "high cost" queries. For our purpose all queries that take more than 250ms to execute are considered "high cost" queries and must be optimized. So I have a filter in the Profiler trace and it captures all transactions that have duration parameter larger than 250ms. I save those records in a table and I am sending a report every 10 min to notify the developers (including myself) about those high cost transactions. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-25 : 14:09:32
|
| Can't you use Top 10 queries with standard reports that comes with SQL Server instead of running Profiler continously? |
|
|
|
ap7256
Starting Member
9 Posts |
Posted - 2008-11-25 : 14:17:07
|
| the top 10 report that comes with with SQL returns the same results all the time. I need to see results dynamically as they come in. Profiler trace allows me to see that. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-25 : 14:20:52
|
| Then see this so you don't tax to your server continously:Choose correct events:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105302 |
|
|
|
ap7256
Starting Member
9 Posts |
Posted - 2008-11-25 : 14:38:37
|
| so, in other words the Profiler should write to a file and not to a table, correct? and I will create a SQL job that would copy the new data from the file into the table every xxx minutes, right? |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-11-25 : 16:57:20
|
| Thats correct.You can also make a daily job to start the trace, lets say at 7am, and a job to stop the trace, lets say at 8pm. This means that your trace runs every day from 7am till 8am. If its really necessary to trace all day (also at night) than you don't need these 2 jobs. |
|
|
|
|
failed to save trace data to table