October 12, 2021 at 12:57 pm
Hi all, I'm trying to create some checklists for troubleshooting various issues, in this case, when TempDb is full. I'd like a prioritized repeatable process where possible to help me out in a hurry.
When TempDB is full, what are the steps you take to troubleshoot that and fix it? Here is what I have so far, but I know it's far from being adequate as I don't yet know the best steps to take to troubleshoot it.
To me, there are a couple of checklists needed for this. When the server will let you connect, and when it won't.
When the server will let you connect:
When the server won't let you connect:
What other steps do you take in order, to help you troubleshoot and fix full TempDB?
Thanks for the help!
October 12, 2021 at 7:50 pm
Grow TEMPDB is another step where possible. I try to ensure I have enough disk space to allow for autogrowth and I try to monitor for abnormal autogrowth on all databases. If I absolutely need to free up disk space, restarting the SQL Server Service is one of the last things I would do as it impacts ALL databases and all running queries. If I can get into the system (sqlcmd or ssms), I will try to shrink tempdb before I would restart the instance.
And when you say the server won't let you connect, is that via SSMS (which is what I expect) or any method (sqlcmd for example)? I've had SQL instances that refused SSMS connections due to various things, but I've never had sqlcmd fail to connect.
I don't like shrinking mind you as tempdb grew to that size for a reason, and it may be a perfectly valid reason. If it is valid, it is likely to happen again, so I'd prefer to not tempt fate and shrink it or put a limit on how big it can get, but instead add more disk and allow tempdb to grow.
But that is just me. I've only had tempdb get out of hand a handful of times and each time I had enough disk for it to grow without filling the disk; just resulted in a bit of wasted space until the next scheduled update window when I rebooted the instance.
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.
October 13, 2021 at 11:06 am
Thank you for your help! I couldn't connect t it via SSMS. Yesterday, thanks to Erik Darling, I learned about how to connect to the server via the remote DAC. Moving forward, that will be added to the list so I can investigate what's going on.
I have several tempdb files and the space allocated should be sufficient. This only happens now and then and it could be several things causing it. I am just trying to learn a really good way to quickly identify it and fix the issue. In this case, it brought the app down.
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply