Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Unanswered: Delete Transaction Logs

    SQL Sever 2000 SP4

    I have a maintenance plan that backs up my transaction logs every 30 minutes durring the day. That same plan is set to delete transaction logs older then one day. It is making the logs like it should. It is not however deleting the old ones. The log does not show it deleting them or any errors. It does however say that it failed. I have coppied a log below.

    Thank you,
    Scott


    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SERVER2' as 'INWOOD\Administrator' (trusted)
    Starting maintenance plan 'Local Tran' on 11/5/2008 3:30:00 PM
    [1] Database IACPS-IW: Transaction Log Backup...
    Destination: [D:\SQL BACKUP\IACPS-IW\IACPS-IW_tlog_200811051530.TRN]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [2] Database IACPS-IW: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [3] Database M2MDATA01: Transaction Log Backup...
    Destination: [D:\SQL BACKUP\M2MDATA01\M2MDATA01_tlog_200811051530.TRN]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [4] Database M2MDATA01: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [5] Database M2MSystem: Transaction Log Backup...
    Destination: [D:\SQL BACKUP\M2MSystem\M2MSystem_tlog_200811051530.TRN]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [6] Database M2MSystem: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [7] Database model: Transaction Log Backup...
    Destination: [D:\SQL BACKUP\model\model_tlog_200811051530.TRN]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [8] Database model: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    Backup can not be performed on database 'msdb'. This sub task is ignored.

    [9] Database pubs: Transaction Log Backup...
    Destination: [D:\SQL BACKUP\pubs\pubs_tlog_200811051530.TRN]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [10] Database pubs: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    Deleting old text reports... 0 file(s) deleted.

    End of maintenance plan 'Local Tran' on 11/5/2008 3:30:01 PM
    SQLMAINT.EXE Process Exit Code: 1 (Failed)

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Does the Domain account that the SQL Agent runs under have adequate permissions to drive where the logs are held?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2008
    Posts
    8
    I think so.

    I have another maintenance plan that backs up the entire database to the same location and deletes old ones after a day. It works fine.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what happens if you symply back up the transaction log manually from the SQL Server Management Studio. It should give you a more helpful error. I trust you adequate space on the destination drive?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2008
    Posts
    8
    How do I back up the transaction log manually? I only know how to do a complete back up manually. Regardless, the transaction log is backed up without a problem. Deleting the old transactions is the issue.

    I have plenty of free space on the drive.

Posting Permissions

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