Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving TempDB Expand / Collapse
Author
Message
Posted Saturday, August 2, 2008 12:41 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, August 12, 2014 10:53 AM
Points: 6,783, Visits: 1,876
Comments posted to this topic are about the item Moving TempDB

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #545688
Posted Monday, August 4, 2008 4:52 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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
Post #545989
Posted Monday, August 4, 2008 5:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, August 12, 2014 10:53 AM
Points: 6,783, Visits: 1,876
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
Post #545997
Posted Monday, August 4, 2008 10:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #546207
Posted Monday, August 4, 2008 12:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,149, Visits: 7,979
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.
Post #546298
Posted Monday, August 4, 2008 3:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.

ATB

Charles Kincaid

Post #546403
Posted Monday, August 4, 2008 3:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,149, Visits: 7,979

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?)

:P




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.
Post #546408
Posted Monday, August 4, 2008 3:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.

ATB

Charles Kincaid

Post #546411
Posted Monday, August 4, 2008 8:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:17 PM
Points: 36,959, Visits: 31,473
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #546464
Posted Wednesday, August 6, 2008 12:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:09 AM
Points: 5,338, Visits: 1,385
Great article, nice stuff....


Post #547284
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse