Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Unhappy Unanswered: SQL ErrorLOG FOR SOX AUDIT

    We have SOX Audits going on and i was asked to show errologs,

    there is option in sql server which says limit the number of errorlogs 6 to 99
    i have unchecked this option long back but today when i went to see 6 month old log it doesnt have records for that date,Our sql server is restarting everynight
    in off business hours ( i am mentioning this becoz this does same job as sp_cycle_errorlog)

    i have log records from last year but entries for this year are missing, i dont know much about "sp_cycle_errorlog" , so what settings should i do to make my production server show Archive#1 ............ Archive#N as Logs separated by individual days without any break in sequence or missing days pls Help

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In short, you do not.

    An errorlog is created when the service starts, or when you issue sp_cycle_errorlog. Any reboot of the server, or any restart of the service will cause a new errorlog to be generated. It is not uncommon for service pack installs to generate 3 or 4 errorlogs.

    A more interesting question may be why is the SQL Server restarting every night?

  3. #3
    Join Date
    Aug 2009
    Posts
    73
    @MCRowley

    I am surprised i have all the logs from Sept2010 to Dec 2010, then i see Log Records For Month OF August 2011...In between everything is missing?? y is that???........


    , our server dont have much memory and our database size is big, sql server on 64 bit takes all the memory, though we have option to limit its memory, but there are 3-4 other applications installed on this server, ( before i had alloted 12GB of Memory to SQL Server out OF 16GB )
    Our Index Rebuilding JOB took all the memory and left our database in Inconsistent mode
    Last edited by amitwadhawan123; 08-12-11 at 12:39.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I don't think that checkbox does anything but let you increase the number of logs you keep. By default, SQL Server only keeps 7 logs (the current one, and six historical). If so, it is badly labeled.

  5. #5
    Join Date
    Aug 2009
    Posts
    73
    @ MCRowley Will setting up max no of log files to 99 solve the problem??

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For the span of 99 restarts of the service, anyway.

  7. #7
    Join Date
    Aug 2009
    Posts
    73

    Red face

    @MCRowley

    Just observed something, a new errorlog file got created,

    1. i havent restarted the server

    2. neither i recycled the log

    i have no idea whats happening with system , i its creating errorlog file on its own and why i have skipped records

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The top of the errorlog will tell you if the new errorlog was created by a restart of the service, or an execution of sp_cycle_errorlog. If you see messages about starting up databases, and listening on IP addresses, the service was restarted.

    If you see a message near the top of the errorlog that says :
    Code:
    The error log has been reinitialized. See the previous log for older entries.
    then someone ran sp_cycle_errorlog. Remember, this can be done by a windows scheduler task, or even a SQL Agent job.

  9. #9
    Join Date
    Aug 2009
    Posts
    73
    Thanks for Reply, i think there is no windows job running because the recycle log message appears at different times...

    Yup, i have started copying recent 20 logs to a separate folder timestamped ,

    how about if errorlog reaches limit of 99...will new log gets created and flushes the old one ??? or how it will work..i will appreciate if you can share your knowledge on this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •