Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tempdb Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 10:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 28, 2013 3:12 PM
Points: 313, Visits: 253
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????
Post #956493
Posted Wednesday, July 21, 2010 10:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 17,710, Visits: 15,580
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #956503
Posted Wednesday, July 21, 2010 11:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:50 PM
Points: 1,194, Visits: 2,220
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
Post #956579
Posted Tuesday, July 27, 2010 5:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 3:00 AM
Points: 164, Visits: 656
This is for your optional,
you just restart the services. New tempdb will created automatically.

Thanks
Balaji.G
Post #959304
Posted Tuesday, July 27, 2010 8:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 17,710, Visits: 15,580
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #959439
Posted Tuesday, July 27, 2010 9:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
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
Post #959469
Posted Tuesday, July 27, 2010 9:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 17,710, Visits: 15,580
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #959474
Posted Wednesday, July 28, 2010 4:45 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 21, 2014 10:47 AM
Points: 258, Visits: 1,093
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."

Post #960401
Posted Thursday, July 29, 2010 12:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 7, 2012 12:02 PM
Points: 7, 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.
Post #960944
Posted Friday, July 30, 2010 12:12 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:37 AM
Points: 4,783, Visits: 208
It could be doing alot of reindexing! That could be the source of your problem?
Post #961569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse