Post reply

File group is running out of space frequently.

  • September 1, 2015 at 2:16 pm

    #304031

    Hello,

    We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.

    Error:

    The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.

    Thanks a lot for your help in advance.

    tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB

    templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited

  • September 1, 2015 at 2:51 pm

    #1823815

    First fix that growth increment. Nothing should use % growth. Set the growth increment to a sensible size. Second, the limit on the file size means that if something needs more than that, it'll fail rather than letting TempDB grow.

    Other than that, you're going to have to identify what's running at the time the alerts fire and investigate whatever you find.

    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

  • September 1, 2015 at 2:58 pm

    #1823821

    Check your code for reindexing....does it use SORT_IN_TEMPDB?

    -SQLBill

  • September 1, 2015 at 3:00 pm

    #1823823

    Thanks for your prompt response. I understand that we can't limited to growth. But, this is how they implemented tempdb. According to technet.microsoft if we have 200 MB or more then filegrowth should be 10%. Can you please suggest what would be the advisable size?

    https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    Thanks again for your help.

  • September 1, 2015 at 3:09 pm

    #1823826

    EasyBoy (9/1/2015)

    Thanks for your prompt response. I understand that we can't limited to growth. But, this is how they implemented tempdb. According to technet.microsoft if we have 200 MB or more then filegrowth should be 10%. Can you please suggest what would be the advisable size?

    https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    Thanks again for your help.

    This is one case where you should ignore Technet! Do as Gail says!


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 1, 2015 at 4:26 pm

    #1823832

    SQLBill (9/1/2015)

    Check your code for reindexing....does it use SORT_IN_TEMPDB?

    The rebuild starts 20 minutes after the alert, so unless there's a second rebuild job somewhere, it's not from rebuilds.

    Could be from a CheckDB, but that's guessing.

    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

  • September 1, 2015 at 4:32 pm

    #1823834

    EasyBoy (9/1/2015)

    Thanks for your prompt response. I understand that we can't limited to growth. But, this is how they implemented tempdb.

    Ok, but that doesn't stop you from reconfiguring it correctly

    According to technet.microsoft if we have 200 MB or more then filegrowth should be 10%. Can you please suggest what would be the advisable size?

    Another stupid recommendation from MS, probably copy-pasted from SQL 7 docs. I wish they'd go through the docs and take the ancient stuff out.

    Not %, never. Because it's a not a steady amount, not a steady time to grow. You need predictability, not files growing in larger chunks the larger they get.

    Identify how large TempDB needs to be. Set the initial size to that. Then set the increment for a sensible size based on what you just set the initial size (you wouldn't set a 1MB growth for a 200GB file, you wouldn't set a 50GB growth for a 200MB file) and based on how long a grow operation is allowed to take (test and time them)

    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

  • September 2, 2015 at 7:16 am

    #1823967

    Thanks all for your reply.

    I will check with senior person if we can remove unlimited auto-growth option and set something about 400-500 MB in place of 10%.

    I would also like to monitor the activity that is happening between 1:00 AM and 2:00 AM. But i will not online during that time to monitor the activities. What is the best way to capture the activities that hamper the performance of tempdb?

    Any script that i can run through the job and generate the report and i can check next day morning?

    Thanks a lot all for your help.

  • September 2, 2015 at 7:33 am

    #1823982

    EasyBoy (9/2/2015)

    I will check with senior person if we can remove unlimited auto-growth option and set something about 400-500 MB in place of 10%.

    Remove the limited autogrowth, you mean?

    I would also like to monitor the activity that is happening between 1:00 AM and 2:00 AM. But i will not online during that time to monitor the activities. What is the best way to capture the activities that hamper the performance of tempdb?

    Server-side trace or extended events. On 2008 I'd go with server-side trace (and Grant will reply in a few moments telling me I'm wrong :-)). You can use the Profiler GUI to create the trace definition, but do not use the profiler GUI against a production system. Take the trace definition generated, specify a file name and location (on a fast, local drive, not a network share), add an end time and increase the max file size from 5MB to something large enough to hold the workload (I don't know how busy your server is), and then put the definition in a job to run at the start of the period you want to monitor.

    There's a discussion of trace scripts here: https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    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

  • September 2, 2015 at 8:17 am

    #1824000

    Alvin Ramard (9/1/2015)

    EasyBoy (9/1/2015)

    Thanks for your prompt response. I understand that we can't limited to growth. But, this is how they implemented tempdb. According to technet.microsoft if we have 200 MB or more then filegrowth should be 10%. Can you please suggest what would be the advisable size?

    https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    Thanks again for your help.

    This is one case where you should ignore Technet! Do as Gail says!

    +1 Billion to both.

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

  • September 2, 2015 at 2:12 pm

    #1824141

    GilaMonster (9/2/2015)

    EasyBoy (9/2/2015)

    I will check with senior person if we can remove unlimited auto-growth option and set something about 400-500 MB in place of 10%.

    Remove the limited autogrowth, you mean?

    I would also like to monitor the activity that is happening between 1:00 AM and 2:00 AM. But i will not online during that time to monitor the activities. What is the best way to capture the activities that hamper the performance of tempdb?

    Server-side trace or extended events. On 2008 I'd go with server-side trace (and Grant will reply in a few moments telling me I'm wrong :-)). You can use the Profiler GUI to create the trace definition, but do not use the profiler GUI against a production system. Take the trace definition generated, specify a file name and location (on a fast, local drive, not a network share), add an end time and increase the max file size from 5MB to something large enough to hold the workload (I don't know how busy your server is), and then put the definition in a job to run at the start of the period you want to monitor.

    There's a discussion of trace scripts here: https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    Thanks Gail. I mean to remove Limited and set that to Unlimited with 400-500 MB of size.

    I read your link and it's really helpful.

    Thank you very much for your help.

  • September 3, 2015 at 10:08 am

    #1824374

    I had a talk with senior person today. And he said they configured Limited file-growth to Unlimited. But at that time they were getting Drive space full alerts. And now they changed to limited growth and getting File group is running out of space alerts. I think as Gail suggested best thing is to setup trace file to check what is running against temp during that time.

    Thanks all for your input.

  • September 3, 2015 at 5:06 pm

    #1824510

    EasyBoy (9/3/2015)

    I had a talk with senior person today. And he said they configured Limited file-growth to Unlimited. But at that time they were getting Drive space full alerts. And now they changed to limited growth and getting File group is running out of space alerts. I think as Gail suggested best thing is to setup trace file to check what is running against temp during that time.

    Thanks all for your input.

    I'd bet credits to Navy beans that it will be some query that using a DISTINCT in it to overcome duplicate results in the return. The duplicates are usually caused because of a misunderstanding about the data and people end up doing accidental many-to-many joins (which are formed in TempDB usually as hash tables), which are also known as "accidental Cross-Joins.

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

Viewing 13 posts - 1 through 12 (of 12 total)

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