Log file of temp db too big - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log file of temp db too big

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2007-02-05 : 17:04:40

Gurus
I have a scenario where the log file of my temp db is too big.What all options are available to me.Please let me know

Regards
Nitin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-05 : 17:05:48

Have you tried the obvious: shrinking it?

Tara Kizer

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2007-02-05 : 17:21:00

Hi Tara
Thanks for the reply.Can i do it online,will it allow me..
Any data loss chances?

Regards
Nitin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-05 : 17:22:31

It will impact users, but there will be no data loss. You should do it during a maintenance window.

BTW, you can also just restart the SQL Server service to shrink tempdb down to the same size as the model database. Tempdb is rebuilt during each startup based upon model.

Tara Kizer

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2007-02-05 : 17:30:35

Hey Tara
Thanks a lot for the help
You rock

Regards
Nitin

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 23:40:06

"Can i do it online,will it allow me..
Any data loss chances?"

Afraid I have to disagree with Tara.

Shrinking TEMPDB "online" is not recommended; there are special circumstances required in order to avoid corruption.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Shrinking%20TEMPDB,Shrinking,Shrink

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 11:09:28

What part do you disagree with me? I only said to do it during a maintenance window and that there would be no data loss. What part is wrong? The thread that you posted is too lengthy for me to find the exact part you are referring to.

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2007-02-06 : 11:38:10

I may just have misread what you implied, if so I apologise.

The most relevant bits from the linked threads is:

http://support.microsoft.com/default.aspx/kb/307487

Which says something to the effect that "SQL Server must be started in Single User mode before shrink is used"

(Which strikes me as unnecessary - if you stop/start SQL Server to get it into Single User mode isn't the side effect of that going to make a brand new TEMPDB anyway?)

And nitin1353 asked "Can i do it online, will it allow me.."

You did indeed say it should be done during a maintenance window, but I wasn't sure that nitin1353 read that as "All users offline".

I've probably read far too much into everything everyone said!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources