October 1, 2008 at 2:18 pm
I am having an issue on all of my SQL 2005 instances where sp_cycle_errorlog sometimes fails to actually cycle the log. I get this error:
DESCRIPTION:Unable to cycle error log file from 'F:\MSSQL.2\MSSQL\LOG\ERRORLOG' to 'F:\MSSQL.2\MSSQL\LOG\ERRORLOG.1' due to OS error '32(The process cannot access the file because it is being used by another process.)'. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access."
I'm testing this on a test instance by running sp_cycle_errorlog in a job hourly. The job completes successfully, but the error log doesn't cycle. The ERRORLOG.1 file is gone, but the current ERRORLOG file isn't renamed to ERRORLOG.1 and no new entries get written to it.
I've taken away access from everything to the LOG directory except my own account and the SQL 2005 local groups in case some other process was trying to lock the file, but I'm still getting the error sporadically.
I don't have this problem with any of my SQL 2000 instances. Has anyone else seen this? Does anyone know of a fix?
October 1, 2008 at 2:34 pm
Is something else perhaps reading the error log file? An anitvirus, a text file, some other process?
p.s. could you please edit your post to fix the width?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 3:37 pm
If the service account doesn't have permissions to create a file (edit but not create), you might see something like that.....
You need to make sure that the account being used to start up SQL Server has the appropriate access to the logs folder.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply