Post reply

Sp_Cycle_ErrorLog not working

  • November 30, 2007 at 3:56 am

    #755871

    We try to avoid these huge errorlog files by cycling them at least every week.

    On our new servers the default is to cycle on a daily basis and keeping 35 days.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 30, 2007 at 4:00 am

    #755874

    it looks like running sp_cycle_errorlog actually deletes the archived files from 1 to the max number. so 5 runs of the SP will delete errorlog.1 thru to errorlog.5. i think thats probably what has happened.

  • November 30, 2007 at 4:30 am

    #755879

    i'm sure sp_cycle_errorlog does not delete the archive files, it moves them along (errorlog becomes errorlog.1, errorlog.1 becomes errorlog.2 etc). the only one that is removed is errorlog.6. Sounds like there is something strange going on if your files are being removed.

  • November 30, 2007 at 4:34 am

    #755880

    its probably deleting errorlog.6 then and moving everything else up one so it looks like errorlog.1 has been deleted and not moving the current errorlog to errorlog.1

  • November 30, 2007 at 4:59 am

    #755887

    Did you by any chance changed the location for the errologs recently?

    I once had a server there I changed the startup parameter and didn't noticed that it had a trailing space. As a result cycling the errolog changed the file Errorlog to Errrorlog .1 and it became invisible to EM because xp_enumerrorlogs won't find them.

    [font="Verdana"]Markus Bohse[/font]

  • June 9, 2008 at 5:53 pm

    #826404

    I had the same problem and it apparently couldn't cycle the error log was for the same reason that the logs were getting so large. Once I corrected the problem that was causing errors (and filling the logs up) I was able to run the sp_cycle_errorlog without a problem.

    Apparently if the log is being written to constantly, SQL server can't cycle it. That's why restarting the service works.

    Hope this helps.

  • October 27, 2008 at 2:04 pm

    #890233

    I have had this recently, its actually a problem outside of SQL causing the issue from what I can find.

    My errorlog. file was over 350m and sp_cycle_errorlog even when run multiple times did not cycle the error log. When I went into the directory, I noticed that errorlog.2 wasn't there (not sure why). I did a quick copy of errorlog.3 and renamed it to errorlog.2, then after running sp_cycle_errorlog again, I had a new errorlog. file, my errorlog.1 file was now the 350m+ file and again errorlog.2 was not in the folder.

    It fixed it without restarting sql (which in my production environment is not possible), but obviously its something I need to look into further...

  • October 28, 2008 at 2:03 am

    #890427

    I started this thread a long time ago!

    I think I eventually traced it down to some AV product still holding a lock on the error log file so i couldn't generate a new one.

    HTH

  • October 28, 2008 at 8:58 am

    #890694

    the times I have seen this it is due to the size of the errorlog, and is resolved by bouncing SQL or the box.

    If it won't cycle a 10MB log thats odd, maybe the server is really low on available memory?

    sysinternals (owned by microsoft) do a free tool called regmon which will tell you all registry activity in realtime, so will highlight any problems in that area.

    What would also be useful is a tool to show just the end of a large errorlog file, like the unix tail command.

    ...and I read pages 2 and 3 of a thread before posting............:blush: sorry

    ---------------------------------------------------------------------

  • August 21, 2009 at 2:31 pm

    #1043155

    we had the same issue 4.7 GB. the cycle procedure was renaming the archives and deleting the oldest one, but didn't touch the big file except that it would indicate the date changed.

    We ended up restarting the SQL service, that was the only thing that worked.

  • February 10, 2010 at 10:55 pm

    #1117381

    I also faced similar issue, wherein SQL Server 2000 ERRORLOG grown up to 300 MB.

    Sp_cycle_errorlog / DBCC ERRORLOG failed to recreate new errorlog file.

    Solution: Bounce the SQL Server Services.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • August 20, 2010 at 8:50 am

    #1209715

    I renamed it the errorlog.2 (1go) without restart SQL Server by errorlogold.2 and create a new one errorlog.2.

    I did sp_cycle_errorlog 6 times to have all my log cleans and now everything is ok.

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply