SQL Server Performance

SQL Server error log

Discussion in 'General DBA Questions' started by vaddi, Apr 24, 2007.

  1. vaddi New Member

    Hello,

    How can I recycle the sql server error log file and specify the retnetion period.

    I can use sPp_cycle_errorlog.

    How can I specify the size of each error log before it recycle or the retention period.

    Thanks
  2. alzdba Member

    I guess you'll have to schedule a monitoring job for it.

  3. satya Moderator

    You cannot specify a size to the error log, as suggested you have to schedule the job every Monday morning to clear up and if requird or based on the entries you can setup the job schedule.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. vaddi New Member

    Thanks for the replies.
  5. MohammedU New Member

    You can write your own script to do it...You can make use of the following...


    Create table #filedetails(
    alternatename char(20),
    size char(20),
    creation_date char(20),
    creation_time char(20),
    last_written_date char(20),
    last_written_time char(20),
    last_accessed_date char(20),
    last_accessed_time char(20),
    attributes char(20)
    )

    INSERT #filedetails EXEC master.dbo.xp-getfiledetails 'sql server errorlog with'
    if (select size from #filedetails ) > <fileSize>
    begin
    sp_cycler....
    end




    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. alzdba Member

    If you want to keep more history-files for the errorlog, you can alter the rollovernumber :
    EXECUTE master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWAREMicrosoftMSSQLServerMSSQLServer'
    ,N'NumErrorlogs'
    ,REG_DWORD
    ,20 -- number of errorlogs you want to keep
    GO
  7. satya Moderator

    ,... don't forget to assess the disk space on the drive where the SQL binaries are stored. Othewise you will have problem in starting up and having too many log files with disk space issues.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page