New at using RML Utilities, SQLDiag creates huge output files (> 40 GB) when running the utility for only 10 minutes; where can I change parameters?

Question

Hello all,

I am fairly new at using the RML utilities.  I ran through the sample that comes with the tool on my local SQL Express instance with no issues.  Today I ran the utility for the first time on a server and only had SQLDiag running for 10 minutes while I ran a workload on 1 database.

After I stopped the trace and SQLDiag, Diag produced an output file of over 40 GB.  I also had 3 trace files each at 250 MB.  Does anyone know if I can pass TraceCaptureDef.sql a database ID to focus on specific databases?  Also, is there a parameter in PPConfig.xml that I can change to reduce the output file size for SQLDiag capture?

Any help would be greatly appreciated.  Thanks.

Answer 1

Just a quick update.  I found out how I can filter by database ID within the TraceCaptureDef.sql script.  sp_trace_setfilter so I'll give that a try.

I am still unsure why SQLDiag.exe created and output file of over 40 GB though.

Again, any insight would be appreciated.  Thank you.

Answer 2

The amount of data collected is completed dependant on what events are being collected by SQLDIAG. SQLDIAG is utility designed for collecting diagnostic data all at once (perfmon, profiler traces, DMV outputs etc.) for troubleshooting performance issues.

Reference:
http://msdn.microsoft.com/en-us/library/ms162833.aspx
http://support.microsoft.com/kb/298475 

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 3

I ran SQLDiag with <ProfilerCollector enabled="false".  It only ran for 9 minutes and produced that very large output file.  9 minutes creates a 40 GB file?  What elements inside PPconfig.xml can I remove to cut this file size down?  I just find it hard to believe that PerfMon counters running for 9 minutes creates that large of a file, especially if you turn off Profiler collection.

Answer 4

By default SQLDIAG creates a folder called SQLDIAG in the Binn folder and uses a SQLDIAG.XML file as the configuration file and collects information from all instances on the box. I'm not aware of SQLDIAG just creating ONE file. 

The PPConfig.XML file is a copy of the SD_Detailed.XML file which has profiler config, perfmon counters config and some custom tasks. The output of these tasks should exist in the output folder.

Can you provide the following information:

1. Can you provide the name of the file that is large in size?
2. Are you using a default configuration file or the PPConfig.XML file mentioned @ http://sqlcat.com/technicalnotes/archive/2008/02/01/precision-performance-for-microsoft-sql-server-using-rml-utilities-9-0.aspx?
3. Also, provide the command that you are using to execute the SQLDIAG along with the parameters.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 5

SQLDiag did create an output folder with several files.  Below I list the file that was very large.  Also, I did change 1 line in PPConfig.xml to turn Profiler off and to capture data from my server remotely

1. <serverx>_<SQLinstancex>_sp_sqldiag_Shutdown.OUT was the file that was over 40 GB.  I eventually had to kill the SQLDiag.exe process and delete the file, due to having no free space left on my laptop hard drive.  For security, I won't display my actual server name or SQL instance.
2. I am using the PPConfig.xml file mentioned at that website, with the exception of changing <Machine name="."> to the server I was capturing data from and <ProfilerCollector enabled="false"
3. sqldiag.exe /Oc:\temp\DJMCapture /IPPConfig.xml

Thank you.  Feel free to ask me any additional questions.

Answer 6

Thanks for the information. This makes the problem more clear.

The SQLDIAG shutdown output file collects the SQL Server Errorlogs, DMV outputs and various other information like sp_configure etc. etc. All this information is put into a Single file which you mentioned. Also, there is an issue with trailing spaces which can cause the size of the file to bloat. You can try collecting the SQLDIAG output without the SQLDIAG shutdown file. You would have to change this:

<SqldiagCollector enabled="true" startup="false" shutdown="true" />

to

<SqldiagCollector enabled="false" startup="false" shutdown="true" />

Just a note of caution. We don't recommend collect SQLDIAG or PSSDIAG remotely for performance reasons. Remote collection of profiler traces leads to severe performance degradation.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 7

Thank you for your help Amit.  I will give that a try and let you know of my results.