Solved

How to move existing transaction log to another disk

Posted on 2007-11-14
3
793 Views
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.
/Cap
0
Comment
Question by:JadeBear
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 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.
0
 

Author Comment

by:JadeBear
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.

Regards,
Cap
0
 
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
install ASE 16 side by side with ASE 15.7 15 880
Create HTML sales dash board 2 438
Lazarus: saving BLOBs from Sybase database 3 164
remote logins for proxy table in sybase 5 69
After hours on line I found a solution which pointed to the inherited Active Directory permissions . You have to give/allow permissions to the "Exchange trusted subsystem" for the user in the Active Directory...
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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