BillPowell
asked on
Issues with sp_cycle_errorlog
In order to keep the SQL Server Error Log files small, we run sp_cycle_errorlog once a day on each server. I have noticed a strange behaviour where The SQL Server Log Viewer in Enterprise Manager will not display the typical list of old logs. It will only show the current one. An inspection of the physical drive shows that the log files are in fact there with the proper name and .01, .02, .03.... extensions.
This behaviour only appears to affect servers that we run the procedure sp_cycle_errorlog on. Any other server that cycles the log only on restarting SQL Server shows the list of archived log files as it should.
Has anyone seen this behaviour before? Is there any way to restore the functionality in EM and still use sp_cycle_errorlog?
Thanks
This behaviour only appears to affect servers that we run the procedure sp_cycle_errorlog on. Any other server that cycles the log only on restarting SQL Server shows the list of archived log files as it should.
Has anyone seen this behaviour before? Is there any way to restore the functionality in EM and still use sp_cycle_errorlog?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, that happens automatically. We also change to save 20 logs instead of the default 6. Maybe that has something to do with the naming. Maybe if you specified to save 100 logs, the naming would be like .001, .002 etc.
In any case trying
Exec sp_readerrorlog 11
will yield the same results.
Strange??
In any case trying
Exec sp_readerrorlog 11
will yield the same results.
Strange??
Very.
ASKER
After some more testing, Ive found that running sp_cycle_errorlog in QA several times, does not adversly effect EM's abiltiy to view the log files and also the naming conforms to the norm of .1, .2,.3 instead of .01, .02, .03 despite the fact that it has been set to retain 20 logs.
The log is cycled by a Windows Scheduled task calling a cmd file that runs the following line:
osql /S %DB_SERVER% /E /Q "EXIT(sp_cycle_errorlog)"
Im not sure what the "EXIT" portion is all about.
The only other difference I have noticed is that the error log files are not named ERRORLOG.1
they are instead named
InstanceName_ERRORLOG.1
Im not sure what one has to do to get the name of the log changed to include the SQL Server Instance?
The log is cycled by a Windows Scheduled task calling a cmd file that runs the following line:
osql /S %DB_SERVER% /E /Q "EXIT(sp_cycle_errorlog)"
Im not sure what the "EXIT" portion is all about.
The only other difference I have noticed is that the error log files are not named ERRORLOG.1
they are instead named
InstanceName_ERRORLOG.1
Im not sure what one has to do to get the name of the log changed to include the SQL Server Instance?
>> Im not sure what one has to do to get the name of the log changed to include the SQL Server Instance? <<
Perhaps just if you have a named instance? / multiple instances on the same server?
Perhaps just if you have a named instance? / multiple instances on the same server?
ASKER
It doesnt appear so. It seems as if using sp_Cycle_ErrorLog will perform the cycle but will append the instance name to the log filename. However performing SQL Server restarts will leave the default name of ERRORLOG.1 without the instance name.
ASKER
Correction, sp_cycle_errorlog will not append the instance name to the error log. I have found the culprit. There was a process running daily that would rename those files. Once I altered the job, that behaviour ceased.
Thanks for the update ... it makes more sense now! :-)
ASKER
Exec sp_readerrorlog 1
will return this error:
Server: Msg 22004, Level 16, State 1, Line 0
xp_readerrorlog() returned error 2, 'The system cannot find the file specified.'
It seems like SQL Server has no idea where to find the file if the log cycle was completed using sp_cycle_errorlog.