July 12, 2017 at 1:33 pm
Location: pageref.cpp:955 Expression: IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType !=PageModifyType_Contents || GetPagePtr ()->IsTextPage () SPID: 77 Process ID: 1752
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 set
This is a datawarehouse database. The table is an SCD Type 2 Dimension table.
I run into this issue every few days. When I delete the non clustered index and recreate it, the problem goes away for a couple of days.
I've read putting the database into Full Recovery mode helps, but I can't afford to have the log grow seeing it's a datawarehouse db.
Anyone have a solution to this issue?
Thank you,
MG
July 12, 2017 at 1:53 pm
2 things come to mind:
1 - did you run DBCC CHECKDB to ensure the database is not corrupt?
2 - did you check the SQL Server error log for details?
If you did number 2, can you post the result?
I can't think why Full Recovery mode would fix that personally.
Do you have any job or something that is being run at the time this error comes up? I am just wondering if there is some pattern to the error occurring such as every time you do a TLOG backup the error comes up. Mind you, in simple recovery mode, you shouldn't be doing tlog backups.
Since removing and re-creating the index fixes the problem, I am expecting database corruption to be what is causing the issues. But I could be wrong.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 12, 2017 at 3:36 pm
Thanks for the reply bmg002.
I did do a dbcc checkdb and it didn't find any corruption.
DBCC CHECKDB (database) executed by domain\username found 0 errors and repaired 0 errors. Elapsed time: 0 hours 7 minutes 3 seconds. Internal database snapshot has split point LSN = 00000db4: 0000ce7d:0001 and first LSN = 00000db4:0000ce7b:0001.
The logs show the following errors and the errors I posted above.
Error 3624, Severity 20 state 1
Error 17066, Severity 16 state 1
This is caused every time I have an etl process run and it executes a merge stored procedure into the dimension table.
I didn't put it into Full Recovery. The only thing that occurs is when I do the merge statement execution. I have it in simple recovery mode and there aren't any other jobs running when this situation occurs.
I kept the index on and ran the dbcc checkdb command and even with that index, it doesn't show corruption.
Once I drop the index, I can run the sp successfully.
Thanks again for your help.
MG
July 12, 2017 at 3:52 pm
May actually have a solution for you:
https://support.microsoft.com/en-us/help/3053960/fix-a-system-assertion-in-longrec.inl-1318-occurs-when-you-rebuild-or
I see you posted this in the SQL 2016 section and the above article applies to SQL 2012. I'm guessing that this might be fixed in a future or current CU? Everything I'm reading online says "update to the latest CU and it should fix the problem".
What CU are you on? Might not hurt to check if it is fixed in a newer CU or not.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 1, 2020 at 6:44 am
Hi I know this is an old post but I had the same problem with SQL Server 2016 SP2 CU12
So as initially mentioned by MG putting the database/s on Recovery mode to FULL helps, which in my case it was the only work around to resolve it. Make sure that you followup the FULL recovery mode with a Full DB Backup before you attempt to re-run your SSIS package.
Also increase the Transaction Log backup frequency, I've adjusted mine to every 15 minutes while the SSIS packages are running and keep an eye on your disk space to see if you need to increase it and/or increase the Transaction log backup frequency.
So by simply changing your DB to FULL Recovery, we can ascertain that is a bug.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply