SQL Server Performance

Retention Policy

Discussion in 'SQL Server 2005 General DBA Questions' started by california6, Apr 20, 2007.

  1. california6 New Member

    I have setup a retention policy of 4 days for my backup's. Unforuntately the backup devide which is holding my backup's doesnt get over-written after 4 days. Is this a bug?

    I am running SQL Server 2005 (service pack-1) on Windows 2003.

    Any help would be greatly appreciated.

    Thanks,
    cali
  2. MohammedU New Member

    There are some issues with backup retentions...apply sp2 and check it out.



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. satya Moderator

    If you are using maintenance plans follow as suggested, if you are using any other script to do so check with profiler.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. california6 New Member

    Thanks for the update:

    I am using a script to do the backup. Here's how my script looks like:

    BACKUP LOG PPROD TO DISK = N'F:BackupTLogPProddb_Tlogbkup.trn' WITH NOFORMAT, NOINIT, NAME = N'SWIFT_PROD_DB-Txn Log Backup', SKIP, NOREWIND, RETAINDAYS = 4, NOUNLOAD, STATS = 10

    When i do a restore headeronly on the backup device, I could see ExpirationDate column populating the expiry date properly. some how it doesnt expire the backup set's.

    Any input?

    Thanks again,
    Cali



  5. MohammedU New Member

    I think SQL server will not overwrite the files when you use this property, it will allow you delete the files after RETAINDAYS but it can be deleted using other methods...

    Try using NOSKIP option

    From BOL:RETAINDAYS = { days | @days_var }
    Specifies the number of days that must elapse before this backup media set can be overwritten.

    FROM BOL:
    { NOSKIP | SKIP }
    Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.

    Note:
    For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.



    NOSKIP
    Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. This is the default behavior.

    SKIP
    Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

    To view the expiration dates of backup sets, query the expiration_date column of the backupset history table.



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. california6 New Member

    Thank you so much Mohammed. I shall try the advice configuration and see, if this works or not.

    Will keep you posted.

    Thanks again,
    Cali
  7. satya Moderator

    If I were you then I shall use another script to delete the backup file when the specified time is expired, will not depend upon the settings as they are dependant with Windows services and not that reliable.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. MohammedU New Member

    I agree with Satya to use different script to delete the files but poster is not creating individual log files with INIT option to use it, he is appending the backup files to the same file which is not preferable in my openion.

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. california6 New Member

    Thanks for your email.

    This is what i want:

    I am doing my production database txn log backup's every 15 minutes. The txn files are dump to one single file and i do not want to keep any txn logs more than 4 days. Thats why i intended to use the above script with retaindays=4:

    EG of the script: I have modified this script to include "NOSKIP" as per Mohammed's recommendation. I dont know, if this will work or not: But here's how the script looks like:

    BACKUP LOG PPROD TO DISK = N'F:BackupTLogPProddb_Tlogbkup.trn' WITH NOFORMAT, NOINIT, NAME = N'SWIFT_PROD_DB-Txn Log Backup', NOSKIP, NOREWIND, RETAINDAYS = 4, NOUNLOAD, STATS = 10


    Recommendation: If you have any workaround to my issue, would be very nice if you please advice. Again i want to have one central txn backup file with 4 day's of txn log's backup in it.

    Appreciated if you advice as to how to proceed.

    thanks again for all your help.
    Cali
  10. MohammedU New Member

    Here is the script...<br /><br />create Procedure Sp_BackUpTransactionLog @Sys_Database Sysname, @BackupPath Sysname ,@FileDelDays int = 4<br />As<br />Begin<br />-- Sp_BackUpTransactionLog @Sys_Database = 'pubs', @BackupPath = '\ServerNamec$ackup'<br /><br />SET NOCOUNT ON <br />Declare@Vch_FileNameVarchar(255),<br /> @Dt_DateAndTimeVarchar(20),<br /> @Vch_SqlStringVarchar(255)<br /><br />if right(@BackupPath,1) &lt;&gt; ''<br />select @BackupPath = @BackupPath+''<br /><br />select @Dt_DateAndTime = convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')<br />-- convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate(), 112)+'_'+ Replace(convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate(), 14),':','_')<br /><br />SELECT @Vch_FileName=@BackupPath+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_' + @Dt_DateAndTime +'.TRN'<br />-- SELECT @Vch_FileName<br />SELECT @Vch_SqlString='BACKUP LOG '+ @Sys_Database + ' TO DISK = '''+ @Vch_FileName +''''<br />SELECT @Vch_SqlString=@Vch_SqlString + ' WITH INIT , STATS = 10 '<br />--SELECT @Vch_SqlString<br />EXECUTE(@Vch_SqlString)<br />declare @cmd Varchar(1000)<br />select @cmd = 'del '+@BackupPath+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_'+convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate()-@FileDelDays, 112)+'*.trn'<br /><br />EXEC master.dbo.xp_cmdshell @cmd, no_output<br /><br />select @cmd = 'del '+@BackupPath+'Completed'+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_'+convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate()-@FileDelDays, 112)+'*.trn'<br /><br />EXEC master.dbo.xp_cmdshell @cmd, no_output<br />end<br /><br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  11. MohammedU New Member

    By the way you can also use Maintenance plan too...for which you don't need to write any code...


    declare @dt datetime
    select @dt=getdate()-4 --No days to delete
    EXECUTE master.dbo.xp_delete_file 0,N'c:Backup,N'BAK',@dt

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. california6 New Member

    Thank you MohammedU.

    Unfortunately i am trying to execute the following code and it say's, "Incorrect syntax".

    Here's how my code looks like:

    declare @dt datetime
    select @dt=getdate()-1 --No days to delete
    EXECUTE master.dbo.xp_delete_file 0,N''c:'',N'dmp',@dt


    Appreciated if you could please suggest as to what's wrong here.

    Thanks again,
    Cali
  13. california6 New Member

    Sorry here's the right code:


    declare @dt datetime
    select @dt=getdate()-1 --No days to delete
    EXECUTE master.dbo.xp_delete_file 0,N''c:'', N'dmp',@dt


    The error message is:

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ''.


    Thanks,
  14. MohammedU New Member

    Remove additional single quotes...

    declare @dt datetime
    select @dt=getdate()-1 --No days to delete
    EXECUTE master.dbo.xp_delete_file 0,N'c:', N'dmp',@dt


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  15. california6 New Member

    MohammedU,

    Thank you so much for the answer. But when executing the new SQL with new parameter's, here's what i am getting:


    Msg 22049, Level 15, State 0, Line 0
    Error executing extended stored procedure: Invalid Parameter

    I dont know, whats wrong this time. Really appreciated if you could please advice.

    Many thanks,
    Cali
  16. MohammedU New Member

  17. california6 New Member

    Thanks for your email.

    Here's my SQL Build version:

    Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)


    Please advice.

    Many thanks,
    Cali
  18. MohammedU New Member

    Looks like you have applied SP2 and I don't think there is any issue with BUILD...

    Try again and see what you get, as I mentioned it is working for me.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  19. california6 New Member

    Thanks for your email.<br /><br />It's working now. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> All what i did is closed my existing query window, stopped SQL Server and started it again. Re-ran it and boom it worked. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> dont know, what was going on..<br /><br />But i have SQL Server with SP1. (Build 3790: Service Pack 1) I am planning to apply SP2. Will that cause any problem?<br /><br />Thanks again for your help and really really apprecaited MohammedU,<br /><br /><br />Thanks,<br />cali
  20. MohammedU New Member

    "Build 3790: Service Pack 1" is not sql build, it is windows build. SP2 also released for window if you want you can apply but it is better to test it before applying...

    Run the following to get the SP info of sql server...

    SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel')) ServicePack,
    CONVERT(char(20), SERVERPROPERTY('ProductVersion')) BuildNumber


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  21. satya Moderator

    YOu have 3054 number and that means its SP2 and I can see you are on x64 bit, I suggest to open a case with CSS in this case as I have seen many issues with maintenance plans on x64 bit editions.
    Even search on MS connecthttp://connect.microsoft.com for any relevant feedback.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page