Databases Reference
In-Depth Information
Gearing Up for Long-Term Data Collection
Sometimes there is a need to collect data for prolonged periods for intermittent issues whose occur-
rence is hard to predict. You are forced to run data collection around the clock to ensure that all
relevant data is captured for the next occurrence of the issue. This raises the question of when the
data collection should be started or stopped. The Diag Manager provides such an option through
another custom diagnostic option called Delete Old Trace Files. This custom diagnostic deletes all
but the
N
most recent Proi ler (.
trc
) i les and PerfMon (.
blg
) i les. When you click Save in the Diag
Manager UI, you are prompted to provide
the number of trace and PerfMon i les that
should be retained, as shown in Figure 11-16.
This gives you the l exibility to run data col-
lection for long periods without running out
of drive space.
FIGURE 11-16
WARNING
The number of trace i les corresponds to a i xed amount of disk
space used, but not to a i xed amount of time captured. It is always a good idea
to run SQLdiag for a brief period during peak hours to estimate the rate of
Proi ler trace events generated and the disk space consumed for about 10-15
minutes. This enables you to form a rough approximation of the time span of the
activity that will be captured before the data is overwritten. The PerfMon i les do
not increase as rapidly in size as the Proi ler trace i les. The Proi ler trace collec-
tion is by far the most resource intensive data collection component in SQLdiag.
In addition to this, you can exercise other options for long-term data collection that enable you to
retain more trace i les without running out of disk space:
➤
Reduce the set of Proi ler events being captured. The following section elaborates on this
topic.
➤
Use the /
C2
command-line parameter to enable background NTFS compression of the out-
put i les generated.
➤
Consider using the /
N
, /
E
, and /
L
command-line parameter to restart data collection at a spe-
cii c time every day, with a new output folder created for each restart of the data collection.
The older folder can be deleted if the issue for which the data collection was initiated did
not occur.
➤
Consider using SQLdiag's built-in NT service support. This can be quite helpful when
you cannot keep a remote desktop session open on your server for a long period of time.
If you are using the command line option to initialize SQLdiag, then you will end up need-
ing to keep a session active so that the console window doesn't close. By running SQLdiag
as a service, you do not have to worry about any such restrictions!
➤
Add more local disk space. This is an obvious option but it's not always viable or possible.
Search WWH ::
Custom Search