Most of you probably already know this but just to make sure… There are three kinds of operations from a transaction log perspective:
- logged operations: those are operations which are completely logged in the transaction log.
- 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
- 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:
- insert into, update, delete
- truncate table
- 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.
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.
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.