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

• suresh kumar: Hi Stephen, I am frequently getting this issue, what is the permanent solution stop logging of error in the SQL Server ERRORLOG file. Initially, Due to this issue my C Drive was utilized the free space in 2months, but now it is utilizing the free space faster than before with in 2 days it is utilized 21GB and My C drive capacity is 250 GB, Kindly provide a permanent solution for stopping of ERRORLOG file or how to reduce the ERRORLOG file filling size.
• suresh kumar: ERROR FILE FILLING NT_AUTHORITY\ SYSTEM 2021年04月09日 15:18:39.29 spid81 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) Jun 28 2012 08:42:37 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64) 2021年04月09日 15:18:39.29 spid81 (c) Microsoft Corporation. 2021年04月09日 15:18:41.61 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. 2021年04月09日 15:18:42.04 Logon Error: 18456, Severity: 14, State: 38. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]2021年04月09日 15:18:44.41 spid81

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
 

• suresh kumar: Hi Stephen, I am frequently getting this issue, what is the permanent solution stop logging of error in the SQL Server ERRORLOG file. Initially, Due to this issue my C Drive was utilized the free space in 2months, but now it is utilizing the free space faster than before with in 2 days it is utilized 21GB and My C drive capacity is 250 GB.
• SqlWorldWide: You need to check what is getting logged? What is generating hose messages and fix that issue? Are you recycling your error log? What number of days you are retaining those error logs? Are their specific error that is getting logged millions of times?
• suresh kumar: ERROR LOG FILE FILLING THE SAME MESSAGE OF MULTIPLE ENTRIES : NT_AUTHORITY\ SYSTEM 2021年04月09日 15:18:39.29 spid81 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) Jun 28 2012 08:42:37 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64) 2021年04月09日 15:18:39.29 spid81 (c) Microsoft Corporation. 2021年04月09日 15:18:41.61 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. 2021年04月09日 15:18:42.04 Logon Error: 18456, Severity: 14, State: 38. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]2021年04月09日 15:18:44.41
• SqlWorldWide: Now you know what is causing the log file to fill up. If you need help solving please open a new question. If my answer solved your current question please mark it as resolved so other readers can be benefited.