1

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.

3

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://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql?view=sql-server-ver15

2
  • 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. Apr 19 at 16:16
  • 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 Apr 19 at 16:26
3

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


 
4
  • 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. Apr 19 at 16:15
  • 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? Apr 20 at 18:56
  • 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 Apr 23 at 3:15
  • 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. Apr 23 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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