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=126.96.36.199, 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.
- Installing SQL Server 2008 BIDS
- 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.