SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving TempDB


Moving TempDB

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Moderators
Points: 12119 Visits: 2730
Comments posted to this topic are about the item Moving TempDB

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2011 Visits: 711
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
Andy Warren
Andy Warren
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Moderators
Points: 12119 Visits: 2730
Thanks Mark! Absolutely restarting the service is a valid shortcut if you can afford the few minutes of down time.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1729 Visits: 2384
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
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 11639
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.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1729 Visits: 2384
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
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 11639
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?)

Tongue



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1729 Visits: 2384
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89479 Visits: 41144
Very nicely done, Andy... great walkthrough on what to look for and when it takes affect.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Anipaul
Anipaul
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7435 Visits: 1407
Great article, nice stuff....



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search