We have a growing Octopus database and I have look into it. What is see, is that the tables ‘event’ and ‘[EventRelatedDocument]’ are big (to our standards).
It uses ca 5 Gb from our database which is ca 9 GB.
We do not use auditing, so we do not care if events are deleted.
So i thought of running the next script:
SELECT TOP 1000 [Id]
FROM [Octopus].[dbo].[Event]
where [Occurred] > ‘2022-01-01 00:00:00.0000000 +00:00’
and [Occurred] < ‘2022-01-01 03:18:12.6948884 +00:00’
order by [Id]
– returns ‘Events-516742’
delete from [Octopus].[dbo].[Event] where [Occurred] < ‘2022-01-01 15:10:51.1965460 +00:00’
delete FROM [Octopus].[dbo].[EventRelatedDocument] where [EventId] < ‘Events-516742’
Can you confirm if this script looks okay?
If not how can i shrink these 2 tables in another way?
I would sincerely advise against the manual removal of any data within the database, as this can have unwanted knock-on effects with missing or orphaned data.
However, in the next few weeks, with the release of Octopus 2023.3, a new feature will ship with Octopus that handles the retention/clean-up of event data within the database - named: Audit-log retention.
In this scenario I would recommend, if possible, waiting until 2023.3 goes live and allowing the audit-log retention feature to clean up these tables - which should provide with more breathing room in terms of data storage space.
Please let me know what you think of the above or if you have any questions/concerns.
Hi Eric,
we have a maintenance stored procedure in place which deletes those records older than a specific number of days. And an additional index maintenance step after the deletions took place.
This stored procedure is executed by an active SQL job running nightly.
I can confirm this works for us.