June 1, 2008 at 11:14 am
I am having some issues with backups. My log backups are failing with the error 'file in use by another user process' I was able to determine that my non_human user sqls_svc (service user that all jobs run under) still had a lock on 6 files.
We just rebooted and failed over our sql server db on a cluster. We are using sql server 2000, sp4 8.0.2187 on windows server 2003 sp1. I can't find any information as to why the files are still 'locked / in use' by the sql user. To fix this for now we had to bounce the backup file server.
Any help will be appreciated.
Thanks
June 2, 2008 at 1:02 pm
SQL Agent should not lock the files, if it is locked then your backup job is still running...
Did you try closing the open files instead of restarting the sql server? if not try it...
Are you using native backups?
MohammedU
Microsoft SQL Server MVP
June 2, 2008 at 1:13 pm
Is there any other log backup running?
To check whether or not there is any other application running, you may try the following commands,
sp_who2 active
DBCC OPENTRAN
June 2, 2008 at 1:45 pm
- for sure an active backup to that file will lock it.
- most of us tend to (x)copy the file to a safe zone at the end of the backup job. This will also lock the file.
- some of us use other tools to copy the backups to a safe zone.
-did you try sysinternals handle, or one of their tools to find out who holds the file lock ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 2, 2008 at 2:11 pm
we are using native client for the log backups. I do not know how to close the files. The logs are backed up from a regular scheduled job. This job failed everytime it ran - I finally found the open files when I logged onto the file server and used the utility to manage files. I knew the file was locked by something as i tried to rename the file, delete and move it. Kept getting file in use error. It was not cleared until we booted the file server.
do you have any idea why the system felt that file was still open?
Geri
June 2, 2008 at 2:17 pm
The logs backup to this file server then the tape backups run between midnite and 6 am. These files locked after that tape backup - nothing but the sql server should have been accessing that file. For some reason the file still had a lock on it from sql server even after we physically rebooted the cluster and restarted sql server and the agent.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply