• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1682
  • Last Modified:

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
0
BillPowell
Asked:
BillPowell
  • 5
  • 4
1 Solution
 
BillPowellAuthor Commented:
Further to that, running:
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.
0
 
Scott PletcherSenior DBACommented:
Typically our error logs are .1 not .01 ... did you change that somehow?
0
 
BillPowellAuthor Commented:
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??

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Scott PletcherSenior DBACommented:
Very.
0
 
BillPowellAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
>> 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?
0
 
BillPowellAuthor Commented:
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.
0
 
BillPowellAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
Thanks for the update ... it makes more sense now!  :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now