Post reply

Handling Tempdb space full situtation?

  • July 23, 2008 at 6:52 am

    #846991

    I believe the latter of those two is referred to as "certifiable", but I pick nits...

    Steve

    (aka smunson)

    :):):)

    GilaMonster (7/23/2008)

    Jeff Moden (7/23/2008)

    Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...

    Depends. Do you mean certified as in qualified or certified as in insane?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • July 23, 2008 at 6:53 am

    #846992

    smunson (7/23/2008)

    Sounds to me like an exam question or homework. Otherwise why ask for 2 different answers. I would suggest that the OP read their course material and find the answers that way... They will likely learn more that way.

    Steve

    (aka smunson)

    :):):)

    Sounds spot-on to me.

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

  • July 23, 2008 at 6:55 am

    #846996

    Jeff Moden (7/23/2008)

    GilaMonster (7/23/2008)

    On my main system, TempDB is 6 files of 8 GB each.

    Now THAT sounds like a fun system to be on and that the DBA (you, Gail?) might actually have his/her ducks lined up.

    It is a fun system. It's stable now, after a hell of a lot of work, but it still tends to throw really interesting problems from time to time. I'm almost going to miss it.

    I don't admin the machine, but the tempDB recommendation was mine.

    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

  • July 23, 2008 at 7:21 am

    #847019

    Thsoe DBA's may have been certifiable, but definitely weren't certified. They also wanted to start archiving data when database size reached 1GB, so I tend to believe they were mainframe or midrange refugees who had spent their careers working on resource restricted systems.

    On the systems I support, we've never had those sorts of issues. We did have to spend some effort allocating data to keep everything on the 270GB partitions we were given. That number was chosen because it was the maximum amount of data that could be restored within the data recovery SLO guidelines. Going to SAN has eliminated that issue nicely.

  • July 24, 2008 at 2:49 am

    #847581

    Tempdb suppose to be growing until there is no more space available.

    So there are two options:

    1. some definition are wrong - check if tempdb deifintions are set to autogrow.

    2. you dont have any more disk space left!

  • July 24, 2008 at 4:56 am

    #847628

    nisan.al (7/24/2008)

    Tempdb suppose to be growing until there is no more space available.

    So there are two options:

    1. some definition are wrong - check if tempdb deifintions are set to autogrow.

    2. you dont have any more disk space left!

    I agree that it should be allowed to grow a lot, but there are some practical limits. Depending on the type of disk system and where TempDB was physically located, I may never let it consume all the hard disk space because the operating system wouldn't have any headroom. And I'd also never let it consume a Tera-byte of free space even if I had it... 😉

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

  • July 24, 2008 at 4:58 am

    #847629

    rames.net (7/21/2008)

    How can we handle the situation if the tempdb space was full?

    reply with more than 2 diff answers?

    Your turn, Pal... how about some feedback. We need at least 2 diff feedbacks from you. 😉

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

  • July 24, 2008 at 5:18 am

    #847635

    You are right, to put a limitation is always good.

    But by the way, tempdb doesn't have to be in the same drive as the operation system you can change the path to anything you like....

  • July 24, 2008 at 6:03 am

    #847655

    nisan.al (7/24/2008)

    Tempdb suppose to be growing until there is no more space available.

    Not sure that's the situation that I'd want to be in.....Proactive monitoring of your resources and system activities SHOULD ensure that you're never in that situation. IMO

    -- You can't be late until you show up.

  • July 24, 2008 at 6:08 am

    #847661

    nisan.al (7/24/2008)

    You are right, to put a limitation is always good.

    But by the way, tempdb doesn't have to be in the same drive as the operation system you can change the path to anything you like....

    Yep... I know... but lot's of folks that I've run into don't have even a multi-disk desktop box that they're running their business on.

    I also agree with what Terry implies... growth of TempDB should never take one by surprise... it should be a planned exercise.

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

  • July 24, 2008 at 7:06 am

    #847700

    I'd really love to have enough partitions in my SAN to have more than one file for my TempDB. @sigh. Gail, you're making me jealous. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 24, 2008 at 7:21 am

    #847714

    So, is it good practice for any database/trans log file to restrict the growth to a little less than what your disk allocation is? (say you have a dedicated disk for each database and trans log) Does anyone keep the unrestricted growth option set?

  • July 24, 2008 at 7:26 am

    #847719

    Angie,

    We actually have some DBs with UNRESTRICTED on them, but on the other hand, we also have several Alerts and jobs set up to monitor Hard Drive space. When space gets to below 25% of capacity, we go out and clean up the drives. A help desk ticket is generated, escalating the issue up to corporate IT when space gets below 10% (which is rare now that we DBAs have our 25% alerts).

    So, if you're going to do UNRESTRICTED, make sure you have enough monitoring going on that you catch any problems before they become problems.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 24, 2008 at 7:35 am

    #847734

    Brandie Tarvin (7/24/2008)

    Angie,

    We actually have some DBs with UNRESTRICTED on them, but on the other hand, we also have several Alerts and jobs set up to monitor Hard Drive space. When space gets to below 25% of capacity, we go out and clean up the drives. A help desk ticket is generated, escalating the issue up to corporate IT when space gets below 10% (which is rare now that we DBAs have our 25% alerts).

    So, if you're going to do UNRESTRICTED, make sure you have enough monitoring going on that you catch any problems before they become problems.

    Ok, so what merits keeps a db unrestricted rather than restricted?

  • July 24, 2008 at 7:40 am

    #847742

    Brandie Tarvin (7/24/2008)

    I'd really love to have enough partitions in my SAN to have more than one file for my TempDB. @sigh. Gail, you're making me jealous. @=)

    They're not on separate drives. Just separate files.

    Back before the upgrade to 2005 we were encountering lots and lots of latch errors on 2:1:3 (allocation page of tempDB). The solution for that is to split tempDB into multiple files to allow allocations to be done from multiple files concurrently. They only need to be moved to separate drives if there are IO bottlenecks, which I don't have on TempDB

    I'd also love to have more SAN partitions. Isn't a reality

    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

Viewing 15 posts - 16 through 30 (of 57 total)

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