cancel
Showing results for 
Search instead for 
Did you mean: 
stevenstadel
Level 13

Corruption in APM_ApplicationTemplate table.

Jump to solution

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.

0 Kudos
12 Replies
silverbacksays
Level 16

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

Hi stevenstadel

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.

=B']
stevenstadel
Level 13

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

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.

silverbacksays
Level 16

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

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.

=B']
stevenstadel
Level 13

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

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.

stevenstadel
Level 13

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

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.

  • Shutdown all Orion Servers. Primary, APEs, and AWEs.
  • Created a backup APM_ApplicationTemplate table.
  1. SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[APM_ApplicationTemplate_bak](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](125) NOT NULL,
    [IsMockTemplate] [bit] NOT NULL,
    [Description] [nvarchar](max) NULL,
    [Created] [datetime] NULL,
    [LastModified] [datetime] NULL,
    [ViewID] [int] NULL,
    [ViewXml] [nvarchar](max) NULL,
    [UniqueId] [uniqueidentifier] NULL,
    [Version] [nvarchar](50) NULL,
    [CustomApplicationType] [nvarchar](50) NULL,
    CONSTRAINT [PK_APM_ApplicationTemplate_bak] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[APM_ApplicationTemplate_bak] ADD  DEFAULT ((0)) FOR [IsMockTemplate]
    GO
  • Copied all data from the APM_ApplicationTemplate table to the newly created backup table.
  1. INSERT INTO dbo.APM_ApplicationTemplate_bak
    (
        ID
       ,Name
       ,IsMockTemplate
       ,Description
       ,Created
       ,LastModified
       ,ViewID
       ,ViewXml
       ,UniqueId
       ,Version
       ,CustomApplicationType
    )
    SELECT ID
          ,Name
          ,IsMockTemplate
          ,Description
          ,Created
          ,LastModified
          ,ViewID
          ,ViewXml
          ,UniqueId
          ,Version
          ,CustomApplicationType
    FROM dbo.APM_ApplicationTemplate
    SET IDENTITY_INSERT dbo.APM_ApplicationTemplate_bak OFF
  • Delete all data on the APM_ApplicationTemplate Table.
  1. DELETE FROM dbo.APM_ApplicationTemplate
  • Revert Source Schema on APM_ApplicationTemplate table to schema pre-upgrade (2019.2)
  1. ALTER TABLE [dbo].[APM_ApplicationTemplate] ALTER COLUMN [Description] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ALTER TABLE [dbo].[APM_ApplicationTemplate] ALTER COLUMN [ViewXml] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  • Copy Data from Backup table into APM_ApplicationTemplate table.
  1. SET IDENTITY_INSERT dbo.APM_ApplicationTemplate ON
    INSERT INTO dbo.APM_ApplicationTemplate
    (
        ID
       ,Name
       ,IsMockTemplate
       ,Description
       ,Created
       ,LastModified
       ,ViewID
       ,ViewXml
       ,UniqueId
       ,Version
       ,CustomApplicationType
    )
    SELECT ID
          ,Name
          ,IsMockTemplate
          ,Description
          ,Created
          ,LastModified
          ,ViewID
          ,ViewXml
          ,UniqueId
          ,Version
          ,CustomApplicationType
    FROM dbo.APM_ApplicationTemplate_bak
    SET IDENTITY_INSERT dbo.APM_ApplicationTemplate OFF
  • Update APM_ApplicationTemplate table schema (pre-2019.2) to post-upgrade schema (2019.2)
  1. ALTER TABLE [dbo].[APM_ApplicationTemplate] ALTER COLUMN [Description] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ALTER TABLE [dbo].[APM_ApplicationTemplate] ALTER COLUMN [ViewXml] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  • Verified with DBCC CHECKTABLE.
  1. DBCC CHECKTable ('dbo.apm_applicationTemplate')
serena Product Manager
Product Manager

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

Hi Steve,

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.

https://support.microsoft.com/en-ca/help/3213240/fix-changing-the-data-type-and-then-updating-the-ta...

pastedImage_1.png

0 Kudos
Highlighted
stevenstadel
Level 13

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

Hello Serena,

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.

serena Product Manager
Product Manager

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

stevenstadel  wrote:

Hello Serena,

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.

One other suggestion from the team is that if you still have this corruption present on your test environment run the following:

exec sp_tableoption 'dbo.APM_ExternalSetting', 'large value types out of row', '1'

exec sp_tableoption 'dbo.APM_ApplicationTemplate', 'large value types out of row', '1'

exec sp_tableoption 'dbo.APM_ComponentDefinition', 'large value types out of row', '1'

0 Kudos
stevenstadel
Level 13

Re: Corruption in APM_ApplicationTemplate table.

Jump to solution

Thank you for the update! We have blown away that environment, unfortunately.

One suggestion I have is to add in a DBCC CHECKTable against the whole database as part of an upgrade process to identify potential issues before a sign off that the upgrade was successful. We will be adding this to our upgrades moving forward. Not sure of the ramifications of adding it to the wizard (It can take some time depending on the DB size) but thought I'd make the suggestion.