Tags

, ,


Today while trying to recycle errorlog in one of the Production server, I got the below error:

Unable to cycle error log file from ‘D:\SQL SERVER\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’ to ‘D:\SQL SERVER\MSSQL10_50.MSSQLSERVER\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.”

Basically it means that file is in use and hence it is cannot create the new file. There is a easy query to find out which process is currently using the Errorlog file:

SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
AND session_id <> @@spid
AND (
TEXT = ‘xp_readerrorlog’
OR TEXT = ‘sp_cycle_errorlog’
)
ORDER BY start_time

If we can go ahead and kill the process then we can Recycle the errorlog..

Hope this helps..Keep debugging 🙂

Cheers,

Subhro Saha

 

Advertisements