November 16, 2010 at 8:30 pm
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
November 16, 2010 at 10:11 pm
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.
November 17, 2010 at 4:22 am
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
November 17, 2010 at 10:12 am
Thanks Lynn and Pradeep for the information. I will investigate on it.
November 18, 2010 at 6:38 am
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
November 18, 2010 at 6:49 am
@ 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
November 18, 2010 at 8:39 am
SKYBVI (11/18/2010)
@ sagarIf 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
November 18, 2010 at 8:49 am
@ 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
November 18, 2010 at 2:35 pm
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
November 19, 2010 at 10:42 am
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
November 19, 2010 at 12:16 pm
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
November 19, 2010 at 12:17 pm
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
November 19, 2010 at 12:19 pm
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/
November 21, 2010 at 4:09 am
have you checked the net backup job status?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2010 at 6:27 am
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