As planned on Thursday, DBA Team was all set with the Change Control to run the SQL Server Profiler trace on the instance. The DBA started the trace using SQL Server Profiler and the trace files were getting stored on the D drive. Within a few minutes the trace for stopped stating there is no space on C drive. Since the C drive is “owned” by the System Admins team, the DBAs asked “Please clean it up”. Our System Admin team is very quick and they got back 6 GB of space within no time. We know, what they do. Just delete the profiles of the DBA team from the server!
DBA all geared up for the second innings, started the Profiler trace again. Five minutes later, same issue. C drive out of space. This time it was obvious that the Profiler was creating the havoc. Hence the DBA got into action. There was a file Prf*.tmp file on C:\Documents and Settings\[DBAName]\Local Settings\Temp\2 directory which was 6 GB in size. This tmp file is used by the SQL Server Profiler to buffer the actual output trace files. Once the SQL Server Profiler is closed, these files are deleted automatically.
At times, the situations drive ones decision. The DBA followed the Application Team’s request as it is and forgot the basics in this case. “Never run the SQL Server Profiler trace on a production box, use Server Side traces instead“. The DBA knew it better and started to create the Server Side script for this trace.
Since we had the template ready all we need to do was create a script out of it and run it on the instance. Opened up SQL Server Profiler and started a new trace and stopped it immediately. Then clicked on File –> Export –> Script Trace Definition –> For SQL Server 2005
The script file was saved to the drive. In the script file, all the settings of the Template were exported except for the Output location of the trace file.
All we had to do was to replace InsertFileNameHere with the actual output path.
The .trc file extension is automatically appended to the file when the trace runs. Executed the script and made a note of the TraceID, so that we can monitor the trace execution.
Now the trace was running on the Server Side and the output files were getting generated as desired.
Bob, we are on the same page now 😉