February 14, 2012 at 11:06 am
1) restart the SQL Server 2008 instance (e.g., stop the SQL Server Service in Windows and restart it or just reboot the server)
2) run this command as a login/user with sufficiently high permissions:
ALTER DATABASE tempdb
REMOVE FILE
February 14, 2012 at 11:23 am
I wouldn't consider option 2 workable or even possible. I haven't tried it but I'm guessing that the command will be rejected, hopefully. Regardless, I wouldn't try it.
As to clearing tempdb, yes, the restart will bring it back to the original size. You can also do some monitoring to see what activity is hitting tempdb and either work to tune those queries or stop them if they are rogue. Tempdb only grows when it needs to and if you have a real need for it to be as big as it is then you should let it remain that size and plan for that accordingly.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2012 at 11:40 am
I would not do it via option #2.
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
February 14, 2012 at 11:47 am
The second one won't clear tempdb. You can only remove an empty file. If tempdb has empty files, you could do this to remove them, but you won't be able to remove the primary data file with it. Hence, it won't "clear" the database.
Why do you want to clear tempdb? Rogue objects persisting when they probably shouldn't be? Data file growing? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 14, 2012 at 11:57 am
GSquared (2/14/2012)
The second one won't clear tempdb. You can only remove an empty file. If tempdb has empty files, you could do this to remove them, but you won't be able to remove the primary data file with it. Hence, it won't "clear" the database.Why do you want to clear tempdb? Rogue objects persisting when they probably shouldn't be? Data file growing? Something else?
Interview question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2012 at 12:12 pm
Based on the other questions that the OP posted, I'm beginning to guess yes....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply