4

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?

Improve this question
9
  • What is it that's using all the space?
    – Gareth Lyons
    Commented Apr 6, 2017 at 13:17
  • Check out this thread - dba.stackexchange.com/questions/81388/…
    – rvsc48
    Commented Apr 6, 2017 at 13:28
  • What are the biggest files in this folder? If it is the SQL errorlogs, what is writing so many entries?
    – datagod
    Commented Apr 6, 2017 at 13:36
  • Probably the dump files...most of which are likely old enough to be useless, especially on a dev box :)
    – Kevin3NF
    Commented Apr 6, 2017 at 13:41
  • 1
    Welp, first thing is that there's a reason for all those dump files. You need to look into that as a priority. You could be getting corruption problems and all kinds of issues that you aren't aware of right now. Once you fix that those won't be created any longer and your disk space issue will go away.
    – Nic
    Commented Apr 6, 2017 at 19:48

2 Answers 2

Reset to default
3

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.
Improve this answer
1

For SQL instance prior to SQL 2012

Cycle the error logs with EXEC sp_cycle_errorlog, you may want to consider increasing the number of logs you keep if you cycle the error logs regularly.

You can set up a SQL Agent job with a T-SQL step. All it has to do is EXEC sp_cycle_errorlog. Schedule the SQL Agent job to run as frequently as you’d like and you’re good to go. The upside of this approach is that it’s automatic and the SQL Server error logs will be more granular, making it easier to find the error messages you’re looking for. Source, brentozar.com

For SQL 2012 and later you can set the max log file size. Again you may want to consider increasing the number of logs you keep if limit the error log size.

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

Source, microsoft.com

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.