Sometimes when using the Audit Trail Viewer for searching it is extremely slow, or you will get a time out error reported. If you are consistently getting this then you may want to consider adding indexes to the main zd_audit table that stores the historical changes in Audit Trail Classic . There are two methods for doing this. Either adding it manually in SQL Server Management Studio or using a SQL script inside the same application.


Creating a Manual Index for ZD_Audit table in Raiser’s Edge. 

From the object explorer in SQL Server Management Studio locate and expand the zd_audit table.



 

Expand the ZD_Audit table properties and right-click on indexes and select new index >> non-clustered index.

 


 

The following screen should be displayed.




Give your new index a name that relates to the field you are creating the index on and select ‘add’ to select a field from the ZD_Audit table.

 

Select the field you would like to index and click ok.

It is recommended that you create indexes on the most frequent search criteria




That completes the creation of an index



 

Creating automated Indexes for ZD_Audit table in Raiser’s Edge. 


The following are scripts that can be run directly in SQL to create three non-clustered indexes on the following fields:


[DateChanged] - the date the change was made in Audit Trail

[UserChangedId] - who the change was made by in Audit Trail

[ConSysId] - who the change was made to in Audit Trail

 

Copy and paste the below into SQL Server Management Studio

 

CREATE NONCLUSTERED INDEX [ZD_DateChanged] ON [dbo].[zd_Audit] 

( 

       [DateChanged] ASC 

)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] 

GO 

  

CREATE NONCLUSTERED INDEX [ZD_UserChangedID] ON [dbo].[zd_Audit] 

( 

       [UserChangedId] ASC 

)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] 

GO 

  

CREATE NONCLUSTERED INDEX [ZD_ConSysId] ON [dbo].[zd_Audit] 

( 

       [ConSysId] ASC 

)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] 

GO