June 2, 2017 at 8:55 am
The initial size of a TempDB was set to 30GB. Now when I check the properties, it says almost 30GB of space available. Now we are running out of space and we are unable to add any space on the drive and yes, this is our production server. Can I reduce the initial size of the file to maybe 5GB? If yes, then are there known issues that I might run into?
June 2, 2017 at 8:59 am
Simple answer, yes you can reduce the size if the space is available.
It shrinking advised? No.
Refer to methods: https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server
Question though, why is your tempdb on the same drive as other databases?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 2, 2017 at 9:06 am
newdba2017 - Friday, June 2, 2017 8:55 AMThe initial size of a TempDB was set to 30GB. Now when I check the properties, it says almost 30GB of space available. Now we are running out of space and we are unable to add any space on the drive and yes, this is our production server. Can I reduce the initial size of the file to maybe 5GB? If yes, then are there known issues that I might run into?
You could reduce the size of the tempdb but almost certainly it will grow again so there is really no point in doing that. Concentrate on finding the reasons for the growth first if the space is cramped on the system, otherwise just let it slide.
😎
If there is a risk of running out of space then consider placing an "overflow" file on another drive.
June 2, 2017 at 9:14 am
I reduced the size but it didn't work.
June 2, 2017 at 2:07 pm
It is possible that tempdb was set to an initial size of 30 GB to prevent repeated growth of tempdb after SQL Server restarts. This makes sense if tempdb growth usually stopped at this point. Growing a database can be a resource intensive operation, especially if done is small chunks like 1MB at a time.
If you are running out of space on this drive I would recommend adding another drive and moving tempdb to that drive.
June 2, 2017 at 7:15 pm
Lynn Pettis - Friday, June 2, 2017 2:07 PMIt is possible that tempdb was set to an initial size of 30 GB to prevent repeated growth of tempdb after SQL Server restarts. This makes sense if tempdb growth usually stopped at this point. Growing a database can be a resource intensive operation, especially if done is small chunks like 1MB at a time.If you are running out of space on this drive I would recommend adding another drive and moving tempdb to that drive.
Let me second what Lynn said. Shrinking it and having it grow can not only lead to slowdowns, but also a very unwelcome surprise if you run out of space. Data tends to grow over time. If you're running that close to zero, get another drive for tempdb now while you still have the luxury of time and don't have a serious crisis on your hands.
Have you had a lot of growth recently in other databases that cause you to run low on space? Is the log drive experiencing a similar shortage?
June 3, 2017 at 9:51 pm
Lynn Pettis - Friday, June 2, 2017 2:07 PMIt is possible that tempdb was set to an initial size of 30 GB to prevent repeated growth of tempdb after SQL Server restarts. This makes sense if tempdb growth usually stopped at this point. Growing a database can be a resource intensive operation, especially if done is small chunks like 1MB at a time.If you are running out of space on this drive I would recommend adding another drive and moving tempdb to that drive.
I'll add a strong third to that and, considering the following...
we are unable to add any space on the drive and yes, this is our production server
... it's time for one of three things to happen (and I strongly recommend that they all happen).
1. It's a production system. Make the investment in disk space or die. 😉
2. Invest some time digging into what the code is that's causing it to grow. Such code is usually also slow code that uses quite a few other resources and usually won't scale. My production server has a heavy duty, large batch/ETL 1.1 TB database on it, along with a couple of near TB databases, as well. I have 8 two GB files for TempDB. None of them have ever grown. That, notwithstanding, I have them on a 100GB allocation of disk space so that I don't ever have to worry about what happens with scalability or the occasional mistake.
3. Do a growth analysis and then buy at least 2 years worth of additional space while making sure that your system can sill accept more after you find out that your estimate was wrong.
Speaking of all that, plan on at least 3 times the current size of the database... 1 for the current database, 2 for growth, and 3 to test restores on because backups don't count. Successful restores from backups do. 😉
Seriously... buy a pot wad of disk space. It's much too costly to have to dance around all of the problems that limited disk space will cause. Keep a TB "hidden" from everyone else so that you don't have to beg, borrow, or steal when the poo hits the fan for unexpected growth or gotta-have-it-yesterday tasks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply