Sql server 2005 Jobs

Question

Post reply

Sql server 2005 Jobs

free_mascot

One Orange Chip

Points: 27169
More actions
January 14, 2008 at 12:25 am

#127504

Hello,
We have created maintenance job for our database backup and maintenance. We have kept the backup retention to 2 days. We have also created the Maintenance cleanup Task to remove the old backup files and scheduled to run everyday. But unfortunately old backup never gets deleted.
Any help will be grately appreciated.
Thanks in advance.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

What is the eaxact version of SQL you are using?

IF you have installed SQL2005 SP2 before march 6th, then there is a know bug on cleanup task.

This problem has been fixed in the latest Cumulative Update. Call MS Customer Service and ask for the SQL Server cumulative update. There is no charge for this service.

"More Green More Oxygen !! Plant a tree today"

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Thank you Minaz for your quick reply.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

UDBNT SSCommitted Points: 1626 More actions · Answer 3

You didn't say whether Minaz's solution sorted you out?

However, if you have say two database DB1 and DB2, each being backed up to what I might view a logical location (say D:\Backup\DB1 and D:\Backup\DB2) are you using the correct syntax in the tidy task?

i.e. Are you saying clear ALL the backups over 2 days old that sit in D:\Backup (with the include sub-folders ticked) or are you running two jobs one for each database being explicit about the database and the specific path D:\Backup\DB1 or D:\Backup\DB2?

Also did you call and flag the backup with the .BAK suffix?

Excuse my 'stating the obvious' but you might be flagging the wrong location\syntax so the tidy job doesn't see any backups to remove despite what you might think it can see?

Enjoy. ;¬)

mmackenzie SSC Enthusiast Points: 175 More actions · Answer 4

Are you backing up to a remote server? We had this issue where the file was being accessed by a user, even though it wasn't, and had to go to the other server and close the file before we could delete it.

try deleting using xp_cmdshell like this:

master.dbo.xp_cmdshell 'del //remote_server/backups/backup_file.BKP'

if this doesn't work you may need to go to the remote server and close the file. Rebooting the remote server will also release the hold on the file as well, but may not be an option.