How to move transaction logs on-line?

[nai]

Hi all,

I have a database with 20GB transaction log file.
The recovery model of the database is Full.
I need to move the T. log file to a new location with the minimal
downtime.
I know I can do this by dettaching the DB, copying the T. Log and
attaching it at the new location. This will take some time though as
copying the T. log file will take up some time.

I thoght that I could maybe create a secondary T. log file and delete
the primary T. log file.
What does it take so I'm able to delete the primary T. log file? Can
you please explain how to accomplish this?

Also, if you can figure out of a better way, then please let me know.

[Robert Klemme]

nai wrote:

Hi all,

I have a database with 20GB transaction log file.
The recovery model of the database is Full.
I need to move the T. log file to a new location with the minimal
downtime.
I know I can do this by dettaching the DB, copying the T. Log and
attaching it at the new location. This will take some time though as
copying the T. log file will take up some time.

I thoght that I could maybe create a secondary T. log file and delete
the primary T. log file.
What does it take so I'm able to delete the primary T. log file? Can
you please explain how to accomplish this?

Also, if you can figure out of a better way, then please let me know.

Do you regularly do backups and shrink the TX log? If not, that's the
first thing I'd do.

Kind regards

robert

[Cimode]

1)backup you log to a specific local location...
2) copy it on another machine (using xp_cmdshell)
3) call for a restore log by remotely calling a repote procedure on the
destination instance (you need to declare the remote instance as a
linked server first though)

Hope this helps...
nai wrote:

Hi all,

I have a database with 20GB transaction log file.
The recovery model of the database is Full.
I need to move the T. log file to a new location with the minimal
downtime.
I know I can do this by dettaching the DB, copying the T. Log and
attaching it at the new location. This will take some time though as
copying the T. log file will take up some time.

I thoght that I could maybe create a secondary T. log file and delete
the primary T. log file.
What does it take so I'm able to delete the primary T. log file? Can
you please explain how to accomplish this?

Also, if you can figure out of a better way, then please let me know.

[Erland Sommarskog]

nai (ni********@laiki.com) writes:

I have a database with 20GB transaction log file.
The recovery model of the database is Full.
I need to move the T. log file to a new location with the minimal
downtime.
I know I can do this by dettaching the DB, copying the T. Log and
attaching it at the new location. This will take some time though as
copying the T. log file will take up some time.

I thoght that I could maybe create a secondary T. log file and delete
the primary T. log file.
What does it take so I'm able to delete the primary T. log file? Can
you please explain how to accomplish this?

It appears you cannot remove the primary log file.

I would:

1) Backup the transaction log.
2) Shrink the log file.
3) Throw out users.
4) Detach database.
5) Copy log file (which is now small).
6) Attach database.
7) Let users in.
8) Increase log file to 20 GB again.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx