Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 R2 TEMPDB massive autogrowth suddenly.


SQL Server 2008 R2 TEMPDB massive autogrowth suddenly.

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)

Group: General Forum Members
Points: 976 Visits: 1917
Ah, great point. I did not realize that. That is an important difference. Thanks for pointing it out Gail!

Joie Andrew
"Since 1982"
PretendDBA
PretendDBA
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 491
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.
Richard Fryar
Richard Fryar
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 1171
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
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
PretendDBA
PretendDBA
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 491
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.
PretendDBA
PretendDBA
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 491
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search