Error “System Assertion Check has failed” Msg 3624, Level 17, State 1 when trying to do a log restore with the STOPATMARK option in SQL 2008
Recently our operational team had to do a restore of a database for BizTalk. These databases require a special restore routine, to let the BizTalk components in sync with the DB.
First they restored the latest full backup, which went without problems.
Then they needed to restore the transaction log backups, with the STOPATMARK option set:
RESTORE LOG [Database] FROM
DISK = N’LocationOfBackupFile’
WITH FILE = 1, NOUNLOAD, STATS = 10,
STOPATMARK = N’BTS_2011_06_06_00_00_29_087′ AFTER N’2011-06-06T02:00:32′
GO
which results in an error:
100 percent processed.
Processed 0 pages for database ‘Database’, file ‘Database’ on file 1.
Processed 2 pages for database ‘Database’, file ‘Database_log’ on file 1.
Location: diskio.c:1769
Expression: filepos + cBytes <= (DWORDLONG) m_size << PAGESIZEBITS
SPID: 65
Process ID: 1908
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 3624, Level 17, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
When restoring the log without the STOPATMARK option, no problems are encountered.
The log mark entries where all filled in correctly. You can see this by querying the dbo.logmarkhistory table in msdb.
We opened a case at Microsoft and they confirmed that this is a bug and is fixed in CU4 for SQL 2008 SP2 (http://support.microsoft.com/kb/2527180). I’ve asked for more information, but they cannot provide it for the moment as it is confidential. I still hope to find some more information though, I’ll update this post with the details.
Tom
June 29th, 2011 at 20:21
Tom,
Did you ever get any resolution on this? I’ve been pulling my hair out with the same error message except that it’s being reported after a database restore. I’ve searched all over the place and it appears that the same error will result from a bunch of different things, all of which are likely a result of the same underlying problem.
I’ve you’ve received any decent support or information I would be VERY interested in finding out about it.
Thanks
September 15th, 2011 at 18:26
I am not sure if a solution was found for this.
I updated my SS2008R2 instance to SP1, but continued to maintain the same issue.
My solution was to Select the contents of the table causing the issue into a new table <>.
Rename the Old Table<>.
Rename <> to <>, moved the FK relationships from <> to newly created/renamed <>.
Placed the Primary Key on the newly created/renamed <>.
Ran my query again….TaDa!!!
all working now.
This may not be your solution, but it worked in my case.
September 15th, 2011 at 18:29
My previous post was missing values:::
I am not sure if a solution was found for this.
I updated my SS2008R2 instance to SP1, but continued to maintain the same issue.
My solution was to Select the contents of the table causing the issue into a new table (OldTable_00).
Rename the Old Table (OldTable_).
Rename newly created table to the old name (OldTable), moved the FK relationships from (OldTable_) to newly created/renamed (OldTable).
Placed the Primary Key on the newly created/renamed (OldTable).
Ran my query again….TaDa!!!
all working now.
This may not be your solution, but it worked in my case.