Tracking tempdb growth using Extended Events

Everyone knows tempdb is a wierd [sic] place

Brent refers to it as a public toilet. I agree with that sentiment. It will let anyone in to do whatever they want.

Recently I was trying to track down what was causing tempdb’s log to grow. I was thinking about using a Trace to do it, but then I remembered that it’s at least 2015, and I should be using Extended Events. Don’t worry, there won’t be any PowerShell. You can keep reading.

I use the below command to fire up my session. It worked on my 2012 and 2014 instances. Anything older or newer than that, and YMMV. You will likely have to change the output directory to whatever exists on your server. Most people have c:\temp, though.

With that fired up, let’s kick tempdb around a little. Don’t do this in production, it’s going to suck. We’re going to shrink files and run an insert and update in a loop. You can increase the number of loops if you want, but 3 is good enough to illustrate how this works.

Now you can either stop the XE session, or keep it running. I don’t care. It’s your ~~~DEVELOPMENT SERVER~~~ and definitely ~~~NOT PRODUCTION~~~, right?

Right.

Let’s see what kind of germs are on the toilet seat

Look, I never said I was good at this XML stuff. If you are, feel free to tidy up all the VARCHAR(MAX) and BIGINT types into something more sensible. If you don’t like my King Krab emoji, blame Jeremiah.

But hey! The results are in. That didn’t take long.

The first file to get it is the log file. All sorts of helpful stuff got collected. If you can’t figure out what was going on from this, go home. Seriously. The query text is right there.

Meaningful data.

Meaningful data.

Yay. There’s more. The log file wasn’t the only one that blew up. All four tempdb files got used, as well. Is there an important lesson here? Probably. Even on my laptop, multiple tempdb files help. Don’t let me catch you out there with one tempdb data file. Or two tempdb log files. I’m watching you.

Happy Birthday!

Happy Birthday!

In case you were wondering…

Yes, it will capture shrinking files, too. So if anyone is being a horrible, you can catch them, and throw them out several windows.

For the love of Milton Berle, please stop shrinking files.

For the love of Milton Berle, please stop shrinking files.

You can use this on other databases as well, just change the database ID in the XE session definition. Just, you know, use it sparingly. There’s overhead for any type of monitoring, and XE is no XcEption (GET IT?). If you have a growth-heavy environment, capturing them all and the accompanying information could be a real burden on your server.

Building a better BAND-AID®

Stuff like this is good to diagnose problems short term, but it’s not meant to be a replacement for a full time monitoring solution. A lot of people spent a lot of time making nice ones. Go try them out and find one you like.

Thanks for reading!

, ,
Previous Post
Jeremiah & Kendra are Heading Out
Next Post
How many servers can one person manage?

33 Comments. Leave new

  • James Youkhanis
    December 2, 2015 1:45 pm

    Erik thanks for this great post, During the office hours today. I asked this question on how to track tempdb growth.

    Reply
  • If you have an earlier version of SQL Server, or if you are troubleshooting a situation and didn’t have the forethought to put a custom xevent session there beforehand, you can also query the default trace. It contains data and log file growth events (up through and including 2016 CTP2) and is on by default, err, hence the name. It can be a little tricky to query but I built a stored proc that rolls the process up into some dynamic SQL (getting the file name and path and then running the DMFs to query the trace) and allows you to filter, for example, on event name being like ‘%Auto Grow%’.

    SQL Monitoring tools like Spotlight are excellent…only trick is you can see the growth but you often don’t see a DIRECT correlation to whose query actually blew out tempdb, and you have to infer from the execution plans which query running at the time would have been voted Most Likely to Fill Up TempDB.

    The system_health xevent session (another default one) doesn’t look like it logs growth events…I was wondering if that would be another alternative but it doesn’t seem so.

    Downside to the default trace…on a really busy server, it can rollover quickly (I’ve seen hours but I wouldn’t be surprised by minutes) so in theory the information could be lost, where in a custom trace or xevent session, it would be more filtered and thus more long lived.

    Reply
  • Very nice code, thanks. But, is this really a problem? What if you allocate nice chunks of space to tempdb and templog and set growth in large chunks, too. Or is even consuming those chunks what you are tracking?

    Reply
    • If you have a ton of space, then probably not. Then again, I think Bill Gates once said something about no one needing more than 640k of RAM.

      This is geared towards people who don’t have a ton of disk capacity, for whatever reason, or are running into other issues that are affecting tempdb growth.

      For example, log growths pause write activity while the growth occurs. They don’t benefit from instant file initialization, so it has to be zeroed out. Maybe those big chunks you’re growing by are taking longer than expected?

      Reply
  • Steve Mangiameli
    December 3, 2015 1:35 pm

    I stopped reading when you announced no Powershell ;-( I have scripted so much of my life out at this point, I don’t “need” to get out of bed anymore; I do it now just to prevent bedsores. That module is under construction.

    I want to see the script that captures the perpetrator blowing out the tempdb log and notifies the Secret Service of his illicit counterfeit ring, sending me the video feeds of nearby cameras so I can watch the take down.

    Instead you just gave us XE and XML. Is this your version of the X games??? I guess it’s better than a trace; pfft.

    Seriously though, nice article!

    Reply
  • “it’s not meant to be a replacement for a full time monitoring solution”

    Which monitoring solutions make it easy to see why tempdb is growing?

    Reply
    • I’ve had good luck with SQL Sentry in the past. I believe Idera and Dell’s products can also collect similar metrics, but my experience with them is limited.

      Reply
      • Thanks. Just my luck, my employer licensed SolarWinds Database Performance Analyzer (formerly Confio Ignite) last year. I’ll see if they can figure out a custom metric for this.

        Reply
        • I also have Ignite on my more critical instances (yes, I still call it Ignite, can’t shake that habit) and I use in conjunction with Spotlight…to me, they complement each other quite well. I use Spotlight for mostly systems administration type activities, database/log file sizes, alerts for drive issues, CPU/memory monitoring, that sort of things, while Ignite/DPA is incredibly handy for troubleshooting what goes on actually within SQL Server….performance issues, troubleshooting time periods when there was a job failure, figuring out what statement is slowing down everything, etc.

          So, DPA/Ignite is an awesome tool and as much as I rolled my eyes at the very genericized name change, it really is a performance oriented tool. In this specific context it’s usefulness would be less about tempdb monitoring, and more about being able to drill into what queries were running in a very specific timeframe, down to the minute/second, and then figuring out which ones were heavy hitters in tempdb, and who or what was running them.

          Reply
  • “Most people have c:\temp, though.” They do?!

    Reply
  • Aaron Hurst
    July 22, 2016 3:23 pm

    I’ve modified the session to track file growth for all databases on my dev server. I’ve noticed that I have a few results where the QueryText column is NULL. Can you help explain why this is?

    –My session create script
    CREATE EVENT SESSION [FileGrowth] ON SERVER
    ADD EVENT sqlserver.database_file_size_change (
    ACTION ( sqlserver.client_hostname, sqlserver.database_id,
    sqlserver.session_id, sqlserver.sql_text )
    WHERE [session_id] > ( 50 ) )

    ADD TARGET package0.event_file ( SET filename = N” ,
    max_file_size = ( 10 ) ,
    max_rollover_files = ( 10 ) )

    WITH ( MAX_MEMORY = 4096 KB ,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
    MAX_DISPATCH_LATENCY = 1 SECONDS ,
    MAX_EVENT_SIZE = 0 KB ,
    MEMORY_PARTITION_MODE = NONE ,
    TRACK_CAUSALITY = ON ,
    STARTUP_STATE = ON );
    GO

    Thank you!

    Reply
  • I have found this useful but given best practice is to pre-grow and cap the tempdb datafiles, I have found it limiting.
    I am interested in knowing what is using all my tempdb and would like to associate the sql with it.
    Maybe XE is not the approach for finding sql hogging tempdb.

    Reply
    • Not sure what you mean, Owen. This captures the SQL that caused the growth event. Is there something else you’re looking for?

      Reply
  • I suppose I am, I like the detail given by your XE setup and I had hoped to use it to monitor what would want to extend my tempdb data files . But thinking about it, it might not be the last transaction that might be my problem, so I thought it would be useful to track what is writing to tempdb at any given time. But of course, this is something different to what you proposed originally.

    Reply
  • Thanks Erik … I’ve run the script, which started the monitoring session… I left it for a while and I got 354 rows with all the fields populated except the most important ones: GrowthFile, DBFileType, SizeChangeInKB, TotalFileSizeInKB, and DurationInMS …. I have filled a #temp table from a query that orders around 1.5 million rows, but all the same … the mentioned fields are all NULL.
    What does that mean? did I do anything wrong?!

    Reply
    • Not sure offhand. Which version of SQL Server are you on?

      You can always just run this part of the script to get the XML, and see if those fields are populated there.

      SELECT CAST([event_data] AS XML) AS [TargetData]
      FROM [sys].[fn_xe_file_target_read_file](‘c:\temp\publictoilet*.xel’, NULL, NULL, NULL)

      Reply
      • The SQL Server Version is:
        Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64)
        Oct 20 2015 15:36:27
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

        And when running the query in your reply, the resulted XMLs contain no tags or attributes of the columns I’ve mentioned in my question.

        And the login I’m querying with is sysadmin.

        Reply
  • Can someone help to add Data file growth on the result query?
    I added “ADD EVENT sqlserver.databases_data_file_size_changed,” but unable to query to see it !!

    Thanks

    Reply
  • I believe you should track sqlserver.databases_log_file_size_changed rather than sqlserver.databases_log_file_used_size_changed, otherwise you’ll end up logging any space changes INSIDE the log file.

    Reply
  • Thanks , Nice and clean. But why time Growth time has time format like 2018-11-17T19:12:19.198Z?

    Reply
    • That’s a perfectly normal date/time format.

      Reply
      • Brent@ i mean the server shows EST time when i do SELECT GETDATE() and extended events return the timestamp in UTC format. Is this some target event in XE causing a different time format compared to one on SQL server showing in EST?

        Reply
        • I came up with adding

          dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), GrowthTime) as LocalTimeStamp and it worked now. seems some issue within xE storing as UTC may be

          Reply
  • Jeremy Bennett
    January 10, 2019 7:49 am

    I’m researching using EE to alert us when our static tempdb allocation (multiple files) reaches over a certain percent usage/threshold. This way we can see processes that are using too much tempdb space for too long OR multiple processes running at the same time using up too much tempdb space combined. Our tempdb files are static with no growth allowed. We usually reserve 80% of the drive for them. But with our data growth we are seeing we need to also expand our tempdb drive OR asses our queries.

    Reply
  • […] set about doing some googling and stumbled across a great post over on Brent Ozar called Tracking tempdb growth using Extended Events written by Eric Darling, the only problem with this solution was that it made use of custom […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}