TEMPDB issue

Question

TEMPDB issue

poratips

Hall of Fame

Points: 3994
More actions
March 28, 2014 at 2:17 pm

#291553

Hello,
We have Sql 2008 and currently we having issue that TEMPDB is FULL, I have restarted the server but it's still showing full.
I am not much thinking to Shrink the DB file.
Could you please explain me the correct steps to follow?
I have TEMP DB files (.mdf and .log) on separate drive and one of the .mdf files is shows on server as almost full means I have 5 GB and it shows 4792 MB.
Thanks and Appreciate it!

Viewing 15 posts - 1 through 15 (of 15 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Shrinking (making smaller) a full file (too small) is a little counter-productive. It's like reducing the speed you're driving at because it's taking too long to get where you're going.

You need to grow the file (make it larger)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

poratips Hall of Fame Points: 3994 More actions · Answer 2

Thanks.

Only my concern is that it might cause corruption, right?

poratips Hall of Fame Points: 3994 More actions · Answer 3

Hi,

When I was looking on server drive after restarting, it is showing on folder - Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data is 4.97 GB and when I right click on drive it shows out of 5 GB, only 270 MB free.

Is it something I need to shrink or restart the server again?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

poratips (3/29/2014)
Hi,
When I was looking on server drive after restarting, it is showing on folder - Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data is 4.97 GB and when I right click on drive it shows out of 5 GB, only 270 MB free.
Is it something I need to shrink or restart the server again?

Are you saying that you have a drive with only 5GB on it and that's where you put TempDB?

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

poratips (3/28/2014)
Only my concern is that it might cause corruption, right?

Shrinking TempDB can, but since that's the opposite of what you need to do (make it bigger, give it more space), that's not a concern.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

poratips Hall of Fame Points: 3994 More actions · Answer 6

poratips

Hall of Fame

Points: 3994

March 31, 2014 at 9:32 am

#1701653

That's correct as my TEMPDB drive is 5 GB.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Then I suspect that the resolution to this problem will involve locating another drive for TempDB or expanding the current one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

poratips Hall of Fame Points: 3994 More actions · Answer 8

Thanks.

I have concern as I am almost close to full capacity and somehow it's not still clearing the space and I couldn't see any open active transaction.

Could you please guide any other steps I should follow or I can try again any step by steps?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

What do you mean by 'not clearing space'?

If you mean the file isn't getting smaller, not it won't and it shouldn't. It sounds like you don't have enough space on that disk for TempDB. You're probably going to need to move TempDB to another drive or expand the current drive to give it more space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

poratips Hall of Fame Points: 3994 More actions · Answer 10

You are right that i need to add space or drive but still I am not much clear that after restarting why it's not clearing the space as Tempdb is rebuilt from copy of MODEL database after SQL Server re-start and as per BOL Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database means all temp objects are deleted.

Thanks.

poratips Hall of Fame Points: 3994 More actions · Answer 11

Thanks.

I mean to say "not clearing space" means even though after restarting the server, it's still shows close to 5 GB.

Do you think that if I change Initial size to 1 GB and Auto growth 100 MB should work?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Work as in start smaller, probably. Work as in not get full and throw errors, probably not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

poratips Hall of Fame Points: 3994 More actions · Answer 13

Thanks, you mean it will be better if I start with 100 MB and 10 MB auto growth?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

No. Never said that.

I said, if you change the initial size it will start smaller. That's it. Counter-productive, as SQL now has to grow the files, but if you want to hinder the DB operations that's your business.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass