Moving transaction log file

Hi folks,
Currently I have data file and transaction log file on the same drive. I
want to move the transaction log file to a different drive without any
outage to the database. What is the best way to do this.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***You will have to detach the database first , then to separate a mdf and ldf
file and re-attach the database
Pls refer to the BOL for more details
sp_detach ,sp_attach stored procedures
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:eLi8zCTBGHA.2356@.tk2msftngp13.phx.gbl...
> Hi folks,
> Currently I have data file and transaction log file on the same drive. I
> want to move the transaction log file to a different drive without any
> outage to the database. What is the best way to do this.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||http://support.microsoft.com/defaul...kb;en-us;224071
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:eLi8zCTBGHA.2356@.tk2msftngp13.phx.gbl...
> Hi folks,
> Currently I have data file and transaction log file on the same drive. I
> want to move the transaction log file to a different drive without any
> outage to the database. What is the best way to do this.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi,
Detach and Attach will have some outage to the database. I am looking
for an option which does not invlove any outage to the database.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||No such option. You could create another log file on the other drive, meanin
g for a while you have
two log files. Then use DBCC LOGINFO etc to watch and see when your original
log file is unused by
virtual log file and try DBCC SHRINKFILE with the empty file option to final
ly remove the original
log file using ALTER DATABASE. I'm not sure whether SQL Server allow you to
remove the very first
log file that a database had, though, so make sure you first try on a test s
ystem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%23TdysOTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Detach and Attach will have some outage to the database. I am looking
> for an option which does not invlove any outage to the database.
> Thanks.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Tibor wrote:
* Use DBCC SHRINKFILE with the empty file option to finally remove the
original
* log file using ALTER DATABASE. I'm not sure whether SQL Server allow
you to remove the very first
* log file that a database had, though, so make sure you first try on a
test system.
No, SQL Server won't allow you to remove the primary transaction
logfile. Tried it just last w, you'll get the following error:
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
Markus|||Last time I tried to move log file to other disk I forgot to set database
offline and it went corrupted. I couldn't recover database, fortunately it
was only a test one.
BOL states clearly that you have to set db offline before mooving files:
1.. Run ALTER DATABASE database_name SET OFFLINE.
2.. Move the file to the new location.
3.. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name,
FILENAME = 'new_path/os_file_name'.
4.. Run ALTER DATABASE database_name SET ONLINE.
Peter|||Another option is to backup database and then restore with "move" option.
Before backing up, set "read only" option on your database and remove "read
only" after you did a restore. May this will be a minimal outage on your
database.
Ramunas Balukonis
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ockc1BUBGHA.3400@.TK2MSFTNGP10.phx.gbl...
> No such option. You could create another log file on the other drive,
meaning for a while you have
> two log files. Then use DBCC LOGINFO etc to watch and see when your
original log file is unused by
> virtual log file and try DBCC SHRINKFILE with the empty file option to
finally remove the original
> log file using ALTER DATABASE. I'm not sure whether SQL Server allow you
to remove the very first
> log file that a database had, though, so make sure you first try on a test
system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
> news:%23TdysOTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
>