Cannot shrink tempdb log file

Question

Post reply

Cannot shrink tempdb log file

Tanveer-700199

Ten Centuries

Points: 1230
More actions
December 8, 2008 at 5:55 am

#216178

HI,

After running the query

backup log tempdb with truncate_only

dbcc shrinkfile(templog1,1000)

I got the following error :

Msg 8985, Level 16, State 1, Line 1

Could not locate file 'templog1' for database 'tempdb' in sys.database_files. The file either does not exist, or was dropped.

When checked in sys.database_files I got only 1 data and 1 log file. However when checked in sysaltfiles table there are 7 data and 7 log files. The logical name, physical path of these extra files are exactly the same when compared with database properties.

Any idea what could be the issue ? Are these extra files used or not ?

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

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

maechismo_8514

SSCoach

Points: 17510

December 8, 2008 at 7:31 am

#909131

Why are you trying to truncate the file anyway? Just restarting the server will clear the space used by tempbDB logfile.....

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

One other thing to note is that shrinking any tempDB files while TempDB is in use may result in corruption.

http://support.microsoft.com/kb/307487 (see right at the bottom, the section "Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use")

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 728159

December 8, 2008 at 12:48 pm

#909317

You should be using ALTER DATABASE to change the size and then restart SQL Server.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Tanveer-700199 Ten Centuries Points: 1230 More actions · Answer 4

Thanks for reply .

Actually we had an activity last week because of which the size of log files has increased and now we are falling short of space hence I need to shrink the log file and release the unused space back to disk. I am carrying out this activity late night hence it won't be used during truncation activity.

Please assist..

Tanveer-700199 Ten Centuries Points: 1230 More actions · Answer 5

Thanks for reply .

Actually we had an activity last week because of which the size of log files has increased and now we are falling short of space hence I need to shrink the log file and release the unused space back to disk. I am carrying out this activity late night hence it won't be used during truncation activity.

Please assist..

Jerry Hung SSChampion Points: 12908 More actions · Answer 6

Interesting, this week I tried shrinking tempdb data & log to clear up some space (just trying for fun, as restarting SQL Server was more difficult to achieve). Data is about 1.6 GB, same as Log

Server is SQL 2000

I did it in SSMS 2008 GUI, it shrunk the log for me, not the data.

So tempdb.mdf is still 1.6GB for now, and templog.ldf is 768KB

SQLServerNewbie MCITP: Database Administrator SQL Server 2005

sgambale SSCommitted Points: 1709 More actions · Answer 7

My tempdb, which hardly ever gets to over 10GB (almost all of it data), got to within 1 GB of the space limit on the drive it's on.

I wound up doing a truncate of the log (even though it was very small, about a few hundred MB), then did a shrinkfile on the data file, then a shrink database. Any attempts to do it w/o the truncate of the log did not shrink the file or the database.

This is a prod sql server and I can't recycle it whenever I want to. However, I'm not thrilled to read the MS article about possible corruption.

Isn't there some other way for tempdb's data part to shrink? Is there nothing in the sql server 2005 db engine that trims the data part of tempdb as a periodic maintenance function that is done in the background?

Steve

SQLRNNR SSC Guru Points: 281324 More actions · Answer 8

sgambale (3/30/2010)

My tempdb, which hardly ever gets to over 10GB (almost all of it data), got to within 1 GB of the space limit on the drive it's on.

I wound up doing a truncate of the log (even though it was very small, about a few hundred MB), then did a shrinkfile on the data file, then a shrink database. Any attempts to do it w/o the truncate of the log did not shrink the file or the database.

This is a prod sql server and I can't recycle it whenever I want to. However, I'm not thrilled to read the MS article about possible corruption.

Isn't there some other way for tempdb's data part to shrink? Is there nothing in the sql server 2005 db engine that trims the data part of tempdb as a periodic maintenance function that is done in the background?

Steve

You should start a new thread with this so it can get the appropriate attention.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events