SQL Clone
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 Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86324 Visits: 45232
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2281 Visits: 2032
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
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 503
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-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 1172
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14825 Visits: 14396
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
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 503
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
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 503
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