SQL Server error log shrink (ERRORLOG)

Source: Internet
Author: User
Tags microsoft sql server mssql mssqlserver numeric value server error log management studio sql server management sql server management studio


First, basic knowledge



By default, the error log is located at:



C:\Program Files\Microsoft SQL Server\mssql.1\mssql\log\errorlog



and ERRORLOG.N files. The default retention is 7 SQL Server error log files: errorlog,errorlog.1~errorlog.6, the current error log (file ErrorLog) does not have an extension. Whenever you start an instance of SQL Server, a new error log errorlog is created and the previous errorlog is renamed to Errorlog.1, before the Errorlog.1 is renamed Errorlog.2, and so on, the original errolog.6 is deleted.



Ii. contraction of errorlog documents



errorlog files on a production server can sometimes run into large files, especially when logging authentication is logged to the error log, which can be a problem when you use SQL Server Management Studio or a text editor to view the error log. In this case, you can generate a new log file through stored procedure Sp_cycle_errorlog without restarting the server, and loop the error log extension number as if you were restarting the service. In addition to Execute Sp_cycle_errorlog, you can use DBCC ERRORLOG to achieve the same functionality. In practice, you can also execute the stored procedure by establishing a job, which controls the size of the log file within a reasonable range.



Note: The data in the old ErrorLog file will be overwritten! If you must save the data in the old ErrorLog file, you can copy the old ErrorLog files to an external storage medium.



EXEC (' DBCC ErrorLog ') or exec sp_cycle_errorlog, or you can place the SP in the job on a regular basis by using the following command.



Third, errorlog other configuration



(i) SQL Server retains 7 error log files by default, and the oldest log is deleted while the new error log is generated, and if you want to keep more of the error log, you can set it in the following ways (SQL Server 2005):



Open SQL Server Management Studio



In the administration directory, right-click the SQL Server log and click Configure



In the Configure SQL Server error log window that pops up, tick the limit number of error log files before recycling check box, and set the maximum error log file count to the desired value. This value is between 6 and 99.






See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/





In addition to the above methods, you can modify the registry by modifying the form.



Create a new registry key (if it is modified):



Hkey_local_machine/software/microsoft/mssqlserver/mssqlserver/numerrorlogs, by default, does not have this registry entry, type selection REG_DWORD, The numeric value is set to the number of logs that you want to keep. The method of modifying a registry key can also be implemented through the following stored procedures:



EXEC xp_instance_regwrite n ' HKEY_LOCAL_MACHINE ', n ' software/microsoft/mssqlserver/mssqlserver ', n ' NumErrorLogs ', REG_DWORD, 20



(ii) By default, SQL Server error logs are located in: Program Files/microsoft SQL Server/mssql.n/mssql/log/errorlog and ERRORLOG.N files. You can modify the path by using the following methods:



In SQL Server Configuration Manager, click SQL Server Services.



In the right pane, right-click SQL Server (< instance name >), and then click Properties.



On the Advanced tab, in the Startup parameters box, you have a semicolon (;) The delimited parameter. In which, there is a parameter beginning with –e, modify the path after the parameter, you can deposit the error log under the specified path, the modification needs to restart the service to take effect.







(c) If you want to filter the error log file query, you can refer to: SQL Server error log filtering (ERRORLOG)



Author: Listen to the wind blowing rain



Source: http://gaizai.cnblogs.com/


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.