Post reply

tempdb growth

  • July 27, 2008 at 9:45 pm

    #154567

    I have a server on which there are a bunch of databases, basically used for a number of applications. There is a job that run dbcccheckalloc against all of the databases during which I see the error log throws the msg 'tempdb full'. When I check the tempdb properties, its the datafile which is 90% of the maz size it has been given. Its been set to auto growth but limited to a max size of 2300 mb due to smaller drive size. I can't restart SQL server to reduce the tempdb size. What is the best way to resolve this issue. The log file is 100mb and the overall db size is 2308 with available space around a 100mb. Thanks in advance.

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • July 27, 2008 at 11:00 pm

    #848956

    Shrink tempdb.Thats the only way to get rid of this

  • July 28, 2008 at 3:43 am

    #849015

    Shrinking a full database is a little pointless.

    Shrink releases unused space to the OS, if a database is full then there is no free space inside it, hence all shrink is going to do is use up lots of CPU and IO for very little gain. Plus it will mean that next time TempDB needs space it has to grow again. More CPU, more IO

    talentguy: Is it only the checkAlloc job that throws the error? CheckAlloc uses a fair bit of TempDB space in order to do the integrity checks. I would suggest that you don't run it while the systems are in use.

    If you are running it during a quiet time then it may be that it does need more space in tempDB than you have available. How big are the user database? Is there any chance of getting another/larger drives or creating a second file for TempDB on a drive with more free space?

    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 28, 2008 at 4:41 am

    #849058

    Thanks for the information GILA

  • July 28, 2008 at 5:26 pm

    #849520

    Gail thanks for the reply.As for ur question there are a bunch of DBCC's like CheckAlloc and CheckDB running against each of the 40 odd DB's on this server. At this point the DB has 2200 mb free space and a msx size of 2300. I have the mdf size restricted at 2200 mb( before it was 2000mb with auto growth enabled) and the log file of 100mb auto growth enabled. The space left on this drive is 4GB. I see the tempdb overflow error again today. Do I change the auto growth to unrestricted file size or what? But I dont want to take a chance with that. I want this DB's .mdf to be of reasonable size and should not overflow when running these commands...thnx guys!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • July 29, 2008 at 4:09 am

    #849653

    Thing is, CheckDB does use a lot of tempDB space. There's no way to get it to use less. I would not suggest unrestricted growth, disk out of space errors aren't pretty. Can you get more drive space in the server?

    Also, you don't have to run checkAlloc and CheckDB. CheckDB includes everything that checkAlloc does.

    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 29, 2008 at 4:41 am

    #849672

    Mr.Gail,I shall go for more disk space as you mentioned that too with restricted growth. Let me re-do the DBCC statements to include what is sufficient to serve my purpose with ease on the space.Thanks a Million Guys...!!! 😀

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • July 29, 2008 at 7:59 am

    #849802

    Hi there talentguy, I hope you have resolved your problem with the tempdb growth. I have had the similar problem last weekend and I executed some update statistics statement against some databases that was used extensively over that period of time. Thought I could give you some alternatives as well. Please note that I fully agree with what Gail has recommended you.

    [font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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