Post reply

Huge tempdb log file

  • October 28, 2016 at 6:33 am

    #331450

    in the wee hours of the morning, something blew up our tempdb log file to 170+GB, filling the disk. the sysadmins extended the disk and the growth stopped at 180GB, but the used amount of log never decreased (its 10 hours later now). the tempdb data file is 80GB and 99.97% free. how is it that the log file can be 180GB and still 98.3% full? see attached if you prefer a chart. how can i clear this and reduce the size of the log?

    thanks!

  • October 28, 2016 at 6:54 am

    #1909342

    i've seen things like this, where backups are scheduled at 4am, and then so is an reindexing job AND a statistics job.

    all at 4am. once all the sorting in tempdb is done, the space is freed up again

    bloat due to reindexing is common, so check if you have other jobs at the same time that can contribute to the issue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 28, 2016 at 7:17 am

    #1909348

    no maintenance jobs were running but there are 60-some-odd applications running on this server and some do overnight processing, batch loadings etc. . i don't have any direct visibility on the application processing side, but no one has reported any errors. it seems that everything just hung until the disk was increased.

    now i've got a 180GB tempdb log file that i cannot shrink. it still reports 99% full and i'm going on 12 hours later. the log should truncate automatically but it seems to be hung at 99% full.

  • October 28, 2016 at 12:37 pm

    #1909419

    You should be able to shrink the log file for tempdb just as you would normally as any other database:

    USE [tempdb]

    GO

    DBCC SHRINKFILE ('templog', 2048)

    GO

    If you have tried this and it won't shrink, you could run queries to see exactly what is filling it. For your immediate concern, space, you may need to clear the cache first - DBCC FREEPROCCACHE GO (warning if this is production) as this clears the procedure cache and will cause re-compiles, but, sometimes, this is the only thing which makes it work. From BOL - Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

  • October 28, 2016 at 4:00 pm

    #1909444

    most likely cause is a long-running transaction. You can set up a variety of things to watch for that.

    If the consumption has been removed (dbcc sqlperf(logspace) may show it) you should be able to shrink. Depending on how large you want the log file to be when you are done you should read Kimberly Tripp's two seminal blog posts on optimal tlog stuff.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • October 29, 2016 at 6:28 am

    #1909475

    Check for open transactions.

    _____________
    Code for TallyGenerator

  • October 29, 2016 at 6:35 am

    #1909476

    At this point, it may just be easier and faster to make sure the initial settings for TempDB are correct and then bounce the SQL Server Service.

    --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)

  • October 29, 2016 at 9:10 am

    #1909481

    Something happened to blow out your tempdb. You may not know what it was yet, but you can be sure that it didn't just happen by itself. What you need to find is what happened. If you don't, the same thing will happen again. It could be a single event or a combination of several things running at once (like Lowell said) but you have to figure it out so you can prevent it.

    Once you have your answer, Jeff's right - verify your tempdb's initial size and bounce the service. However, before you do that, make sure to follow Sergiy's advice and check for transactions that haven't completed yet. If you have anything open and you restart your service, the transaction will probably need to run again. After all, it ran for a reason.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 29, 2016 at 11:27 am

    #1909490

    Ed Wagner (10/29/2016)

    Something happened to blow out your tempdb. You may not know what it was yet, but you can be sure that it didn't just happen by itself. What you need to find is what happened. If you don't, the same thing will happen again. It could be a single event or a combination of several things running at once (like Lowell said) but you have to figure it out so you can prevent it.

    Once you have your answer, Jeff's right - verify your tempdb's initial size and bounce the service. However, before you do that, make sure to follow Sergiy's advice and check for transactions that haven't completed yet. If you have anything open and you restart your service, the transaction will probably need to run again. After all, it ran for a reason.

    Considering the size of the blowout, I'm thinking that one should be able to find the cause rather quickly simply by looking for code that uses DISTINCT to make up for accidental Cartesian Products in the form of many-to-many joins caused either by insufficient criteria or a gross misunderstanding of the underlying tables, or both.

    That's why I suggested bouncing the service now. Once that's done, monitor for "excessive" growth and log it to a table.

    P.S. If there's still an open transaction, trace the spid to the machine and introduce the user to what an angry DBA looks like. 😉

    --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)

  • October 31, 2016 at 4:45 am

    #1909570

    Thanks guys. FREEPROCCACHE didn't help. In the end I had to restart the SQL service to clear the problem.

    No clarity on what caused the problem. I've setup alerts for tempdb log growth so now I'll know ahead of a disk full situation.

    thanks!

  • October 31, 2016 at 5:29 am

    #1909576

    JarJar (10/31/2016)

    Thanks guys. FREEPROCCACHE didn't help. In the end I had to restart the SQL service to clear the problem.

    No clarity on what caused the problem. I've setup alerts for tempdb log growth so now I'll know ahead of a disk full situation.

    thanks!

    That's pretty much what I'd have done. I'd likely go one step further and have some code run to see what's actively using TempDB when the growth alert fires.

    --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)

  • October 31, 2016 at 7:32 am

    #1909614

    Jeff Moden (10/31/2016)

    JarJar (10/31/2016)

    Thanks guys. FREEPROCCACHE didn't help. In the end I had to restart the SQL service to clear the problem.

    No clarity on what caused the problem. I've setup alerts for tempdb log growth so now I'll know ahead of a disk full situation.

    thanks!

    That's pretty much what I'd have done. I'd likely go one step further and have some code run to see what's actively using TempDB when the growth alert fires.

    i use sp_whoisactive but at the time (several hours after the disk full message) it did not report anything with high tempdb allocations. i presume the process finished before i got to it. afterwards, with the full tempdb log still hanging, there were very little tempdb allocations.

    do you have something better than sp_whoisactive? 🙂

    thanks!

  • October 31, 2016 at 7:58 am

    #1909626

    JarJar (10/31/2016)

    Jeff Moden (10/31/2016)

    JarJar (10/31/2016)

    Thanks guys. FREEPROCCACHE didn't help. In the end I had to restart the SQL service to clear the problem.

    No clarity on what caused the problem. I've setup alerts for tempdb log growth so now I'll know ahead of a disk full situation.

    thanks!

    That's pretty much what I'd have done. I'd likely go one step further and have some code run to see what's actively using TempDB when the growth alert fires.

    i use sp_whoisactive but at the time (several hours after the disk full message) it did not report anything with high tempdb allocations. i presume the process finished before i got to it. afterwards, with the full tempdb log still hanging, there were very little tempdb allocations.

    do you have something better than sp_whoisactive? 🙂

    thanks!

    I don't know. I don't use sp_WhoIsActive because I wrote my own a long time ago and so actually know little about Adam Machanic's widely used and deservedly acclaimed code BUT, that (sp_WhoIsActive) would be a good thing have the alert execute and capture the output into one of your "DBA" tables in your DBA database. I do the same with "Blocking". When an alert for block fires, it executes my version of what sp_WhoIsActive does and saves the output (along with clickable code running and, usually, the intended execution plan) and the blocking info.

    Such a thing might would likely work very well on TempDB growth alerts, as well.

    --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)

  • October 31, 2016 at 8:44 am

    #1909655

    Jeff Moden (10/31/2016)

    JarJar (10/31/2016)

    Jeff Moden (10/31/2016)

    JarJar (10/31/2016)

    Thanks guys. FREEPROCCACHE didn't help. In the end I had to restart the SQL service to clear the problem.

    No clarity on what caused the problem. I've setup alerts for tempdb log growth so now I'll know ahead of a disk full situation.

    thanks!

    That's pretty much what I'd have done. I'd likely go one step further and have some code run to see what's actively using TempDB when the growth alert fires.

    i use sp_whoisactive but at the time (several hours after the disk full message) it did not report anything with high tempdb allocations. i presume the process finished before i got to it. afterwards, with the full tempdb log still hanging, there were very little tempdb allocations.

    do you have something better than sp_whoisactive? 🙂

    thanks!

    I don't know. I don't use sp_WhoIsActive because I wrote my own a long time ago and so actually know little about Adam Machanic's widely used and deservedly acclaimed code BUT, that (sp_WhoIsActive) would be a good thing have the alert execute and capture the output into one of your "DBA" tables in your DBA database. I do the same with "Blocking". When an alert for block fires, it executes my version of what sp_WhoIsActive does and saves the output (along with clickable code running and, usually, the intended execution plan) and the blocking info.

    Such a thing might would likely work very well on TempDB growth alerts, as well.

    nice idea. thanks.

    seems like my alerts are not working as expected. i keep getting alerts for log growth but, the alert history shows a value of 104 occurrances in the history, but the log is the same size. i've cleared the history many time and in perfmon i don't see any log growths. any idea which i'm getting false positives?

  • October 31, 2016 at 8:49 am

    #1909658

    Pic attached of alert setup.

Viewing 15 posts - 1 through 15 (of 20 total)

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