Tempdb

  • The size of tempdb in my sever is getting incresaed .... it is about 11 Gb ...how can i reduce the size or shrink without having downtime???

    can we change the path by using modify file

    use master

    go

    Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')

    go

    Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')

    go

    or

    use master

    ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 8)

    --Desired target size for the data file

    ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 1)

    --Desired target size for the log file

    shud i restart the sevice for the above to get it impacted

    and which 1 should i follow to reduce or you have any other recommendations from your side????

  • Well you have one batch of commands to alter the path and file location for tempdb and the other is to modify the data and log file size. For changes to take effect on the path, you would need to restart SQL server.

    As for the growth, if you are concerned about the growth and size of your tempdb, you should find out what is causing the growth. Fix that and then look into altering the size of the files.

    For info on methods to find the root cause of the growth, check out this article -

    http://www.sqlservercentral.com/articles/Log+growth/69476/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi

    For the ALTER DATABASE TEMPDB ... command to change the location and size of TEMPDB requires SQL Server Restart.

    Shrinking the Tempdb using DBCC commands requires that TEMPDB doesn't have any active connections. This needs SQL Server to be started in Singe User mode.

    http://support.microsoft.com/kb/307487

    Thank You,

    Best Regards,

    SQLBuddy

  • This is for your optional,

    you just restart the services. New tempdb will created automatically.

    Thanks

    Balaji.G

  • balaji.ganga (7/27/2010)


    This is for your optional,

    you just restart the services. New tempdb will created automatically.

    Thanks

    Balaji.G

    True. Restarting the services will recreate the tempdb. That should only be done during a maintenance window or if you are experiencing an outage already.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Resize the files all you want, but unless you find the root of the problem, it's just going to grow back to the same size. And now every time SQL Server has to grow the file, it will cause a performance hit. If you're going to shrink the file before finding the root cause, at least set your autogrowth to something like 1G increments.

    Really though, follow Jason's suggestion and find the root cause. Personally, I have a stored procedure that logs the sizes and usage of all of my database files every hour. I then built a report to chart out this history. I then set up a data-driven subscription to alert me if any of the file sizes change, or if a database is using over 80% of it's available space. It took me about a day to set this up, but it is worth it. I am never suprised by database file growth. When there is a problem, I am able to pin-point the hour in which it occurred. Usually this is enough to give me a really good idea of what caused it.

    --J

  • And here is another resource for helping to find the cause (if it is different than the index rebuild that occurred).

    http://www.sqlservercentral.com/articles/Log+growth/69476/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • We had a similar problem on our 2 database servers.

    The CHECKPOINT command below fixed the problem. Our TempDB database was not being checkpointed frequently enough or at all. Why does this situation exist? I don't know. We don't have this problem with any other database on our servers.

    USE TempDB

    CHECKPOINT

    From SS Books Online regarding the CHECKPOINT command: "Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk."

  • Not to beat a dead horse, but ultimately you need to find root cause. SQL Server uses the TempDB to perform sorts that don't fit in memory. Most-likely you have some queries that are poor... unneeded order by clause, nested-loop joins instead of hash joins, poor use if subqueries. Or you may find that it's due to the shier volume of data being sorted.

    If the disk sorts end up being a necessary evil, then you can look at getting tempdb onto a disk with plenty of room to grow, and/or adding more memory to the server.

    Let me know if you need any help looking at the queries.

  • It could be doing alot of reindexing! That could be the source of your problem?

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

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