How to move existing transaction log to another disk

Posted on 2007-11-14
Medium Priority
Last Modified: 2012-06-22
My transaction log is currently on the same disk as my database, and I want to move it too another. I understand the steps involved, but don't get the syntax right:
1. use disk init to create a device on another disk
2. use alter database to allocate the device to the database
3. use sp_logdevice to specify the new device for logging
From the sysadmin guide I see that that's what to do. I must name the new device differently from the original; so it should be logdev02 rather than logdev01. Do I use sp_dbextend? In the original installation script it says, sp_dbextend 'set', 'database', mydbs, 'logdev01', '1024M'

All help gratefully accepted.
Question by:JadeBear
  • 2
LVL 24

Accepted Solution

Joe Woodhouse earned 2000 total points
ID: 20288048
I know that's what the manual says, and I find it more complex than it needs to be.

Note that the method you're using doesn't instantly release the old area - all existing transaction logs remain there until removed by some form of DUMP TRAN method (or "trunc. log on chkpt" option). You also can't remove the old device fragment from the database... so I don't think this is what you want.

I have a much easier (but slower) method for you that is also fully supported by Sybase: Sybase disk mirroring:

1) disk mirror name="current_log_device_name", mirror="path_and_name_of_device_on_new_disk"
2) disk unmirror name="log_device_name", side="primary", mode="remove"

This says mirror the device from the old location to the new, and then break the mirror leaving only the new. The old device is immediately unused and can be deleted at once. You don't have to increase the overall database size either.

Note you have to enable "disk mirroring" (needs a Sybase restart), and the first step of creating the mirror is very very slow, and suspends all activity that would write to the device (for a log device, that is all writes). But it always works and is conceptually very clean.

Author Comment

ID: 20288808
Thank you! I was hoping that something like that was possible (since that's what I've been using on other devices) but the SAG didn't mention anything so simple.

LVL 24

Expert Comment

by:Joe Woodhouse
ID: 20302554
Yes, it's odd given this is a fully supported and documented solution. It's even written up in the SAG, just not as a solution for moving the log... but a device is a device, it works for logs too.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There are more than 1000 cryptocurrencies available across the world, but Bitcoin has gained a significant advantage over others. The cryptocurrencies are capitalizing the market with more than $150 billion dollars.
Migrating Exchange data from one Exchange Server to another server is complicated. Though Exchange administrators can try manual methods to migrate their data from one version of Exchange to another, these manual methods are not that reliable. That…
If you are looking for an automated tool which can generate reports for Outlook emails and other items from PST file, then you can go for Kernel PST Reporter tool. The reports which are created by this tool are helpful to analyze and understand PST …
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question