Prev: SQL Server Failed Assertions
Next: dm_exec_requests start_time not being updated?
From: Chris Wood on 3 Jul 2008 12:57
Hi,

We reboot our servers only once a month for patching and so we cycle the SQL
errorlogs twice a week to keep them to a reasonable size. Every now and
again a problem happens with our SQL Agent job that cycles the errorlog.
Last night the job reported as run successfull but a new errorlog file was
not produced. The older errorlogs have been renamed but errorlog was not
renamed as errorlog.1

If I use SSMS to view the current errorlog I can see the attempting to cycle
error log message from last night and all the entries from before that time.
I have attempted to see if I could force the cycling by running the job
again and now I do not have either an errorlog.1 or errorlog.2 file. At the
same time if I use notepad to view the errorlog I have a real mix of the
latest entries along with the ones that followed the cycling last night
followed by all the entries for the errorlog that I could see by using SSMS.

I know that the only way out of this mess is to get SQL stopped and
re-started but why is SQL2005 doing this? The previous errorlog was not
massive so it should have been able to handle the complete cycling. We are
running with 10 archives so we can have a months worth of logs on disk. This
server has build 3159 on it, soon to go to build 3239.

Thanks

Chris


From: Linchi Shea on 3 Jul 2008 17:16
Most likely that's because your errorlog.1 file was locked by some other
process. You can confirm this by opening errorlog.1 with Word, and then issue
sp_cycle_errorlog.

Linchi

"Chris Wood" wrote:

> Hi,
>
> We reboot our servers only once a month for patching and so we cycle the SQL
> errorlogs twice a week to keep them to a reasonable size. Every now and
> again a problem happens with our SQL Agent job that cycles the errorlog.
> Last night the job reported as run successfull but a new errorlog file was
> not produced. The older errorlogs have been renamed but errorlog was not
> renamed as errorlog.1
>
> If I use SSMS to view the current errorlog I can see the attempting to cycle
> error log message from last night and all the entries from before that time.
> I have attempted to see if I could force the cycling by running the job
> again and now I do not have either an errorlog.1 or errorlog.2 file. At the
> same time if I use notepad to view the errorlog I have a real mix of the
> latest entries along with the ones that followed the cycling last night
> followed by all the entries for the errorlog that I could see by using SSMS.
>
> I know that the only way out of this mess is to get SQL stopped and
> re-started but why is SQL2005 doing this? The previous errorlog was not
> massive so it should have been able to handle the complete cycling. We are
> running with 10 archives so we can have a months worth of logs on disk. This
> server has build 3159 on it, soon to go to build 3239.
>
> Thanks
>
> Chris
>
>
>
 | 
Pages: 1
Prev: SQL Server Failed Assertions
Next: dm_exec_requests start_time not being updated?