Unanswered: Need to remove jounal file after : transfer/apply to secondary db
We are using log shipping and we would like to remove all transfered and applied journal (in the primary box).
We have the intentionto use a trigger like this :
CREATE TRIGGER del_log
FROM log_shipping_plan_history e INNER JOIN inserted i ON e.sequence_id = i.sequence_id
if IF (@lastfile <> NULL)
... remove file (using xp_cmd for example...)
but the problem is that we have only the last file transfered and applied that will be removed
(some time, more that 1 file are applied in one shot ...
see num_files column in log_shipping_plan_history).
Any solution to remove all the files generated before the last one given by the query ?
Any other solutions (sql wizard gives the possiblity to to remove file after a laps of time 1hour, 1day...).
I am looking for the table that contains all the journal files (that we can see when we try to retore a db) ?
If I understand the question correctly, try looking at the log_shipping_secondaries table in the msdb database. This is where the log shipping plans are kept, and this table maintains the last_copied and last_loaded filenames.