can't delete old back up file created by sql server 20005.

  • Hi,

    I have encountred strange error on sql server 2005.

    I have big sql server database, that is the reason i can only keep only one day of full back up so maintanance plan delete old back up then create new full back up. maintance job showing success to delete but file still not deleted and when full back up kick off then it goes over the size of drive and back up fails. This is intermittent. I could not find the reason behind that and when i try to delete manually, error says file is in use by user or process. Need help to find out the reason and if there is any better solution for this kind of maintenance plan. When i had enough space i keep for two day of back up and and did not have any problem to clean up...

    I will really appreciate for any help.

    Thanks

    Sagar

  • Can't necessarily help with the problem at hand, but I would recommend looking into some way of compressing your backup files. You could use a zip program after the backup completes, or use a third-party tool. I would actually recommend HyperBac (now a RedGate product). Used it at my previous employer to compress our SQL Server 2005 backups. It reduced backup/restore times by about 50%, and compressed files to 10%-20% of original size. Did all of this on the fly with no changes to any of our backup processes. You still use the native BACKUP and RESTORE commands.

  • Sagar-636902 (11/16/2010)


    Hi,

    I have encountred strange error on sql server 2005.

    I have big sql server database, that is the reason i can only keep only one day of full back up so maintanance plan delete old back up then create new full back up. maintance job showing success to delete but file still not deleted and when full back up kick off then it goes over the size of drive and back up fails. This is intermittent. I could not find the reason behind that and when i try to delete manually, error says file is in use by user or process. Need help to find out the reason and if there is any better solution for this kind of maintenance plan. When i had enough space i keep for two day of back up and and did not have any problem to clean up...

    I will really appreciate for any help.

    Most likely the Tape backup or Antivirus process is using the backup file. You can make use of Process Explorer to find out which process is accessing that file. More details here[/url]

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Thanks Lynn and Pradeep for the information. I will investigate on it.

  • Pradeeps suggestion for Process Explorer is a great way to pinpoint what has the file open.

    You might want to look at all the Sysinternals tools - a really good set of free utilities.

    And to add to compression - if it is indeed another process backing up the file to another location - if your backup is compressed, this will go faster and use less media.

    Just make sure you understand how to recover should you need to restore from backup.

    Greg E

  • @ sagar

    If you dont need previous day's backup, you can also choose to over write the last day backup.

    In that way, there will not be disk space problem and also no need to rely on maintenance plans to delete previous day's backup.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • SKYBVI (11/18/2010)


    @ sagar

    If you dont need previous day's backup, you can also choose to over write the last day backup.

    In that way, there will not be disk space problem and also no need to rely on maintenance plans to delete previous day's backup.

    Regards,

    Sushant

    If the file is locked by some process, I don't think this is an option.

    Even limiting yourself to 1 backup is usually not a real good practice.

    Greg E

  • @ greg

    In his scenario too, he doesnt have space for more than 1 backup thats why he is deleting the older backups, so overwriting cant be ruled out.

    If the file is overwritten, then he doesn't have to delete the older file (so, no case of getting error while deleting part)

    He can try that, if it works fine, then this can be an alternative. Moreover, also there are more ways as you guys specified.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • I'd be interested in seeing how if a file is locked up and in use, and you cannpt delete it, you are able to overwrite it.

    Maybe if SQL server has it locked, but that doesn't seem to be the case.

    How large is the file and what drive it is on - hopefully not the C:\ drive, might be interesting.

    Another drive added might be an easy and cheap workaround, that would at least allow for more backups.

    Greg E

  • Thanks all for the sharing information. I have regular net back up. but i do local back up/ log back up as well just to make sure the avoid other issue. DB is about 150 GB (and it is not in C: drive ..... lol). we deffinetely need to add the drive but that will not be the case right now as of company. However i have split up into different drives by doing file group back up. which gives more space and manage the backup file here and there. Any idea on to impact one restoring db that people are facing from file group back up comparision to Full single back up.

    Thanks

    Sagar

  • 150GB - I would seriously consider looking into a compression tool.

    Especially since this gets copied elsewhere.

    Probably not too hard to justify just based on media and time.

    Greg E

  • As a temporary work around, it is ok to rely on file group backups. But it is always safe to have a full backup at regular intervals. You never know when you need the latest full backup.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Another handy light-weight tool you could use to find out what's locking the file is Unlocker found here: http://ccollomb.free.fr/unlocker/

  • have you checked the net backup job status?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Ya I found that file was Handled by net back up.. I have changed bkup schedule... How do I remove that lock with out installing third party tool.. Just incase in future. Does anyone has idea?

    Thanks

    Sagar

Viewing 15 posts - 1 through 15 (of 15 total)

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