August 27, 2019 at 10:52 am
Hi there,
I have a really odd issue, in that the initial size of the tempdb data file is increasing.
I recorded that the initial size of the tempdb data file for a production sql server was set to 71GB 13 days ago.
It was sat on a drive which has only 70GB in size and generating alerts in our nagios monitoring.
I increased the size of the disk to 100GB moved on to the next pressing issue on my plate.
The server was rebooted 10 days ago due to patching and now the initial size of the tempdb has move to 102GB and filled the disk again and generating alerts again.
I've attached a screen shot of the tempdb settings.
It's very odd. I've searched through the errorlogs and can't see that the size of the tempdb has been increased manually and I don't believe there is any scripts to change it and I don't believe anyone would increase the size of this tempdb in production without me knowing or without a change ticket.
What I think I'll do is shrink the DB and turn off Auto grow unlimited. But I need to perform an investigation to see if anything might be pushing the tempdb to 100GB.
Whenever i look at the tempdb disk usage is always showing as 100 percent unallocated. But this is just when I happen to view the report and not over time.
August 27, 2019 at 6:47 pm
Does the attached screen shot look odd to anyone?
Is the report lying on how much data space is being used?
You're confusing space-used with space_allocated. Also, you're settings appear to be making use of the default settings, which you should never do. It's never actually a good idea to use a % to control growth. Always use a fixed number of MB for both the MDF and the LDF files.
I'll also state that even if you're using only 50MB for TempDB, you very likely have some serious problems in your code. I have a serve with a couple of multi-terabyte databases and it does some pretty heavy lifting especially when it comes to batch processing. It has 8 data files of 2GB each and the only time they grow is when we do something totally ad hoc and wonky. We do have 100GB dedicated to the drive just to cover such eventualities but it's definitely not the norm.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2019 at 4:49 pm
select name,size*8.0/1024 'Current Size in MB' from tempdb.sys.database_files
select name,size*8.0/1024 'Initial Size in MB' from master.sys.sysaltfiles where dbid = 2
August 28, 2019 at 6:53 pm
What I see is that you have a single data file for tempdb - you should have more than that...how many will depend on a lot of factors, but you can start with matching the number of cores, up to a maximum of 8 files. The only time you would ever need more than that is if you actually experience allocation errors in tempdb.
The second thing I see is the growth set to a percentage...this should be set to a fixed size. If tempdb is growing out to 100GB then you should set a small initial size and set the size of each file to a value that meets the total requirement. For example - 8 files at 15GB each would get you to more than 100GB...
The log file should also be set to a fixed size - with a fixed growth. Depending on usage that could be 1GB or 8GB - and depending on version you will probably need to figure out the growth size. Due to changes in the algorithm for VLF's - you might need to create that growth size at a much smaller size than in previous versions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2019 at 9:38 pm
Be careful now... if you do any work with SET IDENTITY_INSERT ON, you might want to reconsider especially on later versions where they essentially made Trace Flag 1117 ALWAYS active on TempDB with no way to disable it.
The trouble with SET IDENTITY_INSERT ON is that it forces a sort of the full Clustered Index in TempDB and if you have large tables where it is used, ALL of the files in TempDB will grow at the same rate even if they're not needed. It killed me on some of the things that I've needed to do.
I'll also recommend that you actually shouldn't automatically allocate 1 file in TempDB up to 8 files unless you have some bit of proof that you're having problems with TempDB contention. Again, this is because of something makes one file in TempDB go crazy, MS has now made it so that all the files will follow suite even though they're not being used and the larger number of files in TempDB makes the problem of TempDB running out of room even more likely.
Totally agreed on the percent-growth thing, though. It's not often that I use the word but, IMHO, NEVER use a percent-growth on ANY file.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2019 at 8:25 am
Thank you both
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply