Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115

    Unanswered: Tempdb is Eating space Urgent!!!

    Hi All,

    Sql Server 7

    My tempdb is eating 1.27 gb of space of my d dive, and now only 10 mb is left in d drive . for this i stoped and started sqlserver but it didnt release much of space.Pls let me know is there any other way so that i can release some space from my tempdb.

    Waiting for reply

    TIA
    Adil

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Stop SQL Server. Delete TempDB. Delete templog.ldf. Start SQL Server.

    Go in to EM and right-click on TempDB. Select properties. Set an upper limit on file growth so that it can't eat more than 80% of your available drive space (taking into consideration other files that may be co-located on the same volume).

    It is possible that the above steps will not reset the size of Temp DB, but it should.

    Regards,

    hmscott

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by hmscott
    Stop SQL Server. Delete TempDB. Delete templog.ldf. Start SQL Server.
    You're kidding, right?

    http://www.sqlteam.com/forums/topic....chTerms=tempdb
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That does seem a bit extreme. You should be able to issue a

    DBCC ShrinkFile (n, s)

    command, where n is the fileid you want to shrink, and s is the target size. Before you do this, check to see if there are any open transactions holding tempdb at this size (issue dbcc opentran in tempdb). If no transactions are holding up space, then DBCC SHRINKFILE should deal with the immediate problem. Beware of tempdb growing again. You should probably set some limits on the filegrowth, then look for the root cause. Especially if this was an "overnight" growth of tempdb, which it sounds like it was.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Yes, perhaps it is a bit extreme; it should be used as a last resort.

    However, TempDB is recreated every time you start SQL server. When you issue DBCC SHRINKFILE, I believe that much of the activity takes place on TempDB. I've had varying results with SHRINKFILE (I don't much use it anymore for my large databases).

    Given the very small amount of space available to him on his TempDB drive, I thought this might bring about a faster resolution.

    Regards,

    hmscott

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Shrinkfile is an incredible pig if you need to shrink user databases. It is much better at just freeing up empty space. If you need to shrink a user database .MDF file, and you have data at the end of the file, SQL Server has to relocate that data somewhere. This causes indexes to be modified, transaction logs to grow, and probably puts a few locks on the tables being moved, just for good measure. Usually, I find it easier to move a few objects to a new filegroup, then do dbcc shrinkfile (n, truncateonly). Then bring back the objects from the new filegroup. Painful, but quicker, I think.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •