how to check transaction log device 1

[babeo]

Hi

- Could some one tell me what is the command to check which database has a seperate transaction log device? And is this the command to do backup on the transaction log?

Dump trans [databasename] to [directory/filename]

What are the options "stripe on" and "nounload|unload" mean?

- How to turn off the database options "trunc. log on chkpt" and "select into/bulkcopy/pllsort" ? what are these doing that affect the restore later?

Thanks for your help

 

[JMCraig]

Sorry you've been hanging out there waiting for an answer so long.

I've put your original questions here in bold so they're easy to spot.

what is the command to check which database has a seperate transaction log device?

sp_helpdb dbname

Will allow you to check the setup for a given DB. If you have many dozens of databases you need to check, I can give you a different approach that's a bit more complicated.

And is this the command to do backup on the transaction log?

Dump trans [databasename] to [directory/filename]


Yes, that is the command. However, as noted below, it won't work if your DB has the truncate log on checkpoint option enabled.

What are the options "stripe on" and "nounload|unload" mean?

strip on allows you to do a backup to multiple files. I don't recommend it for transaction dumps and I wouldn't bother with it at all unless you have a reason to improve the performance of your dumps at the expense of a substantial headache during recovery (and if you copy your files to tape, a headache at that point too).

nounload|unload only applies to dumps directly to a tape drive; if you dump to a disk file, ignore them. nounload leaves the tape in place--but this may interact with what type of device the tape drive is defined as via Unix. unload tells the system to rewind the tape (and perhaps even pop open the tape drive door depending upon the system and whether that's even possible to do via software).

How to turn off the database options "trunc. log on chkpt" and "select into/bulkcopy/pllsort" ?

Use sp_dboption to enable/disable these options. The general pattern looks like this (you'll need to be logged in as SA or have the sa_role granted to your login):

use master
go
sp_dboption dbname, "trunc", false /* use true to enable */
sp_dboption dbname, "select into", false
go
use dbname
go
checkpoint
go


What are these doing that affect the restore later?

Truncate Log on Checkpoint
This affects two somewhat different things:
1. You can't dump the transaction log (so you can't do an incremental backup at all).
2. During recovery you can't capture the transactions that occurred since your last DB dump; so you cannot recover up to the minute.

In general, this isn't a way to run a production DB; all you're doing is making it impossible to use capabilities of Sybase that make it more capable of handling disk or other hardware failures.

Select into/bulk copy/...
This option allows some kinds of non-logged (that is to say, fast short-cut) operations to occur. Now, as long as no non-logged operation actually does occur, the option itself has no effect on backing up or recovery. But, if a non-logged operation does occur, it does two things:
1. It prevents you from dumping the transaction log (it's now incomplete because the non-logged operation has invalidated it: the log no longer contains a complete accounting of the operations on the DB). This is functionally the same as #1 above under the discussion of log truncation on checkpoint.
2. If a failure should occur while your log is invalid, you cannot recover right up to the moment of failure.

I strongly suggest reading the chapters that cover backup and recovery in the Sybase Sys Admin Guide (see the FAQ on "Where can I read about...?" for help finding the document). You can also contact me directly for more information.

In addition to disabling both these options in a production database, I'd also recommend mirroring your transaction log devices and master device (put the mirrors on separate disk drives or drive arrays).

Good luck--and definitely take advantage of Sybase's ability to recover right up to the moment of failure; it costs almost nothing to do so. And, it is greate to be able to smile at your boss and say, in answer to the question "What did we lose?": "Oh, I had the appropriate processes in place so we lost nothing at all." After you explain how careful and brilliant you were, you politely ask if they'd mind sending a memo to their boss about what a fine job you did!

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[JMCraig]

Sorry you've been hanging out there waiting for an answer so long.

I've put your original questions here in bold so they're easy to spot.

what is the command to check which database has a seperate transaction log device?

sp_helpdb dbname

Will allow you to check the setup for a given DB. If you have many dozens of databases you need to check, I can give you a different approach that's a bit more complicated.

And is this the command to do backup on the transaction log?

Dump trans [databasename] to [directory/filename]


Yes, that is the command. However, as noted below, it won't work if your DB has the truncate log on checkpoint option enabled.

What are the options "stripe on" and "nounload|unload" mean?

strip on allows you to do a backup to multiple files. I don't recommend it for transaction dumps and I wouldn't bother with it at all unless you have a reason to improve the performance of your dumps at the expense of a substantial headache during recovery (and if you copy your files to tape, a headache at that point too).

nounload|unload only applies to dumps directly to a tape drive; if you dump to a disk file, ignore them. nounload leaves the tape in place--but this may interact with what type of device the tape drive is defined as via Unix. unload tells the system to rewind the tape (and perhaps even pop open the tape drive door depending upon the system and whether that's even possible to do via software).

How to turn off the database options "trunc. log on chkpt" and "select into/bulkcopy/pllsort" ?

Use sp_dboption to enable/disable these options. The general pattern looks like this (you'll need to be logged in as SA or have the sa_role granted to your login):

use master
go
sp_dboption dbname, "trunc", false /* use true to enable */
sp_dboption dbname, "select into", false
go
use dbname
go
checkpoint
go


What are these doing that affect the restore later?

Truncate Log on Checkpoint
This affects two somewhat different things:
1. You can't dump the transaction log (so you can't do an incremental backup at all).
2. During recovery you can't capture the transactions that occurred since your last DB dump; so you cannot recover up to the minute.

In general, this isn't a way to run a production DB; all you're doing is making it impossible to use capabilities of Sybase that make it more capable of handling disk or other hardware failures.

Select into/bulk copy/...
This option allows some kinds of non-logged (that is to say, fast short-cut) operations to occur. Now, as long as no non-logged operation actually does occur, the option itself has no effect on backing up or recovery. But, if a non-logged operation does occur, it does two things:
1. It prevents you from dumping the transaction log (it's now incomplete because the non-logged operation has invalidated it: the log no longer contains a complete accounting of the operations on the DB). This is functionally the same as #1 above under the discussion of log truncation on checkpoint.
2. If a failure should occur while your log is invalid, you cannot recover right up to the moment of failure.

I strongly suggest reading the chapters covering backup and recovery in the Sybase Sys Admin Guide (see the FAQ on "Where can I read about...?" for help finding the document.

In addition to disabling both these options in a production database, I'd also recommend mirroring your transaction log devices and master device (put the mirrors on separate disk drives or drive arrays).

Good luck--and definitely take advantage of Sybase's ability to recover right up to the moment of failure; it costs almost nothing to do so and it sure is slick to be able to smile at your boss and say, in answer to the question "What did we lose?", "Oh, I had the appropriate processes in place so we lost nothing at all."

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[JMCraig]

Sorry for the double post--it said it had timed out.
JMC
J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[babeo]

Thanks JMCraig,

You're very kind.
I like your last 3 answers, very detail and helpful.
Thanks again.