Post reply

the tempdb.mdf has grown suddenly.

  • January 20, 2009 at 2:07 am

    #196302

    Hello,

    Someone can explain to me why the tempdb.mdf has grown so much lately?

    And what can I do in order to reduce it?

    I’ve already shrink the data but it was helpful.

    P

    pls, advice.

    Thanks!

    Deby

  • January 20, 2009 at 2:19 am

    #929548

    Check for temp table creation in your SPs. Temp database size also increase for some sorting/aggration operations.

    Regards,
    Nitin

  • January 20, 2009 at 2:27 am

    #929550

    TempDB is used by the server for operations such as storing temporary and work tables, ordering records, storing records’ versions, etc’. If the TempDB is growing, then the server needs more space in it. If you did a one time operation that caused the Tempdb to grow, then you can shrink it, but if the Tempdb got his size during normal work, then you shouldn’t shrink because after a while it will just get to this size again. If the TempDB’s size really bothers you, you can try and check if there are procedures\applications that create large temporary tables or order big amount of data or have to use big working tables in the query plan.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 20, 2009 at 4:23 am

    #929592

    Thanks Adi,

    I don't know why it grown so much, so for I think that the first step is to try to shrink it and see what happened.

    I have some instructions in order to shrink this data in special, but it’s hard to me to understand what to do, do you have maybe some simple explanation about this issues?

    Thanks,

    Deby

  • January 20, 2009 at 4:53 am

    #929603

    You can shrink the database by using dbcc shrinkfile command. BOL has details about it, but if you don’t have any space problems on the disk, maybe there is no reason to shrink the tempdb? Notice that if you’ll shrink it and the server will need to expend the file again, the file will be expended and the process that will need the extra space will have to wait until the file finishes to expend. By the way what is the size of the data file?

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 20, 2009 at 9:18 am

    #929773

    Ady,

    The size is about 32GB and I don't have space in the disk for that .

    I'll try to shrink the db and I’ll look after it.

    Some people told me also that i supposed to use dbcc shrinkfile command for this actions.

    But i’d appreciate if you can advice me the best way to backup this for the case that thing don’t result like I’m expecting?

    Thanks,

    Deby

  • January 20, 2009 at 10:02 am

    #929789

    You may want to look into moving tempdb to its own disk drive (or set of disk drives). You can find instructions on moving tempdb in BOL. Shrinking tempdb may give you back some space, but if SQL Server needs more space again it will have to grow tempdb again and that will also impact your systems performance as mentioned earlier in this thread.

  • January 20, 2009 at 10:18 am

    #929800

    There is no need to do a backup because this is the TempDB. Unlike the rest of the databases, each time that the server is started, it creates the TempDB from scratch. If something goes wrong your and your TempDB won’t be usable, the only option that you’ll have is to restart SQL Server and a new TempDB will be created (but the chances that something will go wrong when you shrink the file are very low).

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 20, 2009 at 10:58 pm

    #930102

    may we know the size of your dbs?

  • January 21, 2009 at 8:13 am

    #930378

    Thanks Adi,

    I shirked the data but I’m afraid that i'll need to move it it a new drive because is till bigger for the D drive.

    Thanks anyway.

  • January 21, 2009 at 8:19 am

    #930385

    Hi

    As every one suggested this is a temp storage, if there is a increase in space on temp DB, you ran out of space and DBCC command is not helpful, then restart the SQL instance ( this is not advised usually),

    there are many reasons for tempdb growth,Check if there are any one using SQL server in a drastic manner ( like using bulk operations on temp table), check any SP that has been recently created that will affect this opearions, it more often human error. 😀

Viewing 11 posts - 1 through 10 (of 10 total)

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