none
SQL Server Logs too big under Management RRS feed

  • Question

  • Hi,

    The sqlserver logs ("Current - date","Archive-date"...) is too big to open easily (esp at the time of crisis). Any idea how can I restrict the size of these logs to manageable size so that they can be read immediately? Is there any way to Archive daily logs separately?

    Thanks

    Chander

    Wednesday, August 25, 2010 9:44 AM

Answers

  • Check if this help http://www.mssqltips.com/tip.asp?tip=1155

     

    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Wednesday, August 25, 2010 10:39 AM
    Moderator
  • I have had a request where I needed to log both successful and failed logins for the SQL server, in this case the SQL would write around 50k rows each day in to errorlog file. For this problem i had to increase the number of error log files first i.e.) By default SQL holds only 6 errorlog files by default (errorlog, errorlog.1...Errorlog.5 on disk) set this value to a greater number that would suit your environment (I have set 80) and the second thing is run the sp_cycle_errorlog (dbcc errorlog) on a fixed schedule using SQL agent jobs so that the logs are written in to a new file now. Also make sure you archive the old logs on to some other folders where SQL service account doesn't have access (so it can’t delete :-)).


    Thanks, Leks
    Thursday, August 26, 2010 1:26 AM
    Answerer

All replies

  • Check if this help http://www.mssqltips.com/tip.asp?tip=1155

     

    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Wednesday, August 25, 2010 10:39 AM
    Moderator
  • I have had a request where I needed to log both successful and failed logins for the SQL server, in this case the SQL would write around 50k rows each day in to errorlog file. For this problem i had to increase the number of error log files first i.e.) By default SQL holds only 6 errorlog files by default (errorlog, errorlog.1...Errorlog.5 on disk) set this value to a greater number that would suit your environment (I have set 80) and the second thing is run the sp_cycle_errorlog (dbcc errorlog) on a fixed schedule using SQL agent jobs so that the logs are written in to a new file now. Also make sure you archive the old logs on to some other folders where SQL service account doesn't have access (so it can’t delete :-)).


    Thanks, Leks
    Thursday, August 26, 2010 1:26 AM
    Answerer