DB maintenance plan to clear tempdb?

Question

DB maintenance plan to clear tempdb?

arvind.patil 98284

Mr or Mrs. 500

Points: 510
More actions
September 24, 2020 at 2:18 pm

#3790691

Hi Experts ,
The tempdb in our production database is getting full and this is causing the SQL server job and other query's to halt.
Should I shrink the tempdb to generate space? .I would require your help in creating a maintenance plan which would trigger automatically to generate space in tempdb.

Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

There is no point of shrinking tempdb - it will grow back.

You need to find out which process makes it grow beyond reasonable limits and figure out how to change it so it won't happen again.

_____________
Code for TallyGenerator

arvind.patil 98284 Mr or Mrs. 500 Points: 510 More actions · Answer 2

Will it free the space temporary?

So in production its still happening and we needs a temporary solution for the time being. It will take us some time to fix the actual issue .

I have one more question . In the production environment can i run Shrink tempdb (concurrently)when the process which is consuming our tempdb is still running.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

arvind.patil 98284 wrote:

Will it free the space temporary?
So in production its still happening and we needs a temporary solution for the time being. It will take us some time to fix the actual issue .
I have one more question . In the production environment can i run Shrink tempdb (concurrently)when the process which is consuming our tempdb is still running.

To answer your question, absolutely not. The reason why it's growing during the process is because the space is in use and trying to shrink TempDB at that time will not do anything to help and may actually put an extra load on TempDB. You have to fix this issue, period.

As for a temporary fix, that will really be counter productive because such temporary fixes become permanent and they don't really fix anything... eventually, you'll run into this problem again with your temporary fix already in place and there won't be a thing you can do about it.

If you want a better temporary fix, build a larger/faster drive for TempDB and then move TempDB to it. Be advised that you'll eventually have the same problem with such a drive because you haven't actually fixed the problem but it may work for now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arvind.patil 98284 Mr or Mrs. 500 Points: 510 More actions · Answer 4

Hi Experts ,

We have identified the process which has consumes the space in tempdb . The stored procedure uses a lot of temp tables in it . Does storing the data in physical tables instead of temp tables and truncating them would re leave the pressure from tempDB?

frederico_fonseca SSCoach Points: 16014 More actions · Answer 5

It would decrease use of tempdb and increase usage of what ever db you put those tables on.

might be worth seeing if those temp tables can be redone so size isn't as big - or even drop them as soon as they are not needed within the proc.

adding a clustered index with compression = page will most likely also help - but only do this if your server is not already cpu bound

if proc is executed concurrently then changing the tables to be on a permanent database will also mean you have to change their layout so and add a column that identifies which execution the records belong to.

this will also mean that deletes would need to be done instead of drop/truncate

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

arvind.patil 98284 wrote:

Hi Experts ,
We have identified the process which has consumes the space in tempdb . The stored procedure uses a lot of temp tables in it . Does storing the data in physical tables instead of temp tables and truncating them would re leave the pressure from tempDB?

Although that could certainly be a problem, it could still be how those temp tables are used rather than the temp tables themselves. For example, an accidental many-to-many join can cause substantial additional use of temp db in the form of spooling.

If you don't have such code problems, perhaps the real fix is to simply provide tempdb with more space because there are certainly some advantages of doing things in tempdb. If you decide to do the processing in a different database, perhaps make a separate database for such interim processing and set it to the SIMPLE recovery model, like tempdb is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 7

please read MS docs about tempdb database

This is where you want to be starting when taking tempdb into focus: Tempdb configuration survey results and advice

( and its referred artiles ! )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

While there is some really good advice out there about the number of files, drives, types of drives, etc, etc, etc related to TempDB, I've found that the biggest problem with TempDB is all the really bad code that a lot of people have written especially when it comes to spooling results due to missing or incorrect criteria in the code or people writing monster "all-in-one-query" code instead of appropriate use of "Divide'n'Conquer" methods.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)