January 5, 2004 at 11:29 am
I created a SQL maintenance plan that suppose to delete backup files 1 day old but for some reason it is not doing so and my disk space is keep getting full. I understand that SQL run the backup job first before the deleting of old files, but how do SQL know which and how old the backup file is so it can delete? Does it look at the .BAK extension and compare it to the date create or does it compare it w/ the 'db_200401022300.BAK?' thanks in advance for your knowlegde..
January 5, 2004 at 1:52 pm
how do SQL know which and how old the backup file is so it can delete?
SQL Server keeps track of the backups in MSDB.
As for why the old backups aren't being deleted, if any part of the maintenance plan fails, SQL Server will not delete the old backups. This is to ensure that you still have your last good backup. Is the plan executing with an error? You can check your SQL error log.
January 5, 2004 at 1:52 pm
how do SQL know which and how old the backup file is so it can delete?
SQL Server keeps track of the backups in MSDB.
As for why the old backups aren't being deleted, if any part of the maintenance plan fails, SQL Server will not delete the old backups. This is to ensure that you still have your last good backup. Is the plan executing with an error? You can check your SQL error log.
January 5, 2004 at 3:05 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks...
January 5, 2004 at 3:05 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks...
January 5, 2004 at 3:06 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks...
January 5, 2004 at 3:06 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks...
January 5, 2004 at 3:07 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks...
January 5, 2004 at 3:07 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks... got an error so keep on replyin so got all these posts.. sowweee..
January 5, 2004 at 3:07 pm
One of the db did failed to backup so I guess this is what caused the old backup files not to be deleted. But it seem strange that even one of the db could not be backup, it still continue to backup the other but does not delete files of the db it succeeded. And continue on that, it seem to me SQL is using the 'db_200312292342.BAK' to mark backup files for deletion because I got several .BAK files that are old but do not get deleted. Please correct me or enlighten me. thanks...
January 5, 2004 at 3:29 pm
You are correct in your assumption that sqlmaint actually looks at the timestamp portion of the file name to determine which files to delete for a given database. It will not, for example, delete all old files with a .bak (or any other) extension. It will also not delete backup files that do not follow the DBName_db_YYYYMMDDHHMM.bak convention. You can artificially lengthen or shorten the "lifespan" of a file by modifying the timestamp portion of its name, as long as you don't change the DBName_db_ portion.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 5, 2004 at 3:47 pm
Thanks for the info and confirming my assumption.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply