August 2, 2008 at 12:41 pm
Comments posted to this topic are about the item Moving TempDB
August 4, 2008 at 4:52 am
Andy,
Nice article!
I believe if the tempdb had grown to a very large size restarting the instance would cause it to be deleted and recreated with the default tempdb size, which is useful to know if your tempdb gets too big... there are other better ways of shrinking it but I seem to recall an issue where tempdb wouldn't shrink but restarting the instance fixed [shrunk] it... it was a non-production machine and I can't recall the specifics though
Mark
August 4, 2008 at 5:05 am
Thanks Mark! Absolutely restarting the service is a valid shortcut if you can afford the few minutes of down time.
August 4, 2008 at 10:07 am
If you created a table in Model on restart it would be in TempDB and in the state created. Am I reading correctly here? Then:
(1) I have a place that I can log what happened since the last restart.
(2) I can detect that a restart has happened.
Further this could lead to serious abuse.
We have been conducting interviews for new candidates. We actually give them broken code to work on and a set of bug reports. We could use this to put a copy of the tables in Model and all our data would be back to pristine on reboot. We already copy the broken code back on reboot.
ATBCharles Kincaid
August 4, 2008 at 12:11 pm
Charles, one thing to watch out for. The new table you're talking about adding to the Model database would not only be added to tempdb on reboot, but it would also be added to any new database you create.
Instead of adding something thing like that to tempdb you could consider a SQL server agent job that would recreate the table for you. This job could be set to run on startup.
Just a thought.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 4, 2008 at 3:29 pm
Thanks for the good thought, Alvin. We run SQL Express on that laptop. We have crippled the chip set for the wireless card, the LAN card, and the USB controller. The only thing that laptop is used for is conducting employment tests. No open book (Google) tests here.
ATBCharles Kincaid
August 4, 2008 at 3:40 pm
Charles, I just had a sort of funny thought. If you go ahead with was said in here, or use tempdb, you might want to recommend to the interviewees NOT to reboot the machine. Could be fun for you if they did but I'm sure they would not find it funny.
(can't you tell I did not get enough sleep last night and the evil me is starting to show itself?)
😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 4, 2008 at 3:49 pm
You think I'm bad, the guy who designed the test found a bug in VS 2008. The way the code is broken throws Intellisense off. It makes wrong recommendations that will just have you going in circles. That's the hard part. It had most lightweights so flustered that they can't even see that the SQL statement in the next part is just missing the ... Uh, wait. Some people might be reading this and preparing resumes.
ATBCharles Kincaid
August 4, 2008 at 8:07 pm
Very nicely done, Andy... great walkthrough on what to look for and when it takes affect.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2008 at 12:21 am
Great article, nice stuff....
August 6, 2008 at 5:52 am
nice stuff. Normally, people overlook the delicacy of the tempdb and its location. It must have the appropriate space to work, especially when you are creating and re-building indices.
Great Stuff
August 6, 2008 at 5:54 am
Restarting instances will truncate tempdb BUT the drawback is the downtime. There are alot of examples where one cannot even think about restarting instance frequently. It is only possible on Occasional basis.
Atif Sheikh
August 6, 2008 at 6:02 am
Atif Sheikh (8/6/2008)
Restarting instances will truncate tempdb BUT the drawback is the downtime. There are alot of examples where one cannot even think about restarting instance frequently. It is only possible on Occasional basis.Atif Sheikh
Right! Depends on the environment. We have Dev and QA boxes that are restarted but our prod boxes couldn't be...
August 6, 2008 at 6:25 am
Thanks everyone! Charles, that's an interesting use of an idea - the best part about content on SSC is reading the discussion that goes with it!
August 21, 2008 at 2:07 am
We tried to do this (including the removing of the old tempdb files), and it crashed SQL server completely... No idea what went wrong, but we're down to reinstalling completely 🙁
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply