Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to clean up the error log in the SQL Server database. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The SQL error log records various problems and some important information encountered during the operation of the database. As needed for troubleshooting, we usually do not take the initiative to clean up these log files. Only every time we restart the server, SQL will automatically delete the oldest log file and generate a new log file. By looking at the log file of the database on the server, it is found that there is a lot of query notification dialog information, and the frequency is very high, resulting in the log file growing very fast. Learn about the relationship between this error and the message mechanism of service broker through google, which can be eliminated by using the trace tag: DBCC TraceOn (4133). However, the urgent task now is how to clear the log information. The easiest way is to delete these log files in the log directory of SQL. However, considering that the SQL Server service needs to be stopped before deletion, which may result in the loss of data in the cache, this is not recommended. So what should be the right thing to do? Execute the following statement: each time EXEC sp_cycle_errorlog; executes SQL, it automatically initializes a log file to empty the contents of the log. When SQL has 7 log files (default), perform this operation 7 times, each time the oldest log file will be emptied. Readers don't have to worry that emptying will take a long time. I have a 40G log on my side, and the file was emptied immediately after the command was executed. This method is especially convenient in times of emergency. Is there any way to set the fixed size of each log file? Checked this information, some people say that you can set the size of ErrorLogSizeInKb in the registry, but it is limited to SQL2012, other versions of the database setting does not take effect, this I have not verified, interested friends can discuss together. Database no log error recovery causes, the customer's SqlServer is 2000, due to the log is too large unmanaged, there is no space, and then the customer separates the database to delete the log (it is said that 200G log =. =), and then shows the separation error, but the database has been separated after refreshing, after deleting the log, the database can not be attached, after online query, summed up the following methods, fortunately, the useful tables are not damaged Only statistical tables are corrupted, but it doesn't matter if the job resets them anyway.-- make sure Enterprise Manager doesn't have any databases open-- set database emergency use mastergosp_configure 'allow updates',1go reconfigure with overridego-- to emergency mode update sysdatabases set status=-32768 where dbid=DB_ID (' Procurement')-- rebuild database log file dbcc rebuild_log ('Procurement'). 'D:\ Procurement_log.ldf')-- verify database consistency (omitted) dbcc checkdb (' Procurement')-- Last step of setting the database to normal state sp_dboption 'Procurement','dbo use only','false'-- We need to restore sp_configure 'allow updates',0go reconfigure with overridego to the item "allow direct modification of the system directory" set in step E, now your database is allowed to connect, now you can check whether there is a problem with the data in each table, if there is a problem, you can only find a professional data reply.

After reading the above, do you have any further understanding of how to clean up the error log in the SQL Server database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.