SQLServerAgent Error: The process cannot access the file because it is being used by another process
-
I have created the following as an automated job to run at midnight on every database server I manage:
This job fails intermittently on some servers, but not others and I get the following error:
SQL Agent - Jobs Failed: The SQL Agent Job "ucjSQLerrorLogNightlyCycle" failed at Wednesday, November 22, 2017 12:00:00 AM with the error "The job failed. The Job was invoked by Schedule 9 (NightlySQLError). The last step to run was step 1 (SQLErrorLogCycle). - Executed as user: MyDomain\MyServiceAccount. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed."
I have performed an intensive search on this error message and have not found much of anything. The only gem I found was to use Process Explorer from the SysInternals suite by Mark Russinovich. However, isolating the SQLAgent.exe binary did not show me what other process is using it; the process is lone. I am either performing the incorrect steps or my process is completely wrong.
Other items to note:
- I have verified that another process isn't running at the same time
- I have ran the job in the middle of the day on a weekend and it will keep running until it fails
- I can copy and paste the code into the SSMS or Toad and the code will execute as expected
EDIT- Added exclusion to antivirus for the log path that SSA manipulates, this made difference as the issue still exists
- I disabled the antivirus and performed the same aforementioned steps with the same result
- Broke the job down into two different steps. The code EXEC sp_cycle_errorlog; works inside of a job and in the query analyzer. The code EXEC dbo.sp_cycle_agent_errorlog; does NOT work in a job OR in the query analyzer.
- Restarting the SQL Server Agent allowed me to run the job successfully, but I don't consider this a "fix"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else. -
It's usually due to jobs that run continuously such as replication jobs. You can hit it intermittently with other jobs as well.
Look at the jobs that are running continuously. For intermittent, just rerun the cycle error log.
You can look at the jobs you have running right now in Job Activity monitor to see if any of those are scheduled with Start Automatically When SQL Agent Starts. Those are continuously running jobs.Sue
-
also check os level backup might be locking the file and/or change time for your sql job to avoid collision
-
@Sue_H - there were no jobs running at the same time the job is scheduled to run, nor during my tests in the middle of the day.
@goher2000 - Are you referring to a 3rd-party backup like Veeam, BackupExec, and the like?
Also, I've edited my OP to add more info.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else. - Sean Perkins - Wednesday, November 29, 2017 8:22 AM
Try executing the following - how many rows are returned?
SELECT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobschedules js
ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s
ON js.schedule_id = s.schedule_id
WHERE s.freq_type = 64
AND s.enabled = 1
AND j.enabled = 1Sue
- Sue_H - Wednesday, November 29, 2017 12:07 PMSue_H - Wednesday, November 29, 2017 12:07 PM
@Sue_H
I get nothing in my result set.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else. -
I had the same issue on various servers, and ended up putting a loop in place to test for something reading or writing to the logs.
This code can certainly be improved, but it works for now.
DECLARE @i int
SET @i = 1WHILE EXISTS(SELECT a.session_id
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id <> @@spid
AND (text LIKE '%xp_readerrorlog%' OR text LIKE '%sp_cycle_errorlog%')
AND @i <= 3
) BeginSET @i = @i + 1
WAITFOR DELAY '00:02'
EndUSE Master
GO
IF DATEPART(DW, getdate()) = 7 Begin
EXEC master.sys.sp_cycle_errorlog;
End
Go
USE msdb
Go
IF DATEPART(DW, getdate()) = 7 Begin
EXEC msdb.dbo.sp_cycle_agent_errorlog;
End
GOMichael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Sean Perkins - Wednesday, November 29, 2017 12:25 PM
Thanks for posting back. I would suspect that it is a process external to SQL Server. If you want to try checking with Process Explorer again, you would want to search on the file. One issue with that is the cycle process renames the other archived logs (.1 becomes .2 then .2 becomes .3...etc) so it could be on one of the other files not just the current log file. I think you can do a find with a wildcard so you could try a find on SQLAgent* . Otherwise the current log, SQLAgent.Out may show the issue.
Another option is to use Process Monitor (by the same person), just check the file activity and filter on just the SQL Agent log files (filter using the Path = and then the path to the file or files for the Agent logs). You can set Process Monitor up to show just file system activity. I've had better luck using that - you will get all activity on the files but the unexpected ones become obvious. Normally on cycling the log it would just be accessed by SQLAgent.exe, svchost.exe and Explorer.exe. If it's something like AV software or system backup process/software, you should be able to catch that.
Sue
-
here is how I will troubleshoot.
download handle.exe https://docs.microsoft.com/en-us/sysinternals/downloads/handle
add another step in job to run handle.exe x:\.......errorlog.log' when the 1st step failsin theory this should tell us which app/process is holding lock on the file.
and yes to @Sean Perkins re: '@goher2000 - Are you referring to a 3rd-party backup like Veeam, BackupExec, and the like?'
- goher2000 - Thursday, November 30, 2017 8:53 AM
So I'm curious - How would using handle.exe be different from Process Explorer? Even the link states you get the same information with Process Explorer.
I think you'd want to point to the SQL Agent error file instead of the SQL Server error file.Sue
-
I may be wrong but my understanding is that process explorer is graphical tool, you probably need to use keyboard shortcut Ctrl+F. or click the “Find” menu and select “Find a Handle or DLL” and type in the filename (you can input only /e /t /p /s switches from command line) , handle.exe being command line utility can be use with in the job it self as step to identify the process locking the file and the execution of handle.exe will be almost immediately after the error. I understand the output will be same however with handle.exe it will be way closer to the time when error occurred
As for pointing to which file, you are right about it.
-
just for clarity see the example below, my apologies in advance for not mentioning the correct filename again .
Handle.exe -a C:\Path\To\Resource\That\Is\Locked\Open > Output.txt - goher2000 - Thursday, November 30, 2017 2:20 PM
The time of the occurrence doesn't matter - it's whatever process is locking it.
But consider this scenario. You have a job step calling handle.exe and if the lock isn't there at that very moment then goes to the next job step to cycle and in this time the file is locked then you have nothing reported back. So you'd need a continuous lock or be lucky with the timing. The post indicated it is intermittent which sounds like you need to be depend on the being lucky with the timing. Same thing if you use that step to go to after a failure. It's a different approach but I'd be careful about assuming something like "This will work".
All of the archive files get renamed in the process and one is deleted so it could be any of the files, not necessarily just the current log file.Sue
-
Well I guess, I mentioned in my earlier post executing handle should be the second step, it should execute only if the 1st step (recycle) fails, I still think it has a fair chance to caught the process locking on to the file, if this does not work then, good luck and happy staring at process explorer daily at midnight or whenever the job run (since this is interim ) you will be able to catch the locking process one day
- goher2000 - Thursday, November 30, 2017 4:04 PM
No need to be a snarky. Hope your day gets better -
Sue
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply