How to handle out of disk Tempdb on production server?

Question

How to handle out of disk Tempdb on production server?

Reo

Hall of Fame

Points: 3774
More actions
January 4, 2010 at 4:49 am

#228869

Hi All;
I was face a question - How to handle out of disk Tempdb on production server? ; In an IBM DBA interview.
The interviewer’s concern is that if tempdb is full and there is no room on disk; how u will handle this situation on live production server.
I was replied that I will try to shrink the database and delete temp tables in temp database without restarting the SQL Server. and another way is that I will add another file in primary file group.
But I’m little bit confuse that can be other way to handle out of disk tempdb problem without restarting SQL Server service? Please suggest.
Regards;
Ram
Ram
MSSQL DBA

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

There are three ways that MS recommends, two you can do without restarting the server however you need to ensure that there is no activity happening in the TempDB.

http://support.microsoft.com/kb/307487"> http://support.microsoft.com/kb/307487

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

When this happens, you actually need a little bit of space to shrink the tempdb and you might not have it. One option would be to add another file to the tempdb on a different disk. That will provide you with a bit of space and you can then try cleaning up the original disk.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Shivrudra SSCrazy Points: 2835 More actions · Answer 3

hi,

I think you need to go though this http://msdn.microsoft.com/en-us/library/ms176029.aspx

Regards,
Shivrudra W

Reo Hall of Fame Points: 3774 More actions · Answer 4

Reo

Hall of Fame

Points: 3774

January 13, 2010 at 6:50 am

#1103007

Thanks Lot! 🙂

Ram
MSSQL DBA

KoldCoffee SSCoach Points: 18971 More actions · Answer 5

Reo (1/4/2010)
Hi All;
I was face a question - How to handle out of disk Tempdb on production server? ; In an IBM DBA interview.
The interviewer’s concern is that if tempdb is full and there is no room on disk; how u will handle this situation on live production server.
I was replied that I will try to shrink the database and delete temp tables in temp database without restarting the SQL Server. and another way is that I will add another file in primary file group.
But I’m little bit confuse that can be other way to handle out of disk tempdb problem without restarting SQL Server service? Please suggest.
Regards;
Ram

Hello Ram. I just read your post. I am studying for certif exam myself and have question. Would moving the tempdb using ALTER statement be a viable solution? I think you do not recommend it because it would require a restart, is that correct?

Thank you for your time.

sqlblogi SSC-Addicted Points: 420 More actions · Answer 6

Sounds like to me someone is not destroying their cursor 🙂

I see this every now and then..as http://support.microsoft.com/kb/307487 says, you must make sure no one is using tempdb and they suggest you start sql in single user mode. Might as well restart the service and find the culprit. I guess that is my real world reply 😛

sqlblogi SSC-Addicted Points: 420 More actions · Answer 7

hxkresl (7/13/2010)
Reo (1/4/2010)
Hi All;
I was face a question - How to handle out of disk Tempdb on production server? ; In an IBM DBA interview.
The interviewer’s concern is that if tempdb is full and there is no room on disk; how u will handle this situation on live production server.
I was replied that I will try to shrink the database and delete temp tables in temp database without restarting the SQL Server. and another way is that I will add another file in primary file group.
But I’m little bit confuse that can be other way to handle out of disk tempdb problem without restarting SQL Server service? Please suggest.
Regards;
Ram
Hello Ram. I just read your post. I am studying for certif exam myself and have question. Would moving the tempdb using ALTER statement be a viable solution? I think you do not recommend it because it would require a restart, is that correct?
Thank you for your time.

Moving the tempdb will require a service restart on the instance

KoldCoffee SSCoach Points: 18971 More actions · Answer 8

KoldCoffee

SSCoach

Points: 18971

July 13, 2010 at 11:56 pm

#1192552

Thank you REO!:-)