temp db is logspace used is >90%.

Question

Post reply

temp db is logspace used is >90%.

charipg

SSCrazy Eights

Points: 9888
More actions
September 18, 2009 at 5:52 pm

#135721

temp db is logspace used is >90%.
i tried to take trnbackup , but its not done.
what wil i do?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sergey Vavinskiy SSCarpal Tunnel Points: 4469 More actions · Answer 1

In the future you have to have the TempDB log file size restricted at certain point, do not allow unlimited grow.

Now I would just restart SQL Server services if it is Ok with your users and a company policy.

TempDB files will be recreated with their initial size.

shyam-760539 SSC Rookie Points: 44 More actions · Answer 2

As other option, You can shrink the tempdb data files and log files

it might release some space

Sergey Vavinskiy SSCarpal Tunnel Points: 4469 More actions · Answer 3

You can shrink the tempdb data files and log files
it might release some space

Hmm... It looks like it is not a real option in this case. For doing that you have to shrink log file and to take tr.log backup in one single transaction. But the author already tried to do tr.log backup without any success. It is very possible that the file is 100% full.

If he has a little space, another posible option would be create a secondary log file, shrink the primary log file and finally delete the secondary log file.

But in the critical situations re-starting the SQL Services is the most effective option.

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 4

I think the only solution is, restart the SQL Server Services. otherwise if you have space in your disk increase the logfile size of tempdb.

Thank You.

Regards,
Raghavender Chavva

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 5

Wait just a minute here, folks... 90% of WHAT??? If it's 90% of the default size, no one cares. If it's 90% of the entire hard disk, then we care. Be until we know, we really can't help. So, let's ask the question....[font="Arial Black"]90% of WHAT???[/font]

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

charipg SSCrazy Eights Points: 9888 More actions · Answer 6

charipg

SSCrazy Eights

Points: 9888

September 21, 2009 at 12:07 am

#1056089

it's 90% of the default size.

Silverfox SSC-Insane Points: 23150 More actions · Answer 7

Jeff has a valid point.

How much space is left on the drive that tempdb is on. and what is the size of the tempdb database.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

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

charipg (9/21/2009)
it's 90% of the default size.

Are you saying that it's 90% of 1 megabyte? What are the sizes of the MDF and LDF files for TempDB and what are the growth settings for both?

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 9

I must agree with "90% of what?"

but:

Backup Log db_name with Truncate_only --(clear it without deleting it)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 10

I'm at a loss as to why others are suggesting that the log should be manually truncated, particularly as tempdb is in 'simple' mode and, therefore, should be truncated automatically when SQL Server issues a checkpoint.

You should consider monitoring the growth in tempdb's log usage for a period - you may find that a long-running transaction is causing the issue.

I've seen the same issue caused by MS Access clients - identifiable by the relevant connection having a wait type of 'NETWORK_IO'. I can't remember the exact circumstances leading to this happening, but I recall that it had something to do with he population of Access' combo boxes and users not scrolling all the way to the bottom of the list of items.

Chris

Sergey Vavinskiy SSCarpal Tunnel Points: 4469 More actions · Answer 11

Chris Howarth-536003 (9/22/2009)
I'm at a loss as to why others are suggesting that the log should be manually truncated, particularly as tempdb is in 'simple' mode and, therefore, should be truncated automatically when SQL Server issues a checkpoint.
You should consider monitoring the growth in tempdb's log usage for a period - you may find that a long-running transaction is causing the issue.
I've seen the same issue caused by MS Access clients - identifiable by the relevant connection having a wait type of 'NETWORK_IO'. I can't remember the exact circumstances leading to this happening, but I recall that it had something to do with he population of Access' combo boxes and users not scrolling all the way to the bottom of the list of items.
Chris

Chris,

I absolutely agree with you, but we are not talking about proactive solutions, we are talking about critical situation.

Yes, it should be truncated at checkpoint, but sometimes disk space issue kills SQL Server just before it. So, if a disk with tempdb is full, checkpoint is not going to happen. So, sometimes you can do

BACKUP LOG tempdb WITH NO_LOG

DBCC SHRINKDATABASE (tempdb, TRUNCATEONLY)

But if you are complitely out of space, it will not work.

In this case re-start SQL Server services is only correct solution, because SQL Server is actuallly down anyway.

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

I;m still waiting on what the current settings of TempDB actually are for this problem so that we can figure out what "90%" actually means. 😉

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