A couple days ago, we found the errorlog file increased to almost 1 Gb, because this error message added to errorlog file repeatly.

Process ID 65:42 owns resources that are blocking processes on Scheduler 3.

When we checked this error, we also thought to create a job to check the errolog file size and send the email to me. This job also could recycle the errorlog file.

This is why we want to check the errorlog file size. I created this script. It worked fine.

Could we use other way to do this like use window monitor?


Thanks


Jim




declare @ErrorlogSize int

create table #DirErrorlog(line varchar(2000))
insert into #DirErrorlog
execute master..xp_cmdshell 'dir "e:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG"'

select @ErrorlogSize=CAST(REPLACE(SUBSTRING(line , CHARINDEX('ERRORLOG',line)-20, 19), ',','') AS int)
from #DirErrorlog where line like '%ERRORLOG%'

----select @ErrorlogSize
if @ErrorlogSize>100000000
begin

exec sp_cycle_errorlog

end

drop table #DirErrorlog