Tempdb

Question

Tempdb

premkuttan

SSCommitted

Points: 1934
More actions
July 21, 2010 at 10:01 am

#148096

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????

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

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

balaji.ganga SSCrazy Points: 2280 More actions · Answer 3

This is for your optional,

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

Thanks

Balaji.G

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

jvanderberg SSCertifiable Points: 6762 More actions · Answer 5

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 7

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."

jpenniman SSC Veteran Points: 213 More actions · Answer 8

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.

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 9

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