SQL Server – Manage and Reduce Large Log Folder Sizes

logssql server

Please note that I'm not talking about transaction logs. I'm referring to a folder within SQL Server called 'Log' where ErrorLog and SQLDrmp files exists.

Well, the fact is that this folder is growing by 8GB a day on a machine dev machine that has 250gb total storage. That's a problem.

I figured out that I can create a new folder called Log2, run sp_cycle_errorlog get the new "errorlog" copy to this new folder, stop SQL Server (and agent) switch the folders, start SQL Server (and agent) again, and delete the old folder.

Ideally I would like to disable this kind of logging but if not possible, is there a way to automatically clean it? I mean, discard the logs perhaps?

Best Answer

It is recommended that you recycle your error log frequently. You can set that up as sql agent job to do that. I do this every night at midnight but you need to decide what is suitable for your environment.

System stored procedure "EXEC sp_cycle_errorlog" will do the recycling for you.

Once you have recycle set up you can decide how many files you want to keep (meaning how many days of error log you want to retain).

  • Expand the “Management” folder in SSMS.
  • Right click on “SQL Server Logs”
  • Select “Configure”
  • Check the box “Limit the number of error log files before they are recycled”
  • Pick some value to put in the “Maximum number of error log files” box
  • Click “OK” List was copied and edited from here.
Related Question