April 16, 2020 at 5:00 pm
Hello,
When the temp db is full ( due to a temp tbl) my learning has been you can drop the temp tbl by going to the tempdb - temp tbls - right click - dropNow, this was done to resolve the issue by dropping the Temp table. I was wondering if dropping the temp table is a 100% safe action and does not involve any kind of data issues ( eg if temp was inserting or some action which was 't complete) since anyway it is temp.
Secondly, I am trying to understand one more scenario. Example : I have seen a DBA just resize one of the temp db PRIMARY file manually.. by navigating to Tempdb - DB properties - files - clicking on the file size and say it was 200MB so reduced it to 100MB. Now is it also a safe action? what are the immediate effects of this? apart from reduced size.
Now, I know the code or issue needs to be analyzed which caused the issue however when it is full it is sometimes an urgent situation and are above two soln valid? Please share your thoughts!
April 16, 2020 at 5:14 pm
Going into tempdb and dropping tables is wrong, maybe an application is using the tables to make some work and by deleting it you are affecting production, or affecting your production data.
If your tempdb is getting full you should check your code or check how you have configured your tempdb.
By resizing your tempdb files you are not fixing the issue, you are buying a little bit of time, they are gonna grow again until they fix the application.
If i was in your shoes and if i can't get more storage to extend tempdb and avoid dropping tables and resizing, i guess i would try to do that after sending an email to everybody explaining what can happen in production, it's way too dangerous.
I would communicate the issue to the higher ups, and tell them that that's the only way to keep production up if they don't get more storage or if they don't fix the application, tell them what can happen if you start dropping temp tables in tempdb and that it is not a good practice at all.
How big is your tempdb?
How are your datafiles distributed?
How much of storage do you have available for your tempdb?
Regards,
April 16, 2020 at 5:24 pm
OK, thanks for some good tips.
From what I see we have 5 logical files of type rows and 1 log file. Autogrowth is none for primary and log has 64MB auto growth set.
Initial size of each PRIMARY files is 19500MB which is a lot. So, it is bad code.
Now, I also wanted to ask, what other options you have beside dropping temp tables and reboot?
April 16, 2020 at 6:15 pm
I think you can't do anything besides getting more storage or fixing the application.
We can wait to see what the gurus have to say, they might have some different approach.
Remember that the SQL Server is the victim and not the problem.
What can you do to help?
Try and hunt queries that are using way too much tempdb.
You can use sp_whoisactive to see active queries and the amount of tempdb that is being used by these queries, try and fix these queries and send them to the development team, or you can hunt them and make a list and tell the dev team to fix these queries.
Also this reply made by Aaron Bertrand in dba stackexchange.
Have you checked on your side?
You don't have any process that can fill the tempdb? DBCC Checkdb tends to eat tempdb.
April 16, 2020 at 6:20 pm
tempdb isn't large or small. It is what it is. You might just need that much space for your workload. This isn't necessarily an indication of bad code, but it can be. Often large queries can spill to tempdb, or developers can write code that create temp tables. In either case, you may or may not be able to reduce the usage of tempdb.
I'd start by ensuring you have enough space, which means you shouldn't get auto growth. From there, you can start to track down queries, as suggested by Alejandro above.
April 16, 2020 at 7:07 pm
Thanks guys, yeah, space issue has been discussed, however all of sudden I see someone running some query which is not using dbo prefix, having select * statements, playing with more data than required, I will continue my work on this to make it stable, which it is now. However, I like to have the options ready on my table when I hit the situation again and would like to take the right action. Yes, SP whoisactive is helping in tracking the bad query running by one of the dev, again it is not about dev or not dev, I am more concerned about the problem and keep the sql server responding. So, thank you for your suggestions! it is indeed helpful.
April 17, 2020 at 2:50 pm
Just my 2 cents, but devs shouldn't be running queries against live, espeically if they are going to take down live by doing so.
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply