Moving the Logfile

Question

Post reply

Moving the Logfile

GST

SSC Rookie

Points: 36
More actions
April 29, 2005 at 9:09 am

#88855

Hi Guys,
I am fairly new to SQL Server but have plenty off background in Oracle, Ingres, and RDB/VMS.
A few years ago, contractors put togather a system for my current company using SQL Server, I am now charged with maintaining it.
I noticed that the Transaction Logfile is on the same raid set as the Datafiles. I thought everyone was taught in Database 101 that you never place data and logs on the same volume.
How do I move the Logfile to another drive.
Do I close the DB, backup the DB, delete the Logfile, recreate the logfile in another location, then restart the DB???
Can I do this using the SQL Server Enterprise Manager???/
Any help would be appreciated.

Thanks,
Gary

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Option 1. sp_detach_db / sp_attach_db

OPtion 2. Backup / Restore

You can use EM to do both but option 1 is the simplest in your situation.

See BOL for details.

BOL is Book Online which is installed as part of SQL Server by default.

GST SSC Rookie Points: 36 More actions · Answer 2

Allen,

Thank You for the swift reply.

As I said I am new to this forum, what do you mean by BOL.

Also instead of doing the detach and attach using stored procedures, can I do them using the Enterprise Manager?? If so what is the proper sequence of events to move the Logfile?

Thanks,

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

From EM --> Right Click your database --> All tasks --> Detach DB --> Make sure all connection to DB is 0 and Click OK.

Move th elog file to new drive.

From EM --> Right click Databases --> All Tasks --> Attach DB.

GST SSC Rookie Points: 36 More actions · Answer 4

Allen,

Sorry to be such a pain in the a--!

But after I detach the DB, "to move the log file to new drive" Can I:

Right click on the DB and select Properties,

Select the Transaction Log tab,

Delete the "Current" Log information,

Add the "New" Log information,

move the "physical" log file to the new location,

then Re-Attach the DB

Once again sorry for being a pain!

Thanks,

Gary

noeld SSC Guru Points: 96590 More actions · Answer 5

After you Detach, the Database Properties is not available (you won't be abe to see it in EM any more until you attach)

By the way when you attach you will have to specify the "new" location of the log file

Oh and BOL = Books OnLine

hth

* Noel