Error 4222

Severity

16

Message text

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.

Explanation

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.

Action

To resolve the error:

  1. Run dbcc checktable (syslogs) to determine which page is currently in use.

  2. 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
    
  3. Confirm that there are no currently active transactions on the database device, then run dump transaction with truncate_only.

    NoteIf 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.

  4. Run the system procedure sp_helplog to ensure that the complete log is on the new device.

Additional information

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.

Versions in which this error is raised

All versions