SQL Server: How to query from default trace on DATA & LOG file autogrow events

Today morning, we had received a severity 17 alert from one of our remote SQL Server instances, indicating primary filegroup for the tempdb had gone full.

The SQL instance is a 2008 64 bit named instance on a active / active cluster node. OS being windows 2008.

Our normal settings for tempdb database across our SQL estate is to allow for unrestricted growth with the tempdb data files being directed to a dedicated data volume.

To take corrective action and to understand what has caused the growth resulting in 150+ GB of disk space to be used up, I logged on to the remote SQL box.

To analyse the data file autogrow, and to determine how much growth happened at what time and duration, I decided to look up the report “Disk Usage”.

As luck would have it, I received an error stating “The file ‘Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ cannot be opened.”

I was left with no option but to query the information from the default trace file. The query used for retrieving the autogrow events is as below:

IF (SELECt convert(int,value_in_use) FROM sys.configurations WHERE name = 'default trace enabled')  = 1
 
BEGIN
 
    DECLARE @curr_tracefilename varchar(500) ; 
    DECLARE @base_tracefilename varchar(500) ; 
    DECLARE @indx int ;
 
    SELECT @curr_tracefilename = path from sys.traces where is_default = 1 ; 
    SET @curr_tracefilename = reverse(@curr_tracefilename);
 
    SELECT @indx  = patindex('%\%', @curr_tracefilename) ;
   SET @curr_tracefilename = reverse(@curr_tracefilename) ;
 
   SET @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ; 
         
 
    SELECT 
    (dense_rank() over (order by StartTime desc))%2 as l1,
    convert(int, EventClass) as EventClass,
   DatabaseName,
    Filename,
    (Duration/1000) as Duration,
    StartTime,
    EndTime,
    (IntegerData*8.0/1024) as ChangeInSize 
    FROM ::fn_trace_gettable( @base_tracefilename, default ) 
    WHERE EventClass >=  92
    AND EventClass <=  95
    AND
    ServerName = @@servername
    AND
    DatabaseName = db_name()  
    order by StartTime desc
 
END

 

Now I had the data, fine, but now another headache remains, resolving the error that pops up whenever we try to veiw reports on the SQL instance.

I have 2 choices over here.

  1. Installing SQL Server 2008 BIDS

OR

  1. Installing “Microsoft Report Viewer 2008 SP1 Redistributable” from http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=bb196d5d-76c2-4a0e-9458-267d22b6aac6

My take would be to go for the 2nd option as that would be light weight. But on a production environment, any new install requires a change request, getting the change requested validated and signed off and then carrying out the install on an agreed date and time. But that is for another day.

 
Regards

Vasudev Menon

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

4 Comments on “SQL Server: How to query from default trace on DATA & LOG file autogrow events”

  1. Hi vasu,

    I’d like to know, how do you trace the error ? before I run the script, what should i do ?

    “I was left with no option but to query the information from the default trace file. The query used for retrieving the autogrow events is as below:”

  2. Hi Hendra,

    From the default trace, the information you only get to know are [in this specific case] at what exact time the file, either the data or log file started to autogrow, by how much and how many times. But to exactly know what was happening at that point of time on your SQL instance, what you need is a sort of session capture. On most of LIVE production servers, a continues session capture [data to be kept or stored only for a couple of days, older data to be auto-purged] helps immensely during the investigation stage on the exact cause, the culprit spid, what was the SQL text that was being executed, total reads, total writes, total space utilization on tempdb etc. I think, its time I wrote a blog on session capture, for me the data collected is the first thing I study as part of investigation during such issues.

  3. Hi Amit,

    Sorry for the late reply, but I wanted to push in more blog on capturing SQL sessions, before answering your query. My blog “why is it necessary to capture and store information on sessions i.e spid(s) running on SQL instance for troubleshooting purpose” points the way in collecting the second part of the crucial information on what exactly caused the autogrow, which user, which application etc was responsible.

Leave a Reply

Your email address will not be published. Required fields are marked *