Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Server 2008 R2 TEMPDB massive autogrowth suddenly. Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 4:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
Joie Andrew (1/7/2013)
Of couse these solutions are only good if SQL has not been restarted since the TempDB errors, as the DMVs will only collect metrics from the time SQL was started.


Those aren't since SQL started. They're queries whose plans are still in cache.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403917
Posted Monday, January 7, 2013 5:10 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:07 AM
Points: 710, Visits: 1,452
Ah, great point. I did not realize that. That is an important difference. Thanks for pointing it out Gail!

Joie Andrew
"Since 1982"
Post #1403922
Posted Monday, January 7, 2013 5:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 5:52 PM
Points: 37, Visits: 156
I've sent an email to all our report writers, reports are written in Crystal. so i'd expect the queries are hardly optimised. problem is becuase it's the start of the year big reports are being run though they "should" be run on the dedicated report server. Some of these guys think they know better.

There are also built in reports in the application that some have been modified by the report team. these can be run by anyone with required security rights in the application all it asks for is a date range. These are all run off the live DB as well. I've asked the report writers to check any reports they might have changed in the last few weeks.

I feel it must be a report as if it was an application glitch we should have had it before. unless it was just one of those things that need a perfect situation to occur.

The server has not been rebooted since it happened.
Post #1403935
Posted Tuesday, January 8, 2013 7:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
The default trace records which login is responsible for file autogrowth. This sometime shows as sa, for example during some system operations, but it is worth a try.

Here's the script I use:

use master;
set nocount on;

declare @path sql_variant
select @path = value
from ::fn_trace_getinfo(0) i
join sys.traces t on t.id = i.traceid
where t.is_default = 1 and i.property = 2

select df.StartTime,
e.name,
df.DatabaseName,
case when df.ApplicationName like 'SQLAgent%JobStep%'
then isnull((select name from msdb..sysjobs where job_id = CONVERT(uniqueidentifier, convert(varbinary(16), substring(df.ApplicationName, charindex('(Job', df.ApplicationName) + 5, 34), 1))), df.ApplicationName)
else df.ApplicationName end AppName,
df.LoginName,
df.SPID,
df.Duration,
df.EndTime,
df.FileName,
df.TextData,
df.IntegerData
from ::fn_trace_gettable(convert(nvarchar(255), @path), 0) df
join sys.trace_events e ON df.EventClass = e.trace_event_id
where e.category_id = 2
order by df.StartTime desc

If the default trace file has rolled over, modify the first bit to look at an older file.



Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Post #1404225
Posted Tuesday, January 8, 2013 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
To view all default trace files:

Reviewing AutoGrow events from the default trace by Aaron Bertrand


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1404255
Posted Wednesday, January 9, 2013 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 5:52 PM
Points: 37, Visits: 156
Well the issue has not happened again.

I did get some feedback from one of our report writters that they were testing a new large report at that time. I'd say this was the cause, they are now only going to run that report off the report server.
Post #1405005
Posted Wednesday, January 9, 2013 3:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 5:52 PM
Points: 37, Visits: 156
Are apparently a few new reports where run and the user accidently set some filters wrong so alot more data was pulled than should have been. the report has been re run and the issues didn't return.
Post #1405045
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse