SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempdb


Tempdb

Author
Message
premkuttan
premkuttan
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 303
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????
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64979 Visits: 18570
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

sqlbuddy123
sqlbuddy123
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5944 Visits: 2243
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
balaji.ganga
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 656
This is for your optional,
you just restart the services. New tempdb will created automatically.

Thanks
Balaji.G
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64979 Visits: 18570
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

jvanderberg
jvanderberg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 746
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
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64979 Visits: 18570
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

Gail Wanabee
Gail Wanabee
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1785 Visits: 1406
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
jpenniman
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 31
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
Rick.Cornell.SME
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5901 Visits: 208
It could be doing alot of reindexing! That could be the source of your problem?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search