Can't release space of temdb

Question

Can't release space of temdb

Viewing 12 posts - 31 through 41 (of 41 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Perry Whittle (9/2/2015)
Perry Whittle (8/31/2015)
Welsh Corgi (8/31/2015)
I'm going to have to start the SQL Server Agent Service.
why did you stop it in the first place?
What is the default size for your tempdb files and how many do you have?
???

I have 1 and it is very small, 7182 MB.

Everything is fine. I will moniter it and take corrective action as needed for the time being.

I have bigger fish to fry.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Welsh Corgi (9/2/2015)
Perry Whittle (9/2/2015)
Perry Whittle (8/31/2015)
Welsh Corgi (8/31/2015)
I'm going to have to start the SQL Server Agent Service.
why did you stop it in the first place?
What is the default size for your tempdb files and how many do you have?
???
I have 1 and it is very small, 7182 MB.
Everything is fine. I will moniter it and take corrective action as needed for the time being.
I have bigger fish to fry.
Thank you.

There have been some useful queries posted here to help you find the culprit. Unfortunately, since you restarted the SQL Server service, your history is gone. This means you'll have to work with what you have and tune those or wait until the deadly one strikes again. Since you have so many procedures, many of which are probably in need of help, my guess is that you won't run out of things to do. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Ed Wagner (9/2/2015)
Welsh Corgi (9/2/2015)
Perry Whittle (9/2/2015)
Perry Whittle (8/31/2015)
Welsh Corgi (8/31/2015)
I'm going to have to start the SQL Server Agent Service.
why did you stop it in the first place?
What is the default size for your tempdb files and how many do you have?
???
I have 1 and it is very small, 7182 MB.
Everything is fine. I will moniter it and take corrective action as needed for the time being.
I have bigger fish to fry.
Thank you.
There have been some useful queries posted here to help you find the culprit. Unfortunately, since you restarted the SQL Server service, your history is gone. This means you'll have to work with what you have and tune those or wait until the deadly one strikes again. Since you have so many procedures, many of which are probably in need of help, my guess is that you won't run out of things to do. 😉

Well thank you for all of your help. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

richard.bowles SSCommitted Points: 1619 More actions · Answer 4

Thanks for the scripts, Eric. You just helped me with tracking down the culprit that was consuming all of the space in tempdb (about 70GB with 8 duplicate copies of the same SQL statement).

sriponraj Grasshopper Points: 13 More actions · Answer 5

Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..

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

sriponraj (9/3/2015)
Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..

So, if the run you kill is the one that processes your payroll hours, how ya going to feel about that? 😉

Killing SPIDs is a bad idea.

--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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Jeff Moden (9/3/2015)
sriponraj (9/3/2015)
Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..
So, if the run you kill is the one that processes your payroll hours, how ya going to feel about that? 😉
Killing SPIDs is a bad idea.

The payroll run is the most loved process that runs in any company. Just ask any employee. 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Jeff Moden (9/3/2015)
sriponraj (9/3/2015)
Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..
So, if the run you kill is the one that processes your payroll hours, how ya going to feel about that? 😉
Killing SPIDs is a bad idea.

Sometimes you have to kill SPIDs. Killing SPIDs without knowing what you're killing is a bad idea indeed.

FYI, that idea was posted by a newbie who apparently didn't really take into consideration what had already been discussed, so .... 😎

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eric M Russell SSC Guru Points: 125561 More actions · Answer 9

If this is a reporting database with stored procedures that typically run for several minutes or longer, then one thing I do to audit usage and performance is implement something like a ReportRun table. It is inserted at the start of each procedure call and updated at the end. You can include columns for whatever stats you feel would be useful. If the report constructs dynamic SQL, then I find it essential to capture that too. Sure, you can query the equivalent of this from DMV views ad-hoc anytime, but it's more difficult in a pinch to narrow down what you're looking for and it isn't persisted after a server reboot. The dashboard application we use does historical performance reporting on the line of business reports.

create table ReportRun

(

primary key ( ReportStartDT, ReportID ),

ReportStartDT datetime not null,

ReportID smallint not null,

ReportEndDT datetime null,

ReportUser varchar(200) null,

ReportSQL varchar(max) null,

ReportRowsAffected int null,

ReportTempdbMB int null

);

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

Alvin Ramard (9/4/2015)
Jeff Moden (9/3/2015)
sriponraj (9/3/2015)
Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..
So, if the run you kill is the one that processes your payroll hours, how ya going to feel about that? 😉
Killing SPIDs is a bad idea.
Sometimes you have to kill SPIDs. Killing SPIDs without knowing what you're killing is a bad idea indeed.
FYI, that idea was posted by a newbie who apparently didn't really take into consideration what had already been discussed, so .... 😎

Agreed that you sometimes need to kill spids but not so you can shrink TempDB. If you're going to do that, you might as well bounce the server. A close second would be to set the server to single user with rollback immediate. Too many things use TempDB to chase spids that are using TempDB.

Understood on the newbie thing, as well.

--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)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 11

Jeff Moden (9/4/2015)
Alvin Ramard (9/4/2015)
Jeff Moden (9/3/2015)
sriponraj (9/3/2015)
Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..
So, if the run you kill is the one that processes your payroll hours, how ya going to feel about that? 😉
Killing SPIDs is a bad idea.
Sometimes you have to kill SPIDs. Killing SPIDs without knowing what you're killing is a bad idea indeed.
FYI, that idea was posted by a newbie who apparently didn't really take into consideration what had already been discussed, so .... 😎
Agreed that you sometimes need to kill spids but not so you can shrink TempDB. If you're going to do that, you might as well bounce the server. A close second would be to set the server to single user with rollback immediate. Too many things use TempDB to chase spids that are using TempDB.
Understood on the newbie thing, as well.

Is there a single_user property at the server level? Maybe you mean to alter all databases to single_user mode?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Eric M Russell (9/4/2015)
Jeff Moden (9/4/2015)
Alvin Ramard (9/4/2015)
Jeff Moden (9/3/2015)
sriponraj (9/3/2015)
Check the connection for temp db by using sp_who2 active. Kill all the connection related to temp db and try to shrink..
So, if the run you kill is the one that processes your payroll hours, how ya going to feel about that? 😉
Killing SPIDs is a bad idea.
Sometimes you have to kill SPIDs. Killing SPIDs without knowing what you're killing is a bad idea indeed.
FYI, that idea was posted by a newbie who apparently didn't really take into consideration what had already been discussed, so .... 😎
Agreed that you sometimes need to kill spids but not so you can shrink TempDB. If you're going to do that, you might as well bounce the server. A close second would be to set the server to single user with rollback immediate. Too many things use TempDB to chase spids that are using TempDB.
Understood on the newbie thing, as well.
Is there a single_user property at the server level? Maybe you mean to alter all databases to single_user mode?

Yes, exactly. Apologies for not being clear on that. Turn off the SQL Server Agent and set the user databases to single-user. Much less desirable than just bouncing the service, IMHO.

--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)