Post reply

Maint plan won''t delete .trn logs as scheduled

  • March 4, 2005 at 12:45 pm

    #185389

    I have two Sql2000 servers that won't delete .trn files as scheduled in the maint plan.  Any advice on how to get the maint plan to cooperate would be appreciated.

  • March 7, 2005 at 4:24 am

    #545677

    Can you please explore more on your backup strategy ?

    I mean what is the duration of the T-Log backups and when is it deleted ?

    Also are your T-Log backups working fine.

    Provide with the info. to help you better.

    --Kishore

  • March 7, 2005 at 7:54 am

    #545709

    I have created a maintenance plan using the wizard.  I have one plan that applys to all databases.

    Full backups occur nightly and those files are kept for four weeks (these are small DB's).

    Transaction logs are backed up every three hours and files are kept for three days.  This is where the problem occurs, it will not remove the logs after three days.  I have tried setting this parameter to other time periods from one to two weeks but the system behavior is the same.

    All of the backups seem to be created right on schedule for both full and tran logs.  I have restored data from these backups into our testing area with no difficulty.

    Everything appears to run well except that the system will not cleanup the old files.

     

  • March 7, 2005 at 10:58 pm

    #545815

    Try out the following steps.

    You must be having jobs created for the Full and T-log backups in SQL EM.

    Right click on T-Log backup job and "All Tasks" and Generate Script.

    Save the .sql file.

    Open the .sql file in Query Analyser. One of the scripts will have the following steps: (I have put sample data)

    -- Add the job steps

      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID B1398FE0-B762-47B1-A9DD-4370CA8DE42E -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog  -UseDefDir  -DelBkUps 10MINUTES -BkExt "TRN"''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

      EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

    See the highlighted portion in RED.                                                         For example, in this script my T-log backups will get deleted in 10 minutes.

    Check in your .sql file.

    Let me know what it shows at your end in your .sql file.

    Also Create script for your Full Database backup as well.

    Let me know of your progress.

    --Kishore

  • March 9, 2005 at 2:54 pm

    #546244

    I created and viewed the .sql file.  The instruction to delete the logs is in there.  What I have discovered though was that the job has been reported as having failed.  This would account for the fact that the files were not deleted.

    What is puzzling though is that the SQL log itself indicates the log files are being backed up (I have verified that they really are) and has not indicated any failures.

    The event viewer on the machine does indicate the failure.

    So at this point I will look into what is causing the job to fail, I suspect when that is fixed my .trn log files will be deleted properly.

    Thanks for your reply to the posting, you put me on the correct track to finding the problem.

     

  • March 10, 2005 at 12:17 am

    #546293

    Yes. If the job script file contains the -DelBkUps parameter, then the .trn files should be deleted at the scheduled interval.

    However, if the job itself is failing, then the delete wont occur.

    Let me know, if you need more help.

    --Kishore

  • March 10, 2005 at 11:53 am

    #546452

    Have you tried the following?

    In Enterprise Manager, select "Management" -> "SQLServerAgent" -> "Jobs", then right-click on the job to get the pop-up menu and select "View Job History". When the job history dialog appears, select the "Show Step Details" check box. This should show more info about what's going wrong.

    Steve G.

  • March 10, 2005 at 10:23 pm

    #546539

    I wonder if you are backing up the system databases or any other databases with the recovery model set to simple.  In that case the job would fail when it tried to do the transaction log back up on those databases.  I just created a maintenance plan to backup the transaction logs of a user database and master.  It was set to remove the log after 1 minute.  The logs were never removed.  I then remove master from the plan, ran it again and all of the old logs were deleted.

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • March 11, 2005 at 11:44 am

    #546696

    The recovery model for all DB's except temp is either full or bulk-logged.  I created a new maint plan for system DB's and modified the old one to only backup user DB's, all of the logs were cleaned up correctly with the exception of the MSDB log.  I think I will watch the system over a few days to be sure that this new behavior is consistent.  At least it seems to be heading in the right direction!

  • March 11, 2005 at 1:58 pm

    #546734

    I've run into something similar on a regular basis with my servers. Mine occurs on the full .bak as well as the .trn. I'm assuming SQL2K on Win2K(3).

    If you have the the "Run integrity checks before backups" checked on in the maint plans, and the integrity checks fails -- usually because someone is in the database and it can't take it to single user mode -- it won't delete the backup files. You probably don't have this problem when you run the full backups, because that is at midnight. But when you are doing tran logs during the day it won't work and you see the tran logs build up.

    The catch is that you have to completely rebuild all the the maint plans from the ground up. For some reason, if you just go in and uncheck the box it will still run the integrity checks before backups.

    I built the job below to blow out all backups (trn and bak) between 2 and 7 days that are in the msdb log. I run it once a day and on error out of disk space. You should be able to modify it easily enough to just go after tran logs.

    declare @file_name varchar(100)

declare FileList cursor for
    select  physical_device_name
    from msdb.dbo.BackupMediaFamily bmf 
    left join msdb.dbo.BackupSet bs on 
       bs.media_set_id = bmf.media_set_id
    WHERE datediff(hh,bs.backup_start_date,getdate()) > 48 
    and datediff(hh,bs.backup_start_date,getdate()) < 168 
    and physical_device_name not like '%BEXVDI%'
    ORDER BY bs.backup_start_date desc

OPEN FileList
FETCH NEXT FROM FileList INTO @file_name
 WHILE (@@fetch_status  -1) 
  BEGIN
    --print @file_name
    SELECT @file_name = 'DEL ' + @file_name
    EXEC XP_CMDSHELL @file_name
    FETCH NEXT FROM FileList INTO @file_name
  END
DEALLOCATE FileList

    Jus my $0.02.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply