August 14, 2019 at 3:57 pm
Hi
Sorry of the newbie question, but I'd thought I'd seek the opinion of you gurus.
I have tempdb on a production DB Server which I suspect is quite heavily used.
According to the Disk Usage report the Data Files Space Usage show as 100% Unallocated. Which is seem a bit strange.
Also prior to me increasing the disk space on the drive where the tempdb.mdf sits. Its a strange thing. Somehow someone has initial Size of the tempdb.mdf file to be 71GB on a drive that was only 70GB.
This came to my attention that the drive only had 10MB free until I increased it.
The disk usage report on the DR sql Mirror for temp seems "fine" with the percentage distributed among Index, Unallocated, data and Unused.
What's your thoughts about tempdb in a production DB server being 100% unallocated, does that seem strange? Would I plan a restart of sql server to recreate tempdb?
August 14, 2019 at 5:04 pm
Hmm UAT doesn't have 100% of the tempdb.mdf file set to unallocated either, most of it is but not 100%. Same for the prod server for another product.
August 14, 2019 at 6:33 pm
It is not unexpected - it depends on what is processing at the time you look at the utilization. Temp tables do not retain data - once the connection is terminated the data is cleared and no longer allocated.
It appears that you have a single mdf file - which may or may not be an issue. Generally you should have multiple mdf files configured for tempdb - the number of files will depend on the size of your system and tempdb utilization. If you have high utilization of tempdb - you may be experiencing issues - review this article: https://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
To your issue - if someone wrote a bad query that caused tempdb to grow - you need to identify that process and correct the code. Review this article for some ideas: https://www.sqlskills.com/blogs/paul/category/tempdb/
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply