NXLOG is holding the SQL server log files and SQL server recycle job is failing

Post a different question
4
responses

Hello
We have an issue with SQL server logs recycling.
Upon investigating we found as a possible root cause that a process NXLOG.exe is holding and preventing the recycle/deletion of the MSSQL log files.
Could you please advise on this issue.

AskedDecember 6, 2021 - 4:33pm

Answer (1)

Hi Phobyux,

Just to clarify, are the MSSQL log files being read by the Nxlog agent. if not, how did you confirm that Nxlog is a possible cause... is it possible to share more details?

AnsweredDecember 17, 2021 - 12:05pm

Comments (3)

  • Phobyux's picture

    Hi
    We determined that the NXLOG is holding log files with procexplorer from sysinternal suite from MS.

    The only process that use those log outside of SQL server was NXLOG and when we killed it we were able to recycle.

    December 20, 2021 - 1:09pm
  • Phobyux's picture

    Hello

    Here is the file:

    ## This is a sample configuration file. See the nxlog reference manual about the

    ## configuration options. It should be installed locally and is also available

    ## online at http://nxlog.org/docs/

    ## Please set the ROOT to the folder your nxlog was installed into,

    ## otherwise it will not start.

    #define ROOT C:\Program Files\nxlog

    define ROOT C:\Program Files (x86)\nxlog

    Moduledir %ROOT%\modules

    CacheDir %ROOT%\data

    Pidfile %ROOT%\data\nxlog.pid

    SpoolDir %ROOT%\data

    LogFile %ROOT%\data\nxlog.log

    <Extension charconv>

    Module xm_charconv

    AutodetectCharsets UTF-8, UCS-2LE

    </Extension>

    # Load the json extension

    <Extension json>

    Module xm_json

    </Extension>

    <Input sql-ERlogs>

    Module im_file

    File "D:\apps\databases\ssz22pf\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ER*"

    ReadFromLast False

    SavePos False

    Exec $FileName = file_name();

    Exec $Hostname = hostname_fqdn();

    Exec $raw_event = "NXLog|" + $Hostname + "|MS_SQL_SERVER_ERROR|" + $FileName + "|" + "000|000" + "|" + convert($raw_event, 'UCS-2LE','UTF-8');

    </Input>

    # Send the read log lines out to nxlog server

    <Output out-sqlERlogs>

    Module om_tcp

    Host 10.208.0.226

    Port 10013

    OutputType LineBased

    </Output>

    # Build the route from nxlog on Windows to nxlog on server

    <Route 1>

    Path sql-ERlogs => out-sqlERlogs

    </Route>

    January 19, 2022 - 2:25pm