Every SQL Server database has a transaction log which retains a history of each transaction that occurs in SQL Server that modifies data. Using this log, and any log backups available, RockSolid can allow users to search for specific records based on a provided criteria. This may allow database administrators to identify the source of changes if other audit methods are unavailable.
How To Audit the SQL Server Transaction Log
- Create a new RockSolid service request that will be used to execute the log search commands. This should be of task type "Manual DBA Request". Give the service request an appropriate title and description.
- Ensure the instance is the relevant instance that contains the database to be audited.
- Set the Service Request status to "IN PROGRESS"
- Choose the Manual Resolution tab
- On the "Dynamic Commands" tab choose "Audit Database Transaction Log" as the Task to Execute.
- Under the Audit Database Transaction Log header, choose the relevant: Database, Login (optional), Operation (Delete, Insert or Update), Start Date (optional), End Date (optional)
- Hit the "Preview Command" button
- Hit the "Execute Command" button to execute the log audit
Depending on the parameters selected the database transaction log audit may take several minutes or more to run. Results are stored in RockSolid for future use.