SQL SERVER ERRORLOG FILE UTILIZING THE FREE SPACE

Question

I'm stuck up in one of the issue.

I'm Using SQL SERVER 2008 R2. The SQL SERVER Log file is utilizing the disk space. When ever I clear the space in the DRIVE, the SQL SERVER Error log file starts to utilize the free space.

Log file path is available in C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50. WINCC\MSSQL\Logs\ERRORLOG

Kindly, help me to resolve the issue.

Answer 1

You can close the existing log file by running

sp_cycle_errorlog

Which will create a new empty file After that just delete the old log files You should probably look what errors are being logged & try and fix those so the new error log doesn’t fill up so fast

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql?view=sql-server-ver15

Answer 2

I agree with everything @Stephen said, especially about checking what is filling up your error log.

You can manage this in two different ways.

By Size

I am adding this code as an example ONLY. You need to adjust the numbers based on your need. I want to keep 20 files, each size 1 MB (1024 KB).

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1024
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

By Number of days you want to retain the error log

Set up a SQL Agent job to run at midnight with sp_cycle_errorlog. Configure how many of those files you want to keep with the below code—for example last 20 days.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 0
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 45
GO