Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Red face Unanswered: SQL2K: Event Log File Size / Rotation

    Hello,

    I have a SQL Server that generates a reasonable amount of log data in the SQL Event logs. I'm finding it difficult to use this data as the individual log files become rather large i.e. several hundred MB before they rotate.

    What I would like to achieve: Set a constraint like: Each log will rotate when it reaches [XX]Mb in size and the files will be kept through [Y] rotations.

    I can't seem to find how to do this through Enterprise Manager. I've had a decent look through google and there seems to be no info on this forum....can anyone point me in the right direction?

    Thanks for your time.

    Rgds,
    Sithlordelvis.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    sp_cycle_errorlog will be your friend in this effort. You will have to do some work to get the file size of ERRORLOG, maybe by using xp_cmdshell to get a directory listing of the files in the LOG folder of the SQL Server data directory.

    Or you could choose to recycle the ERRORLOG on a periodic basis. Once a week works for us. I have it scheduled thru SQLServer Agent.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2004
    Posts
    11
    Tomh53,

    Thanks - I will give this a try. Strange that it is not handled more elegantly within Enterprise Manager....I hope it's handled better in sql server 2005 as we are migrating to '05 in about six months...

    Rgds,
    Sithlordelvis

    Quote Originally Posted by tomh53
    sp_cycle_errorlog will be your friend in this effort. You will have to do some work to get the file size of ERRORLOG, maybe by using xp_cmdshell to get a directory listing of the files in the LOG folder of the SQL Server data directory.

    Or you could choose to recycle the ERRORLOG on a periodic basis. Once a week works for us. I have it scheduled thru SQLServer Agent.

Posting Permissions

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