April 26, 2018 at 9:50 am
I ran a profiler trace last night on a SQL Server 2016 instance using SQL Server Profiler 2017. I saved the data to a table as the trace was running. I left the profiler window open so I could also save the trace file (as a .trc file) after the fact. I find it useful to have both. This morning - I blame morning brain fog - I chose "save as" and pointed the save file to a remote disk that I just remembered is NAS and very slow. At this point it has processed 7% of the save in 15 minutes. I would like to stop the save and repoint it to a local disk and then move it, however, I can't figure out how to do that. I have tried ctrl+c, ctrl+break, break by itself and various other combinations to no avail. Has anyone else ever experienced this and have a suggestion?
Thanks!
April 27, 2018 at 6:25 am
Not a clue how to address your specific issue, but, one HUGE point.
The GUI for Profiler has a different memory management mechanism within SQL Server than the server-side trace events do. It can absolutely take over the memory of your server and cause all sorts of problems. Since we're posting in a SQL Server 2017 forum, I can very safely say, you shouldn't be using Profiler or Trace any more, you should be using Extended Events. However, regardless of whether or not you take that advice, please, don't run the Profiler GUI against your production environments ever again. It is quite dangerous for your servers.
Good luck on the Save As issue.
"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
April 27, 2018 at 9:36 am
Thank you for the advice. I am well aware of the risks SQL Profiler poses to production servers. I also prefer to use Extended Events whenever possible. However, this was a controlled test - one user sending one transaction at a time through a proprietary application/database. This was done so that I could define what to look for using extended events. Profiler was run from a workstation, not directly on the server and I left the results open to save the next morning (it was a long test and saving was not top priority since I had already sent the results to a size limited table). I have to add that SQL Profiler 2017 is still a tool that is part of the SQL Server Tool Suite. I know that there has been discussion since SQL Server 2012 that it will be replaced with extended events, however that hasn't happened yet - probably because there are times when it can be useful.
All of that said - after 4 hours the results were saved. I was unable to determine any way to stop the save once it started.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply