June 12, 2007 at 5:28 pm
Hi,
What steps do I have to take to enforce a minimum size of the tempdb after a server restart?
June 12, 2007 at 5:53 pm
Right click on tempdb in Enterprise manager, and select properties. There you can set the minimum data file size and minimum log file size.
June 12, 2007 at 6:02 pm
I assume in the space allocated section? I was looking at that but...
Won't that change the size of the database that is currently in use?
Or will it change ONLY after a server reboot?
I've been looking into the help files but can't find a clear answer as yet.
June 12, 2007 at 7:02 pm
Few adds on: You cannot modify a db file size to a lower value from the properties screen. TempDB will go to the initial size after restarting the sql server service. Otherwise you can shrink it using dbcc and restrict the growth from properties. However, you have to be carefull when restricting tempdb size based on what your application executes: whether it's using aggregates, or large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing.
Good luck.
June 12, 2007 at 8:12 pm
Hi,
When creating a new database, the 'Space Allocated' field actually reads 'Initial Size (MB)'.
Thats Fine.
What I would like to know is that: if I change the 'Space Allocated' setting on the tempdb, will it actually mean that the tempdb will created at that size on the next re-start?
June 14, 2007 at 3:26 am
Yes.
Setting the size of tempdb in SQL 2000 EM or SQL 2005 SSMS will do 2 things:
1) Increase the current size of tempdb to the values you haave given.
2) Use the size given as the initial size for tempdb when SQL restarts.
My guess is that at SQL restart, the schema for model is copied to tempdb, and all the space map pages in tempdb are re-initialised. This would allow the very quick initialisation of tempdb that we see on startup, regardless of tempdb size. Tempdb for our DW system is 100 GB, but it initialises at startup in much the same time as other systems where tempdb is just a few MB.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 14, 2007 at 6:25 am
use alter database to change the size of tempdb then restart sql services.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 14, 2007 at 10:52 am
Here is a sample script (adjust the sizes for your environment):
---
use master
go
---
alter database tempdb modify file
(name = tempdev,
size = 8192MB,
maxsize = 10241MB,
filegrowth = 2048MB)
go
alter database tempdb modify file
(name = templog,
size = 512MB,
maxsize = 1025MB,
filegrowth = 256MB)
go
checkpoint
go
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 14, 2007 at 4:27 pm
thanks for all replies.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply