SQL Server Performance Forum – Threads Archive

Retention Policy

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

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.

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.

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

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.

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

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.

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.

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

Here is the script… create Procedure Sp_BackUpTransactionLog @Sys_Database Sysname, @BackupPath Sysname ,@FileDelDays int = 4 As Begin – Sp_BackUpTransactionLog @Sys_Database = ‘pubs’, @BackupPath = ‘\ServerNamec$ackup' SET NOCOUNT ON [email protected]_FileNameVarchar(255), @Dt_DateAndTimeVarchar(20), @Vch_SqlStringVarchar(255) if right(@BackupPath,1) <> ” select @BackupPath = @BackupPath+” select @Dt_DateAndTime = convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, getdate(), 112)+”+ Replace(convert(char(12), getdate(), 14),’:’,”) – 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),’:’,’_’) SELECT @[email protected]+replace (@@servername, ”, ‘$’)+’_’[email protected]_Database+’_tlog_’ + @Dt_DateAndTime +’.TRN' – SELECT @Vch_FileName SELECT @Vch_SqlString=’BACKUP LOG ‘+ @Sys_Database + ‘ TO DISK = ”’+ @Vch_FileName +”” SELECT @[email protected]_SqlString + ‘ WITH INIT , STATS = 10 ‘ –SELECT @Vch_SqlString EXECUTE(@Vch_SqlString) declare @cmd Varchar(1000) select @cmd = ‘del ‘[email protected]+replace (@@servername, ”, ‘$’)+’_’[email protected]_Database+’_tlog_’+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, getdate()[email protected], 112)+’*.trn' EXEC master.dbo.xp_cmdshell @cmd, no_output select @cmd = ‘del ‘[email protected]+’Completed’+replace (@@servername, ”, ‘$’)+’_’[email protected]_Database+’_tlog_’+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, getdate()[email protected], 112)+’*.trn' EXEC master.dbo.xp_cmdshell @cmd, no_output end MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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.

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

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,

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.

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

It is working fine for me…
What sql build you are on? Check the following msdn thread… http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=718788&SiteID=1
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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

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.

Thanks for your email. 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.. But i have SQL Server with SP1. (Build 3790: Service Pack 1) I am planning to apply SP2. Will that cause any problem? Thanks again for your help and really really apprecaited MohammedU, Thanks, cali

"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.

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.

]]>