none
TEMPDB Mdf growing unexpectedly

    Question

  • MDf Data file  for tempdb grew to 130 GB yesterday on one of the servers . We recreated it by shutting SQL down .However , again it has assumed 120 GB . We cannot go on restarting the server indefinitely .What can be the reason for this . Is it a known issue for SQL Server 2005 SP2 ??? What is the permanent solution for this ?


    Pratibha Nandwana ------------------ Please mark solved if I've answered your question.
    Wednesday, July 22, 2009 7:01 AM

Answers

  • There are many uses of tempdb data.  The example in the previously referenced SQL Server Storage Engine article http://blogs.msdn ..com/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-trou bleshooting-out-of-space.aspx shows a large hash join that requires tempdb space for internal objects.  Other significant tempdb consumers include temp tables and variables plus the version store.
     
    Note that query and index tuning may help reduce tempdb space requirements.  The query in the article can help identify the problem ones.
     

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    "Prati" wrote in message news:aeb0d5d0-fa09-404 e-8bb4-d703e249f00e...

    Thanks,I will try to run it when problem is occuring  . However what I do not understand is why is MDF file growing . If it was Ldf , i could understand . But why MDF of Tempdb ??


    Pratibha Nandwana ------------------ Please mark solved if I've answered your question.
    Wednesday, July 22, 2009 12:23 PM
    Moderator

  • If ur tempdb grew to 130 GB and u see 99 percent free space, i would just shrink to reclaim the space.

    nik

    Shrinking databases of any kind on a regular basis is asking for bigger problems down the road.  First the database is going to have to grow again, the next time whatever operation that caused it to grow reoccurs which impacts performance every time the auto-growth kicks in, and second it leads to file system fragmentation which requires special tools to defragment, or that you shutdown your SQL Server services while the windows defragmenter runs.  The solution to the problem can be found using the troubleshooting document that Dan references above.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, July 22, 2009 5:46 PM
    Moderator
  • Prati,

    130 GB for tempdb is a LOT of space. I would try to identify the root cause of this, instead of patching up by server re-start. Look for cross join, hash joins or missing join conditions etc. Some of these diagnostic queries have to be run at the time of the issue happening and it may NOT always feasible. I had a issue with sort warnings on my server and I use below methodology to identify the root cause of the problem. You could use the same method to look for 'tempdb data file autogrow' event to track exactly what SQL statements contributed to this problem. Use *extreme* caution to setup the profiler with minimum events and columns otherwise, this action can cause overhead to the server.

    Tracking & Resolving Sort Warnings in SQL Server

    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Wednesday, July 22, 2009 5:48 PM
    Moderator

All replies

  • Take a look at the below article by Sunil. Take a look at the queries and their execution plan and fine tune the query:
    http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

    -Deepak
    Deepak | Mark the answers if it helps to solve your problem |
    Wednesday, July 22, 2009 7:12 AM
    Moderator
  • Thanks,I will try to run it when problem is occuring  . However what I do not understand is why is MDF file growing . If it was Ldf , i could understand . But why MDF of Tempdb ??


    Pratibha Nandwana ------------------ Please mark solved if I've answered your question.
    Wednesday, July 22, 2009 7:24 AM
  • Pls use sql profiler trace and activity monitor to findout activities running on server.
    Wednesday, July 22, 2009 8:33 AM
  • There are many uses of tempdb data.  The example in the previously referenced SQL Server Storage Engine article http://blogs.msdn ..com/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-trou bleshooting-out-of-space.aspx shows a large hash join that requires tempdb space for internal objects.  Other significant tempdb consumers include temp tables and variables plus the version store.
     
    Note that query and index tuning may help reduce tempdb space requirements.  The query in the article can help identify the problem ones.
     

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    "Prati" wrote in message news:aeb0d5d0-fa09-404 e-8bb4-d703e249f00e...

    Thanks,I will try to run it when problem is occuring  . However what I do not understand is why is MDF file growing . If it was Ldf , i could understand . But why MDF of Tempdb ??


    Pratibha Nandwana ------------------ Please mark solved if I've answered your question.
    Wednesday, July 22, 2009 12:23 PM
    Moderator
  • pratibha,

    i have the same problem in my Environment.in ou scenario, tempdb frows to 120 GB also , but after the job is complete, the space is not releases until the databse is shrunk but i see that 99 percent of the space is free. SO i just shrink the tempdb. dunno whther it is gud or bad,

    since you are on 2005, you are at an advantage to use DMVs to identify whats using ur tempdb

    http://technet.microsoft.com/en-us/library/cc966545.aspx article provides gud info, also read the dyanamic management views section, DMVs used to track tempdb usage.

    If ur tempdb grew to 130 GB and u see 99 percent free space, i would just shrink to reclaim the space.

    nik
    Wednesday, July 22, 2009 5:24 PM

  • If ur tempdb grew to 130 GB and u see 99 percent free space, i would just shrink to reclaim the space.

    nik

    Shrinking databases of any kind on a regular basis is asking for bigger problems down the road.  First the database is going to have to grow again, the next time whatever operation that caused it to grow reoccurs which impacts performance every time the auto-growth kicks in, and second it leads to file system fragmentation which requires special tools to defragment, or that you shutdown your SQL Server services while the windows defragmenter runs.  The solution to the problem can be found using the troubleshooting document that Dan references above.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, July 22, 2009 5:46 PM
    Moderator
  • Prati,

    130 GB for tempdb is a LOT of space. I would try to identify the root cause of this, instead of patching up by server re-start. Look for cross join, hash joins or missing join conditions etc. Some of these diagnostic queries have to be run at the time of the issue happening and it may NOT always feasible. I had a issue with sort warnings on my server and I use below methodology to identify the root cause of the problem. You could use the same method to look for 'tempdb data file autogrow' event to track exactly what SQL statements contributed to this problem. Use *extreme* caution to setup the profiler with minimum events and columns otherwise, this action can cause overhead to the server.

    Tracking & Resolving Sort Warnings in SQL Server

    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Wednesday, July 22, 2009 5:48 PM
    Moderator
  • Thanks much for correcting me on this, I thought it was a quick fix  without actually thinking about the consequences.

    I shall certainly use te link to identify the reasons for the tempdb usage. Unfortuantely we are on SQL server 2000 still and I dont think (Are there any tools other than the profiler?)
    to identify whats eating up tempdb.

    If a Database autogrows, is it mandatory to run a windows system defragmenter to defragment the database.


    Thanks again
    Nik
    Wednesday, July 22, 2009 10:14 PM