SQLServerAgent 錯誤：32

一項“每週日上午 12:00:00 將回收 SQL Server 代理錯誤日誌一次”的作業。本週末在 SQL Server 2008 R2 SP2 Enterprise 上一直失敗。

此作業在 MSDB 中執行系統儲存過程，此處sp_cycle_agent_errorlog記錄了該過程。

錯誤資訊是：

消息 22022，級別 16，狀態 1，第 0 行 SQLServerAgent 錯誤：32。

按照此 Connect item中的建議使用 procmon 。但是，根據 procmon 日誌（至少從我能讀到的），沒有其他程序使用這些 SQL Server 日誌文件，所以我對導致問題的原因感到困惑。此伺服器未配置 SCOM 或任何其他收集性能數據的軟體，如鍊接中所建議的那樣。重新啟動代理，作業現在執行。

我的問題是：根據下面的日誌，我還應該注意什麼，以避免再次出現同樣的問題？

"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",""

錯誤 32 在 winerror.h 中定義為：

ERROR_SHARING_VIOLATION

該程序無法訪問該文件，因為它正被另一個程序使用。

使用SysInternals 的 Handle.exe來確定誰（或什麼）打開了文件。

如果錯誤日誌位置是D:\MSSQL\ErrorLogs，您可以執行它來查看誰ERRORLOG打開了文件：

handle.exe D:\MSSQL\ErrorLogs\ERRORLOG

輸出將類似於：

搖桿 v3.51 
版權所有 (C) 1997-2013 Mark Russinovich 
Sysinternals - www.sysinternals.com 

sqlservr.exe pid：540 類型：文件 1F8：D:\MSSQL\ErrorLogs\ERRORLOG
notepad.exe pid：19270 類型：文件 1F8：D:\MSSQL\ErrorLogs\ERRORLOG

在上面的輸出中，您可以看到 SQL Server 和 Notepad.exe 都打開了文件。

引用自：https://dba.stackexchange.com/questions/102930