URGENT - moving tempdb
-
Hi,
I need to move tempdb. On BOL it gives instructions on how to do this. so far so good. Howerver, it also says that the db must be in exclusive access mode. In EM, it doesn't seem to give an option to put tempdb in single user mode. How do I schieve this?
I have to do this in just over one hour, so if anyone out there knows, please post me!!
-
To put a database in single user mode use the query analyzer.
ALTER DATABASE tempdb
SET SINGLE_USER
To put it back in normal mode use
ALTER DATABASE tempdb
SET MULTI_USER
I would advise not using EM, since I do not know if and how many connections it makes to tempdb by default.
-
Here is an article from the Microsoft Knowledge Base:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q224071
It gives step-by-step instructions on moving databases, including TempDB.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Thanks for the replies , and aplogies for doubble post.
It is from this article I am working and
quote:
You should have exclusive access to the database being moved. If you encounter problems during the process and are unable to access a database you have moved, or are unable to start SQL Server, check the SQL Server error log and SQL Server Books Online for details on the errors encountered.
Howerver when I ran the SET Single_User I got:
Option 'SINGLE_USER' cannot be set in database 'TEMPDB'.
How can I ensuer I have exclusive access?? Sorry if I am being dim.
-
Use the system stored procedure sp_who or the well-known but undocumented system stored procedure sp_who2 to find out what processes are accessing TempDB other than you. If it's a user process, you have the option of executing a KILL on the SPID in question. However, I'm guessing you've made sure no one is using the SQL Server except you... in which case, it could be one of your own processes using TempDB as well.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
So, are you saying that I should be able to ser tempdb to single user mode?
I have looked at sp_who2, and there are no users connected.
If I run the script to move tempdb, even though I am not explicitly in Single user mode, will it work if no-one is connected?
-
TempDB doesn't have to be in single-user mode in order to run the ALTER DATABASE commands cited in the article. I ran a successful test with two users explicitly connected to TempDB.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Thanks Brian,
I have to do this in 10 minutes - so here goes. Funny, I put your book on the lobby table to bring to work today (SQLAdmin 911), and then rushed out without it!! Still, I got to speak to the real thing - cool!!
Thnak you so much for your help!!
-
I think you're confusing me with Brian Knight.
Too many Brian's running around! He's a co-owner of this site and often responds in the forums as well, though.K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Ooops - sorry!!
Anyway, the hour has passed, and everthing went OK, so you are still cool!! I have recently moved from DEV to DBA, and I still panic everytime I have to do something on a live box.
Thanks a million!!
-
Glad to be of help. Actually, being mistaken for Brian Knight is a good thing... for me. Probably not for him, though!
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Too many Brian's running around! He's a co-owner of this site and often responds in the forums as well, though.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply