May 28, 2019 at 2:32 pm
I need to increase the size of my tempDB on my 2014 SQL Server as well as adding 3 more of the same increased size.
Can I do both of these things at the same time?
Do I need any downtime? Will the changes take effect immediatly or will SQL Server need to be restarted?
May 28, 2019 at 4:03 pm
Yes, this can be done without downtime.
The second part of the question is why? What things are you seeing that makes you need to do this?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 29, 2019 at 7:52 am
On two Instances I have inherited there is just one tempDB file on both of a size of 8MB, the drive tempDB is on is 30GB and tempDB is the only thing on the drive.
This seems completly wrong to me and when compared to other sql server instance we have in the farm. I plan to change the size to 5GB and add in three more tempDB files and take autogrowth off the files.
I was unsure about weather I could make the changes with no downtime and if the changes take effect immediatly or needs a server reboot.
I am also doing this as some users have complained about slowness on this server, I cannot see anything else on the instances that would effect it and have suggested they look at the code/calls of the application. But the tempDB settings seem completly incrorrect so I would like to change them anyway.
May 29, 2019 at 9:28 am
If tempdb doesn't grow from above the initial size, increasing its size will not help.
Also, amount of tempdb data files should be in correlation with amount of cpus and, in general, not more than 8.
"Slowness" of the server can be even network related. For example, I had issue when some users were happy with the performance of the server and others were not. It was found out that the latter worked with the server from another office (country).
May 29, 2019 at 1:12 pm
On two Instances I have inherited there is just one tempDB file on both of a size of 8MB, the drive tempDB is on is 30GB and tempDB is the only thing on the drive. This seems completly wrong to me and when compared to other sql server instance we have in the farm. I plan to change the size to 5GB and add in three more tempDB files and take autogrowth off the files. I was unsure about weather I could make the changes with no downtime and if the changes take effect immediatly or needs a server reboot. I am also doing this as some users have complained about slowness on this server, I cannot see anything else on the instances that would effect it and have suggested they look at the code/calls of the application. But the tempDB settings seem completly incrorrect so I would like to change them anyway.
When users complain of slowness, you should likely spend some time confirming what exactly is slow.
Simply adding files to tempdb may not be the cause of your issues. It may alleviate some of the "slowness", but it is certainly not a cure.
Here are some questions:
Is the server physical or virtual? If it's virtual, there is an entire set of configuration items that need to be looked at.
Is the slowness intermittent, or is it always slow?
How have you captured that tempdb is causing the issue?
Have you done any wait stats analysis?
I fear you are performing some knee-jerk reaction to the users complaining, with no real plan based upon actual facts.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply