16
DUMP TRANSACTION for database '%S_DBID' failed: log page (%ld) is on a data-only segment. This is probably due to an incomplete move of the log to a device separate from the data. Complete the log migration or contact the System Administrator.
The dump transaction command copies the transaction log, recording any database changes made since the last database dump or transaction log dump. After copying, it truncates the inactive portion of the log. Transaction log dumps are only possible when data and log reside on separate devices.
If data and log share device space, it is possible to move the transaction log to its own device by using the system stored procedure sp_logdevice.
Error 4222 is raised when you execute dump transaction, but the first page of the transaction log is found to be on a data-only segment of the database. Since the log is not isolated from the data, the transaction dump fails.
The likely cause of this error is that the steps to move the log to a separate device were initiated, but were not complete at the time you issued dump transaction. To understand this, note that sp_logdevice moves future allocation for the transaction log to the new device. However the log remains on the original device until the extent that is currently in use has been filled and the transaction log has been dumped. Error 4222 indicates that the log in its current state cannot be migrated to its own device.
To resolve the error:
Run dbcc checktable (syslogs) to determine which page is currently in use.
Execute enough transactions to fill the extent currently in use. The following commands will fill at least one extent on the log (8 pages):
1> create table dummy1 (c1 char(255), c2 char(255))
2> go
1> insert dummy1 values ("a", "a")
2> go 16
1> drop table dummy1
2> go
Confirm that there are no currently active transactions on the database device, then run dump transaction with truncate_only.
If an active transaction causes a begin tran to
be written to the log pages in question, a transaction dump will
not be able to truncate those pages. This is why you should avoid
having activity on the device while you migrate the log off the
data segment.
Run the system procedure sp_helplog to ensure that the complete log is on the new device.
Refer to “Moving the transaction log to another device” in the chapter “Creating and Managing User Databases” in System Administration Guide: Volume 2 for details on moving the transaction log.
All versions
