February 10, 2017 at 12:41 pm
Why is it called "Initial Size (MB)" in tempdb properties in SSMS?
It is current file size. If it grows, e.g. from 2GB to 32 BG, after reboot it will not go back to 2GB.
February 10, 2017 at 2:02 pm
RVO - Friday, February 10, 2017 12:41 PMWhy is it called "Initial Size (MB)" in tempdb properties in SSMS?
It is current file size. If it grows, e.g. from 2GB to 32 BG, after reboot it will not go back to 2GB.
From everything I've read online and from personal experince, I believe that if you reboot it will go back to the initial size you set tempdb to and will grow as needed (presuming auto-grow is on).
I do not know of a nice way to see wha tthe "initial size" of tempdb was mind you.
So if you do set tempdb to 2 GB and don't manually grow tempdb, when you reboot it it will go back down to 2 GB.
But depending on what normal activity is, it may quickly grow back to 32 GB.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 11, 2017 at 10:14 am
you dont have to reboot to shrink tempdb, you can always use shrinkfile or shrink database, in case tempdb does not shrink then you can use dbcc freeprcchase after issuing commit command and try again.
be advised freeproccache are not not recommended for production systems and can impact performance
https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/
February 11, 2017 at 1:02 pm
Guys, I did a test on my local SQL Server. Tempdb Initial size was 8MB.
I grew it manually to 50MB.
I did reboot.
Initial Size = 50MB.
Why everybody keeps saying it will go back to Initial Size after reboot?
It will not.
February 11, 2017 at 1:30 pm
RVO - Saturday, February 11, 2017 1:02 PMGuys, I did a test on my local SQL Server. Tempdb Initial size was 8MB.
I grew it manually to 50MB.
I did reboot.
Initial Size = 50MB.
Why everybody keeps saying it will go back to Initial Size after reboot?
It will not.
TempDB is resized to its initial size when SQL Server is started. It isn't about the reboot, but about the SQL Server service.
If you grow it manually, you've set the initial size - in your case, to 50 MB. So that's the initial size.
If the initial size is 8 MB and it grows itself to 20 MB, the initial size is still 8 MB. When you restart, it'll be 8 MB.
February 14, 2017 at 12:15 pm
Finally it's clear!
If you manually grow file,
it will not re-initialize to whatever size was when SQL Server started.
Question.
If you know your tempdb files sizes will grow automatically up to let's say 2GB each file,
is it a good idea to grow them right away after restart?
Otherwise SQL Server performance will suffer doing automatic grow from 8 MB to 2GB.
Correct?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply