Ask the Community
Groups
Pop-up to rebuild Audit table to improve performance - Connect IT Community | Kaseya
<main> <article class="userContent"> <p><strong>PROBLEM:</strong></p> <p>When I log into my server, I get a pop-up that I need to rebuild my Audit tables to improve performance? How do I go about this?</p> <p><strong>SOLUTION:</strong></p> <p>The solution to this is to run a reapply schema. A reapply schema rebuild a variety of the indexes in the database and also adds indexes if they are missing. That pop-up can appear if there are indexes missing on the audit table primarily because the Audit tables tend to be one of the largest tables in the database.</p> <p>Simply go to the System tab > Configure page > and click on 'Reapply Schema'</p> <p>You can also rebuild these indexes through SQL Management studio by running the query below.</p> <p> </p> <p><em>USE [ksubscribers]</em><br><em>GO</em></p> <p><em>DROP INDEX [auditRsltApps_applicationName_idx] ON [dbo].[auditRsltApps]</em><br><em>DROP INDEX [auditRsltApps_agentGuid_idx] ON [dbo].[auditRsltApps]</em><br><em>DROP INDEX [IX_auditLastAudit] ON [dbo].[auditRsltApps]</em><br><em>DROP INDEX [IX_baseAuditAgent] ON [dbo].[auditRsltApps]</em><br><em>DROP INDEX [IX_lastAuditAgent] ON [dbo].[auditRsltApps]</em><br><em>go</em></p> <p><em>CREATE NONCLUSTERED INDEX [auditRsltApps_agentGuid_idx] ON [dbo].[auditRsltApps]</em><br><em>(</em><br><em> [agentGuid] ASC</em><br><em>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]</em><br><em>CREATE NONCLUSTERED INDEX [auditRsltApps_applicationName_idx] ON [dbo].[auditRsltApps]</em><br><em>(</em><br><em> [applicationName] ASC</em><br><em>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]</em><br><em>CREATE NONCLUSTERED INDEX [IX_auditLastAudit] ON [dbo].[auditRsltApps]</em><br><em>(</em><br><em> [lastAudit] ASC,</em><br><em> [agentGuid] ASC</em><br><em>)</em><br><em>INCLUDE ( [flags]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]</em><br><em>CREATE NONCLUSTERED INDEX [IX_baseAuditAgent] ON [dbo].[auditRsltApps]</em><br><em>(</em><br><em> [baseAudit] ASC,</em><br><em> [agentGuid] ASC</em><br><em>)</em><br><em>INCLUDE ( [applicationName],</em><br><em> [flags]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]</em><br><em>CREATE NONCLUSTERED INDEX [IX_lastAuditAgent] ON [dbo].[auditRsltApps]</em><br><em>(</em><br><em> [lastAudit] ASC,</em><br><em> [agentGuid] ASC</em><br><em>)</em><br><em>INCLUDE ( [applicationName],</em><br><em> [flags]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]</em></p> <p><em>GO</em></p> </article> </main>