Error: 17049, Severity: 16, State: 1. DBCC ErrorLog fail

DBCC ERRORLOG

Error: 17049, Severity: 16, State: 1.
Unable to cycle error log file from 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG' to 'D:\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."

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


kill the spid


rerun DBCC ERRORLOG and it successful


reference : Unable to cycle error log due to lock that will not clear

SQLAgent - TSQL JobStep (Job % to SQL Job Name

SQLAgent - TSQL JobStep (Job % to SQL Job Name



select sp.program_name
from
sys.dm_exec_requests req
left join sys.sysprocesses sp on sp.spid=req.session_id
where (req.session_id>50 or req.session_id is null) and req.session_Id NOT IN (@@SPID)
order by logical_reads




go


program_name
SQLAgent - TSQL JobStep (Job 0x8BF803BD5BFFD3448392075F1E82E037 : Step 1)


what is the job name that execute?


SELECT CASE
   WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
   ELSE RTRIM([program_name])
  END ProgramName
  , Val1.UqID
  , Val1.UqIDStr
FROM sys.sysProcesses P
  CROSS APPLY
   (
   VALUES (
     CASE
      WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
       CAST(
        SUBSTRING(RTRIM([program_name]), 32, 8) + '-' +
        SUBSTRING(RTRIM([program_name]), 40, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 44, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 48, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 52, 12)
       AS uniqueidentifier)
      ELSE NULL
     END ,
     CASE
      WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
       CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)
      ELSE NULL
     END
     )
   ) Val1(UqID, UqIDStr)
  OUTER APPLY
   (
   SELECT DISTINCT Name
   FROM MSDB.dbo.SysJobs
   WHERE CAST(Job_ID AS varchar(50)) = Val1.UqIDStr
     OR Job_ID =  Val1.UqID
     OR CAST(Job_ID AS varchar(50)) = Val1.UqID
   ) J
WHERE RTRIM([program_name]) ='SQLAgent - TSQL JobStep (Job 0x8BF803BD5BFFD3448392075F1E82E037 : Step 1)'  




Extract from
http://www.sqlservercentral.com/Forums/Topic1648616-392-1.aspx