May 7, 2002 at 8:50 am
I want to move Datafiles and transaction longs to another disk, as the present disk is full.
Can I move the datafiles and transaction logs destination my Enterprise Manager??
Thanks in advance
Chandu
May 7, 2002 at 8:56 am
Hey Chandu,
Easy way is to use sp_detatch_db and sp_attatch_dp
For example
exec sp_detatch_db 'MyDB'
exec sp_attach_db @dbname = N'MyDB',
@filename1 = N'e:\mssql\data\MyDB.mdf',
@filename2 = N'l:\mssql\logs\MyDB_log.ldf'
Hope that helps!
Clive
Clive Strong
May 7, 2002 at 9:30 am
Clive
Thanks very much...
I hv some queries regarding the same.
1) Can I move the datafile through Enterprise Manager
2) Do I have to move the datafiles before I deattah and attach using Explorer?
3) Do I hv Shutdown the Database?
Regards
Chandu
May 7, 2002 at 9:54 am
Chandu,
1) Yes you can. If you right click on the database, go to "All Tasks" and then detatch Database.
2) Once Detatched, move the files to wherever you want them Then reattatch by right clicking on the "Databases" node -> All Tasks -> Attatch Database... Navigate to find your database files then specify the new DB Name (if Changing) and the Owner of the DB.
3) You do not have to shutdown the Server.
Hope that helps!
Clive
3)
Clive Strong
May 7, 2002 at 9:56 am
You can detach and attach thru EM by right click the database and do task, detach then when moved right click database folder and do task attach, there you can browse for the mdb and specific the new location of the log.
You must move after detach and before attach thru Explorer.
This DB will be offline until done. You should wait until no one is in it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 11:00 am
Thanks very much for the details.
But, it appears that the detach option is disabled on the Database. On right clicking the Database (e.g Master) in the EM I see detach database option, but the same is disabled.
When I right click on the Databases option, I can only see “attach database” option.
Regards
Chandu
May 7, 2002 at 11:06 am
You would not be able to detatch the master database while the Server is running. When you detatch & reatatch databases it reads/writes to/from the master database.
You can only detatch/reatatch Databases which are not system DB's.
Not entirely sure of the process to do this with system db's. Maybe you have to start in Single user mode (sqlservr.exe -c -m from the command prompt) or stop the SQL Server Service and then do it. Need to look in to that one.
Clive
Clive Strong
May 7, 2002 at 11:16 am
I forgot to mention that we are using SQL Server Version 7. I don't any of those options in SQL Server 7.
I was actually looking detach database option on SQL Server 2000 installed in my laptop before carrying the same in the LIVE Database. I also checked for Nothwind Database (SQL Server 2000) and noticed that detach option is disabled.
Live Database is running in SQL Server V7. However, I find that these features are not available in SQL Server V7.
How do we move datafile in Version 7 ??
My apologies for not mentioning about the version.
Thanks
Chandu
May 7, 2002 at 11:24 am
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071 for details on how to move the various system databases for both 7 and 2K.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 9, 2002 at 3:44 am
I moved the datafile for one database. However, when I was trying run sp_detach procedure for other database I got following error:
Server Msg 3702 Level 16, State 1,. Line 0
Cannot drop the database helpdesk because it is currentl in use.
DBCC execution printed error message, contact system admn...
How do I force all users to log off? Or can I start the database in the restricted mode to carryout this operation?
Thanks in advance
Chandu
May 9, 2002 at 4:05 am
Hey Chandu,
There are a few things to check...
1) Are there any users connected to any applications which access your HelpDesk DB? If so, have them log out.
2) Are you using Query Analyser and have a session connected to the HelpDesk DB? If so, again, log out.
3) If none of the above, check your Current Activity -> Process Info. You could have existing connections to the HelpDesk DB. If these are not active connections, you can kill these.
Are you trying to move these files on the live server? If so, personally, I'd schedule this kind of job outside of working hours..!!
See how that goes.
Clive Strong
May 9, 2002 at 4:47 am
Another spot people miss is did they connect to the DB in EM. Close EM to release connection or change DBs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 9, 2002 at 5:27 am
Thanks once again for your inputs...
EM as well as Query Analyzer was open when I was performing above. But, I don't think I was connected to Helpdesk DB.. It is possible that some users must hv been connected...
Can I start the database in restricted mode to perform these operations?....I agree that this operation has to be performed after working hours, but I can shutdown the database say for 15 mins...
I also noticed that status of some users is "sleeping" against helpdesk database...
Thanks,
Chandu
May 9, 2002 at 5:41 am
Usually you can start a DB in single user mode or the server itself in single user mode. But if you have connections until they go away they can lock you out. Sleeping means the connection is still considered live but no transactions are taking place. Could be they are done and the connection timeout has not been reached or they have just finish/not started a transaction.
I would shut down SQL and restart to clear all connections but do not restart SQLAgent until you are done. If anyone other than yourself appears at that time you can use kill on the spid to kock them out (see BOL for more detaisl). But be carefull not to kill a system process.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply