Tempdb full

Question

Tempdb full

ramyours2003

SSChampion

Points: 12268
More actions
March 16, 2018 at 3:04 am

#343970

I would like to know the transactions which caused the tempdb full on the last week . I don't have any 3rd party tool configured , is any way to find .please provide ur replies .
March 16, 2018 at 10:57 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 1

if SQL services were stopped i cannot think of a way

is this the first time it has happened?
do you limit the size of TempDB?
how big is the disk?
was it TempDB data or log which grew?

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 2

if you have backups and happen to have a log backup close to the time of failure you may be able to restore that and query what was running at the time

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 3

if you can restore close to the failure try this:

DECLARE @filename nvarchar(1000);
SELECT @filename = cast(value as nvarchar(1000))
FROM ::fn_trace_getinfo(default)
WHERE traceid = 1 and property = 2;
SELECT *
FROM ::fn_trace_gettable(@filename, default) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE DatabaseName = 'tempdb'
ORDER BY ftg.StartTime;

should show what was active in TempDB

Sue_H SSC Guru Points: 90860 More actions · Answer 4

sambartick 90616 - Friday, March 16, 2018 5:01 AM
if you can restore close to the failure try this:
DECLARE @filename nvarchar(1000);
SELECT @filename = cast(value as nvarchar(1000))
FROM ::fn_trace_getinfo(default)
WHERE traceid = 1 and property = 2;
SELECT *
FROM ::fn_trace_gettable(@filename, default) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE DatabaseName = 'tempdb'
ORDER BY ftg.StartTime;
should show what was active in TempDB

Except backup and restore operations aren't allowed with tempdb.

Sue

jasona.work SSC Guru Points: 50117 More actions · Answer 5

ramyours2003 - Friday, March 16, 2018 3:04 AM
I would like to know the transactions which caused the tempdb full on the last week . I don't have any 3rd party tool configured , is any way to find .please provide ur replies .

If you didn't have some sort of auditing or trace already in-place and running at the time the TempDB filled up (and I'm presuming that by "filled up" you either have TempDB limited in size or on its' own drive that it expanded to fill,) then you're not going to be able to determine what caused the problem.

*MAYBE* if it was an Agent job you could look at what jobs would've been running around the time it filled up and narrow it down from there, otherwise at this point you have no way to find out what did it.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

Depending on how long ago it actually was this occurred, you might be able to get some information from the instance default trace. This script will look for Autogrowths in TEMPDB:
--default trace history DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc' FROM sys.traces WHERE is_default = 1;

SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, td.EndTime, td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData FROM sys.fn_trace_gettable(@path, DEFAULT) td INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id WHERE td.DatabaseID = 2 AND td.EventClass IN (92) ORDER BY td.StartTime;

You can at least see when the growths occurred, and the login name. If it was a scheduled SQL agent job you might see something like this in the ApplicationName:
SQLAgent - TSQL JobStep (Job 0x2F0C91B758120349A8718608E2087C2E : Step 18)
which you can find what job it is by running the following query in MSDB:
SELECT * FROM msdb.dbo.sysjobs WHERE job_id = 0x2F0C91B758120349A8718608E2087C2E;

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

ramyours2003 - Friday, March 16, 2018 3:04 AM
I would like to know the transactions which caused the tempdb full on the last week . I don't have any 3rd party tool configured , is any way to find .please provide ur replies .

For the future, you could setup an alert to notify you and even capture the running queries into a table along with where, what, and maybe even who was running them.

Also, I've never looked in the SQL Server log to see if anything is captured there. You might get lucky by looking. As someone else said, default trace might help if it hasn't rolled over.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)