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 2^nd 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