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

The TEMPDB Expand / Collapse
Author
Message
Posted Sunday, August 25, 2013 6:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113








































Dear All,

I have run into a situation wherein some maintenance is required on the Cluster disk that hosts this TEMPDB. So iam having to temporarily move the files residing in there to elsewhere during this downtime. Once the physical disk maintenance is complete I would put back the files to where they were.

What are the things to be considered and how can I go about it completing it smoothly :) ?

One other question is doing such a maintenance on the sqls tempdb, will it affect the other system or other databases?

Many thanks,
Jai
Post #1488270
Posted Monday, August 26, 2013 1:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
Use alter DB and move the files, restart the services to take the settings effective as SQl resets tempdb during startup.



Regards
Durai Nagarajan
Post #1488310
Posted Monday, August 26, 2013 7:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Thanks Durai! My thoughts were like this:

- shutdown the db
- move the files from this disk/drive(X) to another temporary disk/drive(Z)
- perform whatever maintenance to this disk drive X such as format, replace, etc...
- once maintenance is done, ensure the disk drive X is available;
- copy the temp files back from Z to the disk drive X
- startup the db

Will this work!?

Thanks..
Post #1488522
Posted Monday, August 26, 2013 9:02 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: Thursday, October 16, 2014 6:18 PM
Points: 3,961, Visits: 7,170
You can omit this step " copy the temp files back from Z to the disk drive X"

When the SQL services is restarted, the tempdb files are recreated - there is no need to copy them/back them up to a "safe" place...


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1488531
Posted Monday, August 26, 2013 10:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Thank you!
Post #1488549
Posted Tuesday, August 27, 2013 1:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
mJai (8/26/2013)
Thanks Durai! My thoughts were like this:

- shutdown the db
- move the files from this disk/drive(X) to another temporary disk/drive(Z)
- perform whatever maintenance to this disk drive X such as format, replace, etc...
- once maintenance is done, ensure the disk drive X is available;
- copy the temp files back from Z to the disk drive X
- startup the db

Will this work!?

Thanks..


like MyDoggieJessie said you can ignore the tempdb back to drive X

but why not using alter and restart sql it will have tempdb on different location and DB will be up and running in the interim.


Regards
Durai Nagarajan
Post #1488577
Posted Tuesday, August 27, 2013 4:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Good idea, I think i can try that too, Durai. Thanks!
Post #1488659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse