SQL Server Performance

Old transaction log files on standby server

Discussion in 'SQL Server Log Shipping' started by ASULEJ, Jan 12, 2004.

  1. ASULEJ New Member

    Does anybody know how to remove automatically old transaction logs from standby server?

    They are removed from primary server (Option : Remove files older than .. 2 days), but they stay on standby server, even when they are successful restored.

    Now I have to remove them manually.

    PS. I use option “File retention period – 2 days) but it doesn#%92t work.
  2. satya Moderator

    Ensure no other application(virus scanners) or process are accessing those files while SQL is trying to perform LS process.

    Ensure SQL Service account has necessary admin privilege on that folder to remove the files as per the Log shipping.

    Take a look at SQL server error log for any information or check the maintenance plan log file for more details.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ASULEJ New Member

    This is a dedicated server; there is no other applications on it, which can block the access to these files.
    User has sufficient rights to the folder where transaction log files are stored.
    I vied logs and everything looked fine. Backup, copy and restore operations were completed successfully.

    I don#%92t know what is wrong.
  4. satya Moderator

    Check and paste maintenance plan log file for details, it will be stored under LOG directory of MSSQL installatation or any other directory if you choose so.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. ASULEJ New Member

    Below is one of the log files (Primary):


    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'POMOST' as 'POMOSTlogshipp' (trusted)
    Starting maintenance plan 'EURO Log Shipping' on 2004-01-03 13:00:02
    [1] Database EURO: Transaction Log Backup...
    Destination: [D:logshipEURO_tlog_200401031300.TRN]

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

    [2] Database EURO: Delete Old Backup Files... <- it deletes old files from primary server
    1 file(s) deleted.

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

    End of maintenance plan 'EURO Log Shipping' on 2004-01-03 13:00:02
    SQLMAINT.EXE Process Exit Code: 0 (Success)

    On standby server, there is no maintenance plan log files
  6. satya Moderator

    How about job history which restores this LS job on the standby server?
    And ensure the files stored on standby server are not marked as read-only.

    Just for testing add the following script to execute the net commands and know the status:

    declare @cmdFile varchar(50)
    set @cmdFile= 'net file >C:
    etfile' + convert(varchar(10),getdate(),12) + '.log'
    declare @cmdSess varchar(50)
    set @cmdSess= 'net session >C:
    etsess' + convert(varchar(10),getdate(),12) + '.log'
    exec xp_cmdshell @cmdfile
    exec xp_cmdshell @cmdSess

    Review the netfile.log and netsess.log file to see if there is a process on another machine which has a lock on the file which could not be deleted.

    If not I can provide a seperate script to delete those files, as nothing works above.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. ASULEJ New Member

    The script returned:

    session
    Computer User name Client Type Opens Idle time


    -------------------------------------------------------------------------------
    \POMOST Windows 2000 2195 0 00:00:08
    The command completed successfully.

    file

    There are no entries in the list.

    If you can send me "seperate script", I will be grateful.
  8. satya Moderator

    Will do, BTW are you using any third party application for the backups alone?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. satya Moderator

Share This Page