Sql-server – SQLServerAgent Error: 32

errorssql serversql-server-2008-r2sql-server-agenttroubleshooting

A job that "will Recycle SQL Server Agent Error Logs once every week on Sunday at 12:00:00 AM." has been failing this weekend on SQL Server 2008 R2 SP2 Enterprise.

This job executes a system stored procedure in MSDB, sp_cycle_agent_errorlog that is documented here.

The error message is:

Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: 32.

Used procmon as suggested in this Connect item.
However, according to the procmon log (at least from what I can read) there are no other processes that use these SQL Server log files, so I'm puzzled as to what is causing the problem. This server is not configured with SCOM or any other software that collects performance data, as suggested in the link.
Restarted the Agent and the job runs now.

My question is: anything else I should look at, based on the log below, to avoid the same problem showing up again ?

"Time of Day","Process Name","PID","Operation","Path","Result","Detail"
"10:29:57.1027987","Explorer.EXE","8300","NotifyChangeDirectory","E:\Log","SUCCESS","Filter: FILE_NOTIFY_CHANGE_FILE_NAME, FILE_NOTIFY_CHANGE_ATTRIBUTES, FILE_NOTIFY_CHANGE_LAST_WRITE"
"10:29:57.1030609","SQLAGENT.EXE","3768","CreateFile","E:\Log","SUCCESS","Desired Access: Write Data/Add File, Synchronize, Disposition: Open, Options: , Attributes: n/a, ShareMode: Read, Write, AllocationSize: n/a, OpenResult: Opened"
"10:29:57.1032305","SQLAGENT.EXE","3768","CloseFile","E:\Log","SUCCESS",""
"10:29:57.1032815","Explorer.EXE","8300","NotifyChangeDirectory","E:\Log","SUCCESS","Filter: FILE_NOTIFY_CHANGE_FILE_NAME, FILE_NOTIFY_CHANGE_ATTRIBUTES, FILE_NOTIFY_CHANGE_LAST_WRITE"
"10:29:57.1034443","Explorer.EXE","8300","NotifyChangeDirectory","E:\Log","SUCCESS","Filter: FILE_NOTIFY_CHANGE_FILE_NAME, FILE_NOTIFY_CHANGE_ATTRIBUTES, FILE_NOTIFY_CHANGE_LAST_WRITE"
"10:29:57.1042525","Explorer.EXE","8300","NotifyChangeDirectory","E:\Log","SUCCESS","Filter: FILE_NOTIFY_CHANGE_FILE_NAME, FILE_NOTIFY_CHANGE_ATTRIBUTES, FILE_NOTIFY_CHANGE_LAST_WRITE"
"10:29:57.1049382","Explorer.EXE","8300","NotifyChangeDirectory","E:\Log","","Filter: FILE_NOTIFY_CHANGE_FILE_NAME, FILE_NOTIFY_CHANGE_ATTRIBUTES, FILE_NOTIFY_CHANGE_LAST_WRITE"
"10:29:58.1094078","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Data/List Directory, Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1094489","Explorer.EXE","8300","FileSystemControl","E:\Log","INVALID DEVICE REQUEST","Control: FSCTL_LMR_QUERY_DEBUG_INFO"
"10:29:58.1095021","Explorer.EXE","8300","CloseFile","E:\Log","SUCCESS",""
"10:29:58.1096909","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Data/List Directory, Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1097311","Explorer.EXE","8300","FileSystemControl","E:\Log","INVALID DEVICE REQUEST","Control: FSCTL_LMR_QUERY_DEBUG_INFO"
"10:29:58.1097812","Explorer.EXE","8300","CloseFile","E:\Log","SUCCESS",""
"10:29:58.1098886","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Data/List Directory, Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1099237","Explorer.EXE","8300","FileSystemControl","E:\Log","INVALID DEVICE REQUEST","Control: FSCTL_LMR_QUERY_DEBUG_INFO"
"10:29:58.1099827","Explorer.EXE","8300","CloseFile","E:\Log","SUCCESS",""
"10:29:58.1100790","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Data/List Directory, Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1101096","Explorer.EXE","8300","FileSystemControl","E:\Log","INVALID DEVICE REQUEST","Control: FSCTL_LMR_QUERY_DEBUG_INFO"
"10:29:58.1101561","Explorer.EXE","8300","CloseFile","E:\Log","SUCCESS",""
"10:29:58.1175276","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Data/List Directory, Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1175681","Explorer.EXE","8300","FileSystemControl","E:\Log","INVALID DEVICE REQUEST","Control: FSCTL_LMR_QUERY_DEBUG_INFO"
"10:29:58.1175952","Explorer.EXE","8300","QueryDirectory","E:\Log","SUCCESS","0: ., 1: .., 2: CommandLogCleanup_0x7ACA4A54174CFA42B688E05C5B452E2C_1_20150503_1.txt, 3: CommandLogCleanup_0x7ACA4A54174CFA42B688E05C5B452E2C_1_20150510_1.txt, [....]
"10:29:58.1188121","Explorer.EXE","8300","QueryDirectory","E:\Log","NO MORE FILES",""
"10:29:58.1221598","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Attributes, Read Control, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1222496","Explorer.EXE","8300","QuerySecurityFile","E:\Log","BUFFER OVERFLOW","Information: Owner, DACL"
"10:29:58.1223064","Explorer.EXE","8300","QuerySecurityFile","E:\Log","SUCCESS","Information: Owner, DACL"
"10:29:58.1223416","Explorer.EXE","8300","CloseFile","E:\Log","SUCCESS",""
"10:29:58.1224553","Explorer.EXE","8300","QueryOpen","E:\Log","SUCCESS","CreationTime: 10/09/2013 15:32:18, LastAccessTime: 01/06/2015 10:29:57, LastWriteTime: 01/06/2015 10:29:57, ChangeTime: 01/06/2015 10:29:57, AllocationSize: 131,072, EndOfFile: 131,072, FileAttributes: DNCI"
"10:29:58.1227712","Explorer.EXE","8300","CreateFile","E:\Log","SUCCESS","Desired Access: Read Attributes, Read Control, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened"
"10:29:58.1228076","Explorer.EXE","8300","QuerySecurityFile","E:\Log","BUFFER OVERFLOW","Information: Owner, DACL"
"10:29:58.1228461","Explorer.EXE","8300","QuerySecurityFile","E:\Log","SUCCESS","Information: Owner, DACL"
"10:29:58.1228844","Explorer.EXE","8300","CloseFile","E:\Log","SUCCESS",""

Best Answer

Error 32 is defined in winerror.h as:

ERROR_SHARING_VIOLATION
The process cannot access the file because it is being used by another process.

Use SysInternals' Handle.exe to determine who (or what) has a file open.

If the error log location is D:\MSSQL\ErrorLogs, you could run this to see who has the ERRORLOG file open:

handle.exe D:\MSSQL\ErrorLogs\ERRORLOG

The output will look similar to:

Handle v3.51  
Copyright (C) 1997-2013 Mark Russinovich  
Sysinternals - www.sysinternals.com  

sqlservr.exe       pid: 540    type: File           1F8: D:\MSSQL\ErrorLogs\ERRORLOG
notepad.exe        pid: 19270  type: File           1F8: D:\MSSQL\ErrorLogs\ERRORLOG

In the above output, you can see both SQL Server and Notepad.exe have the file open.

Related Question