Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    47

    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
    ON log_shipping_plan_history
    AFTER INSERT
    as
    declare
    @lastfile nvarchar(256)
    SELECT @lastfile=i.last_file
    FROM log_shipping_plan_history e INNER JOIN inserted i ON e.sequence_id = i.sequence_id
    where i.activity=1
    begin
    if IF (@lastfile <> NULL)
    ...
    ... remove file (using xp_cmd for example...)
    ...
    end

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

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •