Most of you probably already know this but just to make sure… There are three kinds of operations from a transaction log perspective:

1. logged operations: those are operations which are completely logged in the transaction log.
2. minimally logged or non-logged operations which are logged in the transaction log but not every change performed by this operation is logged. They do not prevent a dump and restore of the transaction logged
3. minimally logged or non-logged operations which are not logged in the transaction log at all.

Here are a few example of each type of operations:

1. insert into, update, delete
2. truncate table
3. select into, fast bcp, parallel sort

When an operation of the third category is performed, since the transaction log entries are missing, a dump and restore of the transaction log only is not possible anymore. This means ASE is not able to recover the database in a disaster recovery scenario unless you take a full dump of the database. Since the dumped transaction log does not contain the required information, ASE prevents you from dumping the transaction log once one of these operations has been performed because you couldn’t use the dumped log to recover the database anyway. Many people tend to think that truncate table also prevents a transaction log dump, which is not the true. Truncate table does not log every deletion in the table and is thus not a fully logged operation but it does log all page deallocations in the transaction log so that it’s still possible to reconstruct the database. So if you rely on a transaction log dump to recover the database or if you use it to migrated data from a system to the other one, it is important to:

• prevent such operations to happen
• check whether such operations have been performed

prevent such operations to happen

This can be done by setting an option on the database:

master..sp_dboption mydb, "select into/bulkcopy/pllsort", false
go
use mydb
go
checkpoint
go

Replace mydb by the name of the database you want to prevent such operations on. With this option set select into, fast bcp and parallel sort operations will not be allowed anymore on this database.

check whether such operations have been performed

You can use the following query if such operations are not prevented as shown above.

select tran_dumpable_status('mydb')

If it returns 0, then everything is fine and a dump of the transaction log should work fine. Otherwise, such an operation was performed and it is not possible to dump the transaction log until a full dump of the database is performed.

If the returned value is not 0, you can find out exactly what happens by interpreting the return bit mask:

• 1 – the specified database name was not found.
• 2 – there is no separate log device for this database.
• 4 – the log first page is on a data-only disk fragment.
• 8 – the trunc log on chkpt option is set for the database.
• 16 – an operation preventing a dump of the transaction log was performed.
• 32 – a transaction log dump with truncate_only was performed.
• 64 – it’s a new database and a full database dump must be performed before you can dump the transaction log.

Here are a few explanations on the different values:

• 1 – this mostly means that you’ve misspelled the database name.
• 2 or 4 – the transaction log needs to be entirely on a separate transaction log device for the dump of the transaction log to work.
• 8 – the database is configured to truncate the transaction log on checkpoint. This means that the transaction log is mostly empty except for things which happened since the last checkpoint. A dump of the transaction log then makes no sense, since most of the changes performed have already been discarded. see this post for more information.
• 16 – a select into, fast bcp or parallel sort has been performed and the transaction log would be useless anyway.
• 32 – the transaction log has been truncated by this command and the chain has been broken.
• 64 – the transaction log contains the changes applied since the last full dump so you need at least one full dump in order to use it.

If you’re still working with a pre-15 (e.g. 12.5) version of ASE, the tran_dumpable_status will not be available as it was introduced in ASE 15. Instead, you’ll have to read from the dbinfo structure. This can be done using:

dbcc traceon (3604)
go
dbcc dbinfo (my_db_name)
go

Replacing my_db_name by the name of the database you want to check. And then look for offset 128=dbi_ldstate. If the value is 4, 8 or 12, it means you cannot dump the transaction log for this database. Here’s what the different values mean:

• 4 (DBI_NONLOGGED_WRITES) means that a non-logged write was performed
• 8 (DBI_TRUNCATED_ONLY) means that the log was dumped with truncate_only
• 12 means both

In all three cases, a normal dump of the transaction log will not be possible.

The dbinfo structure is also accessible through the keys1 column of sysindex for syslog (id = 8). So you can alternatively use the following statement:

SELECT CONVERT(BINARY,SUBSTRING(keys1,129,1)) & 12 FROM my_db_name..sysindexes WHERE id = 8

Replacing my_db_name by the name of the database you want to check. This will also return 0, 4, 8 or 12. It is possible to perform a normal dump of the transaction log only if it returns 0.

So if you do not need any of the operations which prevent the transaction log from being dumped, the best solution is to prevent them on the database level. Otherwise, when you need to perform a transaction log dump (e.g. because the transaction is full or because you need to migrate the changes to a replicated database), you should first check whether a transaction log dump would be possible and trigger a full dump instead if not.