After our last upgrade we are seeing corruption on the SQL table APM_ApplicationTemplate.
DBCC CHECKTABLE (Orion.dbo.APM_ApplicationTemplate) found 173 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 14 seconds. Internal database snapshot has split point LSN = 02832433:0002c030:000e and first LSN = 02832429:0000cc87:0002.
We can't run a repair without putting the database into single user mode which means taking an outage.
Since this issue appeared after our latest upgrade what is the consequence for restoring the table from before the upgrade? We can restore the table from before the last upgrade which will give us all the templates that we had before except for the ones that were added with the last SAM version. This in itself is not a problem as we can backup all of the new templates and re-import and re-apply them. The one main issue I see is the AppInsight for Active Directory that was introduced in the last upgrade.
We are not able to backup the AppInsight for Active Directory though the GUI. What is the best way to restore the table and then get this feature added back in?
Your thoughts are appreciated.
Solved! Go to Solution.
I would recommend opening a ticket with support on this, they may be able to assist you with the least destructive way of sorting out this rogue table.
Whilst your suggestion is logical, it is possible that they key fields within the table are referenced elsewhere, and replacing the table with the old, pre-upgrade version, may break other things than the obvious of loosing new templates.
Thanks for the thoughts silverbacksays
We are absolutely working with support but we are not getting very far yet. Their suggestion is to try a repair, which failed, then a reload the table from before the issue started, which was before the upgrade. I've emailed back with my concerns but wanted to try thwack to see if anyone else has feedback.
Thanks for expanding on that Steven.
Keep this thread updated, if you don't mind, as I'll be interested to see the outcome. Looks like you're in a bit of a bind there, so I hope you can get it resolved without having to resort to a repair.
Still trying to work with support on this issue. The table corruption happened because of our last Orion upgrade. The upgrade changed the Description and ViewXml datatype fields changed from ntext to nvarchar in the APM_ApplicationTemplate table.
Despite the integrity error the suite is working just fine. We can add, remove, and assign templates without issue.
We have fixed the corruption with a test environment, but it removes all of the records in the APM_ApplicationTemplate table, including the out-of-the box and custom templates. This loss means all associations between actively assigned application monitors and their respective templates are lost.
More concerning is that Support has also confirmed that all SAM historical data and assigned application monitors may be removed and will need to be reassigned. This is a non-starter for us as we can not lose this data.
I am still pushing for a more custom support solution. The ideal solution would be to work with development to make sure our data is not lost after doing the repair, or working through those 173 application template records and find out what we can do to make the Description and ViewXml datatype fields that have changed to be valid as nvarchar datatypes.
We were able to solve this problem. Support's proposed solution did not work for us.
One of our excellent DBAs was able to come up with a fix. When we restored from backup and ran the upgrade the table was corrupted. Running a DBCC CHECKTable repair on the table removed all the templates from the table, and all those assigned application monitors in the database. We were between a rock and a hard place. This is an overview of what we did to fix the table.
These are the steps that we followed in case anyone else has the same issues.
PLEASE tell your DBA that this solution saved our beans and we are so humbly appreciative! Friday Microsoft spent a considerable amount of time on this issue to no avail. We applied the SW posted solution, to no avail. It was the query your DBA constructed that finally allowed us to resolve this issue. Real life saver here! Many Thanks!
Just following up here - our team looked into reported corruption cases and the underlying cause was identified as a MS SQL issue. The linked workaround suggests that this is addressed by applying the latest cumulative updates to your SQL server.
Our DBAs did see this KB as well. It didn't apply in our case because we were already beyond the CU level mentioned and the APM_ApplicationTemplate Table only had 850 records in it, less than the > 4000 mentioned in the article.
For testing purposes, we did set up an environment with absolute latest SQL 2016 SP2 CU 10, loaded the Orion environment from before the upgrade, and still had the corruption.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.