메시지 3456, 수준 16, 상태 1, 줄 1
10퍼센트 처리되었습니다.
20퍼센트 처리되었습니다.
30퍼센트 처리되었습니다.
40퍼센트 처리되었습니다.
50퍼센트 처리되었습니다.
60퍼센트 처리되었습니다.
70퍼센트 처리되었습니다.
80퍼센트 처리되었습니다.
90퍼센트 처리되었습니다.
100퍼센트 처리되었습니다.
파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Data'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Data2'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Data3'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_GUEST'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_z1'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Log'에 대해 234355개의 페이지를 처리했습니다 .
메시지 3456, 수준 16, 상태 1, 줄 1
페이지 (1:828275), 데이터베이스 'userDatabase'(데이터베이스 ID 5)에서 트랜잭션 ID (16:395253956)에 대한 로그 레코드 (3529369:96028:38)을(를) 다시 실행할 수 없습니다. 페이지: LSN = (3529369:95946:19), 유형 = 2. 로그: OpCode = 3, 컨텍스트 19, PrevPageLSN: (3529369:95244:5). 데이터베이스 백업에서 복원하거나 데이터베이스를 복구하십시오.
메시지 9004, 수준 16, 상태 6, 줄 1
데이터베이스 'userDatabase'의 로그를 처리하는 동안 오류가 발생했습니다. 가능하면 백업을 사용하여 복원하십시오. 백업이 없을 경우 로그를 다시 만들어야 합니다.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE LOG이(가) 비정상적으로 종료됩니다.
내용을 보니 현재 처리중인 LSN (3529369:95946:19) 과 이전 LSN (3529369:95244:5) 이 맞지 않아 나는 에러인데....왜 이런 현상이 일어나는지는 이해할 수 없네...
테스트가 필요한 부분은 데이터베이스 커럽션이 있는지
차등백업 후 로그백업을 복구하면 에러없이 진행 가능한지 등이 있겠다.
최후의 수단으로 CONTINUE_AFTER_ERROR 가 있을 수 있다.
읽어볼만한 글
http://dba.stackexchange.com/questions/93473/unable-to-restore-error-3456
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/36dd0107-ceca-4d48-8523-3c5624c135b2/msg-3456-level-16-state-1-could-not-redo-log-record?forum=sqldatabaseengine
https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/
복사본
In order to understanding why error 3456 would be thrown, we need to take a little step back and understand how SQL Server handles this corner of recovery.
When SQL Server is redoing an operation, and that redo is a page modification, it makes a quick check. In the page header there is ultimately going to be a PageLSN, which is an indication of the last LSN that has modified that page, recorded by the page. Think about it like this, the page keeps track of the last LSN that has made modifications to it. This is the PageLSN.
Every time there is a logged page modification operation, that log record includes a few LSNs. Namely, the log record's LSN (think... Current LSN), and then it has what's called the Previous Page LSN (PrevPageLSN going forward). So when we modify a page, one of the pieces of data that is put into the log record is what the page indicates as being the last LSN before you to have modified the page.
Think about it like this... Your car needs to have work done on it. Mechanic John works on your car, and in the engine bay it has a little tag and Mechanic John writes "John worked on this car last". Then the next time you take your car in to another shop, Mechanic Mark looks in the engine bay and sees that Mechanic John worked on this car last. On his data sheet he writes this information. Same idea with SQL Server.
This can be somewhat confusing, so take a look at this image below on sequential page modifications, and how the PageLSN and PrevPageLSN relate:
Let's loop back around, as this all comes into play when you need to redo an operation on a page (restores, recovery, HA, etc.). When SQL Server needs to redo a page operation, it makes a sanity check to see if the PageLSN on the page matches the PrevPageLSN that the log record includes. If that is not equal, then you will see error 3456 get thrown.
Does PageLSN equal PrevPageLSN? No??? Stop and raise error 3456...
Let's analyze your error message, which includes the how:
Could not redo log record (210388:123648:232), for transaction ID (0:1016710921), on page (4:8088), database 'SomeDB' (database ID 6). Page: LSN = (0:0:1), type = 11. Log: OpCode = 4, context 11, PrevPageLSN: (210388:122007:1). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.
I have bold'd the two pieces of data that have an inequality causing the error. You can see that our PageLSN is 0:0:1 (this was found in the page's header), and our PrevPageLSN is 210388:122007:1(this was found in the data on the log record that was attempting to be redone). These are obviously not equal, hence err3456.
So in order to find out the why of this event, would be to find out why there is a disparity here. We really need to trace the lifecycle of page 4:8088 and see where the disconnect is. Unfortunately without further information, or hands-on troubleshooting there isn't much else I can do besides give you the background of this recovery operation and what causes the error.
Problem
This tip describes SQL Server Log Sequence Numbers (LSNs) and how the sequence numbers link full, differential and transaction log backups. We will look at sample backups and how these LSN values can be read from the backup files to determine the restore path.
Solution
This tip is the continuation from this tip, Different Ways to Restore a SQL Server Database, and utilizes the same database creation and backup scripts to explain how the SQL Server full, differential and transaction log backup chain is mapped between each backup type. If you want to follow along, please read this first tip and setup your database and backups.
RESTORE HEADERONLY
When restoring a database, the initial database RESTORE sequence must begin from a FULL database backup. A database RESTORE sequence cannot begin with a differential file backup or transaction log backup. When restoring databases there are four important LSNs: FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN. These values can be retrieved from a SQL Server backup file using the RESTORE HEADERONLY command.
You can use RESTORE HEADERONLY to retrieve the backup header information for each backup file on disk as shown below.
USE [master] RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK'
I have collated the FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN for these database backup files (Database setup script) into an Excel spreadsheet as shown below. The full scripts to retrieve the LSNs from all backup files are found at the end of this tip.
"Column A" is the backup type performed at a point-in-time (i.e. F1 = first full backup, T3 = third transaction log backup, D2 = second differential backup).
Some attributes for the full database backup LSNs are:
- The very first full database backup will always have a DatabaseBackupLSN of zero
- The very first full database backup's FirstLSN will be the same as the CheckpointLSN
Some attributes of a differential database backup LSNs are:
- The DatabaseBackupLSN value for the differential backup identifies the full database backup that is required in order to apply the differential database backup
- The DatabaseBackupLSN value for the differential backup will match its base full database backup CheckpointLSN
- The CheckpointLSN maps to the CheckpointLSN of the first transaction log backup after the differential backup
Some attributes of a transaction log backup LSNs are:
- A LSN uniquely identifies every record in a transaction log backup
- The FirstLSN and CheckpointLSN of the first transaction log backup is also the first full database backup CheckpointLSN if the backup is taken when the database is idle and no replication is configured
- The transaction log LSN chain is not affected by a full or differential database backup
- LSN are sequential in nature. A higher LSN value indicates a later point in time
Understanding LSN Mapping Examples in SQL Server
Below are 3 sections that describe the LSN mapping for:
- Full database backup LSN to Transaction Log backup LSN
- Full database backup LSN to Differential database backup LSN
- Differential database backup LSN to Transaction Log backup LSN
Full database backup LSN to Transaction Log backup LSN
- FirstLSN identifies the first log record included in the backup
- LastLSN includes log records up to, but not including this LSN
- When planning which transaction log backup to use to roll forward, the LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup
- In the example above, Full database backup LastLSN 34000000025600001 falls in between transaction log backup T1 FirstLSN 34000000016000100 and LastLSN 34000000028800000. Applying T1 after F1 will succeed, applying T2 or T3 after F1 will result in an error.
- A transaction log backup's LastLSN is the FirstLSN in the subsequent transaction log backup chain. In the example above, transaction log backup T1 LastLSN 34000000028800000 is the FirstLSN of transaction log backup T2, transaction log backup T2 LastLSN 34000000030400000 is the FirstLSN of transaction log backup T3 and so on
Full database backup LSN to Differential database backup LSN
- A differential database backup can only be applied ONCE to a restored full database backup that has a CheckpointLSN value that is equal to the differential backup DatabaseBackupLSN
Differential database backup LSN to Transaction Log backup LSN
- A differential backup LastLSN + 1 will be in between the FirstLSN and LastLSN of its subsequent transaction log backup
- In the example above, Full database backup LastLSN 34000000035200001 falls in between transaction log backup T3 FirstLSN 34000000030400000 and LastLSN 34000000036000000. Applying T3 after D1 will succeed, applying T4 or another transaction log backup will result in an error.
Script to Retrieve Backup LSNs
This is the full script to retrieve the LSNs for all database backup files created from this tip.
USE [master] RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T3.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T4.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\D2.BAK' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T5.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\F2.BAK' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T6.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T7.TRN' RESTORE HEADERONLY FROM DISK = N'C:\Temp\D3.BAK' RESTORE HEADERONLY FROM DISK = N'C:\Temp\T8.TRN'
Next Steps
- Take a look at this tip that shows different ways to restore a SQL Server database -http://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/
- Learn more about Log Sequence Numbers and Restore Planning - http://technet.microsoft.com/en-us/library/ms190729(v=sql.105).aspx