Post reply

tempdb log space usage troubleshooting

  • July 21, 2015 at 11:15 am

    #398777

    I received an alert from our alerting system that log space for tempdb is used over 60%.

    My question is what options do I have to troubleshoot and fix it? If it would be a regular user database, I would check for log_reuse_wait_desc in sys.databases, run another log backup, and maybe some other things. But what I can do with tempdb?

    Thanks

  • July 21, 2015 at 11:26 am

    #1813921

    I think, TempDB checkpointing occurs at 70%.

    You can also try manually running the Checkpoint several times on TempDB and see if that helps.

    If it still doesnt help, I suppose there are Active Transactions that are performing a great deal of operations utilizing the TempDB. Until those transactions are running, the log will continue to grow.

  • July 21, 2015 at 11:41 am

    #1813924

    60% may not be big deal. You can keep an eye on it using DBCC SQLPERF(LOGSPACE).

    If you consistently see that the tempdb log is filling you will want to see what it causing it to fill up and address the issue from there.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • July 21, 2015 at 11:56 am

    #1813933

    Thanks a lot, Vincy. Checkpoint greatly helped me. I was running it just once, and it was enough to bring it to 0%, though it took almost 2 min.

    Alan: how can I find what causing it to fill?

    Thanks

  • July 21, 2015 at 12:48 pm

    #1813947

    SQL Guy 1 (7/21/2015)

    Thanks a lot, Vincy. Checkpoint greatly helped me. I was running it just once, and it was enough to bring it to 0%, though it took almost 2 min.

    Alan: how can I find what causing it to fill?

    Thanks

    Take a look at this link: MSDN: Catch what is causing tempdb to grow? and this one:

    How to identify which query is filling up the tempdb transaction log?

    It depends on your environment and there is a science to it. Querying DMV's or using Extended Events are the approaches that I am comfortable with (Extended Events is available on SQL Server 2012+ unfortunately so you could not use that).

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • July 22, 2015 at 8:14 am

    #1814123

    Thanks for the links, Alan. Especially for the 2nd one. Interestingly, it in turn links back to SSC site to a great script written by Gianluca Sartori: http://www.sqlservercentral.com/scripts/tempdb/72007/

    About external events, they are present in 2008 R2, it just does not have a GUI, but it has whole set of DMV views, they all start with sys.dm_xe.... I tried to learn it couple of years ago, but this subject is way too complex.

Viewing 6 posts - 1 through 5 (of 5 total)

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