best size for TEMPDB
-
hi all,
i have 80gb and more database for proxy server logging.
During execution of more queries on this db i had an error "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..".
now my tempdb is set up in automatic grow and it's size is about 3.5Gb.
Which is the best size of TEMPDB for this pourpose?
Thanks!!!!!!
-
SQL Server does the best job determining what it needs because that can vary based on your databases needs, sometimes it can get large but you really should let SQL decide and make sure you have plenty of space on the hard drive to support the growth.
-
You need tempdb to be big enough for your daily and peak workload, without it needing to grow while the work is running. You should not plan to shrink tempdb during startup or any other time, unless a one-off event has caused tempdb to grow to a size that will not be needed again.
A few weeks of normal use should show you how big tempdb needs to be at your installation. It is not possible to say that for a user DB size of x GB you need y GB in tempdb, everywhere has different workloads. All that can be said is tempdb needs to be big enough, and that you must allow for tempdb growth along with user DB growth in our capacity planning.
Our DW instance has a main user DB of about 320 GB, and the way our ETL and reports are written our tempdb needs to be 100 GB to cope with the workload. In another year we may well have to add an extra 10 or 20 GB to tempdb to cope with the DW growth.
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
-
i understand!
thanks for your suggestions
-
Looking atthe error you are hitting it is actually the log on tempdb that is filling - and there is generally not much point in transaction logs on tempdb beyond the scope of the current transaction becasue when you shut down SQL Server the tempdb is cleared anyway - the prompt you are getting for a log backup seems to indicate you are not using Simple logging on tempdb - I would look at changing to Simple logging (on tempdb only NOT the main data db)
If the tempdb log fills up with a single SQL statement then it is too small (and the SQL may need a look at)
James Horsley
Workflow Consulting Limited -
hi,
thanks for your message. You are right, the event in errorlog marked "log file for tempdb"... but in real my tempdb datafile growth until 3.5Gb and recovery model is set up on "Simple".
so i'd like to ask you if it will be a good thing to manually increase the tempdb logfile or add more datafile for my tempdb.
Thanks!!!!!!!
-
If the log file fills up you need to increase the size of the log file. If the data file fills up you need to increase the size of the data file.
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
-
Even in 'simple recovery' mode you can get the tlog of a database running out of space, incl for tempdb. SQL Server makes heavy use of tlogs internally, exclusive of the recovery mode. The recovery mode really decides the verbosness of the data recorded and how it is handled for recovery purposes.
My guess, filippo, is that you ran something that made use of aggregations or hash joins, or something similar that makes heavy use of tempdb. There can be two options here. One, several respondents have already mentioned, which is increase the space available for tempdb's tlog file. Another possible option, if you can, is to have the scripts run in smaller batches. If you run some heavy script, whatever objects are made use of in tempdb may last to the end of the whole transaction, continuing to growth unless it hits an issue such as yours. If you can break the script down so that it runs in small batches, then you may find that though tempdb is still heavily used, it will handle it in smaller transaction chunks and so not make tempdb's tlog grow as much. Not sure if this will be possible for you, but if it is then it may be another option if you are stuck for diskspace.
Rgds
iwg
-
1) Sizing tempDB depends on both the amount of data on the server as well as the access patterns on it. As iwg mentioned aggregations and hashing on joins as well as sorts, huge batch DMLs, temporary table or large table variable usage all use tempDB.
2) If you have tempDB set for DEFAULT parameters for size/growth, you just fragmented the hell out of your tempDB at the OS level and will suffer a significant performance hit for that. 3.5 GB size grown at 1MB increments is 3500+ fragments! You should size tempDB to be as big as it needs to be from the moment sql server starts up, and you should give it a reasonable growth factor based on that initial sizing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
thank you all!
i will plan a better growth strategy and check and tracing the queries which have a heavy impact on my tempdb!
i will tell you in next days my decisions!
thanks very much again!!!!!
-
If you can afford a couple of SQL restarts I'd first get the tempdb down to it's initial size on fresh installation - 2 Mb. Then I would immediately allocate it at 3072 Mb, 3 Gb, for the data portion with a growth factor of 1024 Mb, 1 Gb. For the transaction log I would then allocate 512 Mb initally with a growth factor of 512 Mb. This would eliminate the sever OS disk fragmentation that you have caused to your present SQL Server. Then I would just monitor to find out what the actual 'norm' for data and log size. For example if ot turns out that it is 4 Gb data and 1 Gb log, then go through the same steps to get tempdb down to 2 Mb and then allocate it right. Something like 4096 Mb data, 2048 Mb growth and limit at 6145 Mb. For the log, 1024 Mb with 512 mb growth and a limit of 1537 Mb.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
If your tempdb log isn't alone on the disk, it's possible that something else ate all the free space without generating a message and you didn't realize it. Even if the only other files on the same drive are SQL log files, I have seen processes like index optimization in other databases grow their log files until there was no free space, and the first error message that appeared was for a relatively small tempdb log doing something perfectly normal.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply