How to disable log for MSSQL server?

Created:

2017-02-05 17:43:47 UTC

Modified:

2017-08-08 13:08:48 UTC

0

Was this article helpful?


Have more questions?

Submit a request

How to disable log for MSSQL server?

Applicable to:

  • Plesk for Windows

Question

How to disable logs for MSSQL server?

Answer

  1. It is not possible to disable ERRORLOG and transaction logs for MSSQL server because they are required for the service to function properly. However, there are solutions how to manage number and size of log files:

    • to manage number of ERRORLOG files connect to the MSSQL server using SQL Management Studio. In Object Explorer expand Management tree and right click on SQL Server Logs and open Configure tab

    • to manage ERRORLOG file size. In SQL Server 2012 and later versions, you can use the following code to set the maximum size of individual error logs:

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

    , where 5120 is the size of ERRORLOG file in Kilobytes. For more information you can check the following technet article How to manage the SQL Server error log

    For MSSQL 2008 database server, create a SQL Server Agent job that executes at some point every day and runs the command

    EXEC sp cycle errorlog;

    GO

    This causes a new error log file to be created and will prevent the error log becoming overly large on systems that do not reboot for a long time.

  2. To disable creation of log_xx.trc files, use the following query:

    EXEC sp_configure 'show advanced options', 1;
    
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'default trace enabled', 0;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'show advanced options', 0;
    GO
    RECONFIGURE;
    GO

    To enable it back

    EXEC sp_configure 'show advanced options', 1;
    
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'default trace enabled', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'show advanced options', 0;
    GO
    RECONFIGURE;
    GO
Have more questions? Submit a request
Please sign in to leave a comment.