Method for clearing error log of MS SQL Server database


The SQL error log records various problems encountered during the operation of the database and some important information. As a need for troubleshooting, we usually do not take the initiative to clean up these log files. Only when the server is restarted, SQL will automatically delete the oldest log file and generate a new log file.
By viewing the log files of the database on the server, it is found that there are a lot of query notification dialog information, and the frequency is very high, resulting in the rapid increase of log files.
Method for clearing error log of MS SQL Server database
Google understands the relationship between this error and the message mechanism of service broker. This information can be eliminated by using the tracking flag: DBCC traceon (4133, – 1).
However, the top priority now is how to clear these log information. The simplest way is to delete these log files from the SQL log directory. However, considering that the SQL Server service needs to be stopped before deletion, which may lead to the loss of data in the cache, this is not the recommended practice.
So what should be the right approach?
Execute the following statement:
EXEC sp_cycle_errorlog;
Each time you execute SQL, you will automatically initialize a log file and clear the contents of the log. When SQL has 7 log files (default), please perform this operation 7 times and clear the oldest one each time.
Readers don’t have to worry that emptying will take a long time. I have a log with 40g. After the command is executed, the file is emptied immediately. This method is particularly convenient in case of time emergency.
Is there any way to set the fixed size of each log file?
After checking this information, some people said that the size of errorlogsizeinkb can be set in the registry, but it is limited to sql2012. Other versions of the database will not take effect after setting. I haven’t verified this. Interested friends can discuss it together.
Database recovery without log error
The reason is that the customer’s sqlserver is version 2000. Because the log is too large and unmanaged, there is no space. Then the customer separates the database and wants to delete the log (it is said that the 200g log =. =), and then displays the separation error, but the database has been separated after refreshing. After deleting the log, the database cannot be attached. After online query, the following methods are summarized. Fortunately, the useful tables are not damaged, only the statistical table data are damaged, But it doesn’t matter. The homework will reset these tables anyway
–Make sure that the enterprise manager does not have any databases open
–Set database emergency
use master
sp_configure ‘allow updates’,1
reconfigure with override
–Set database 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 (can be omitted)
dbcc checkdb(‘Procurement’)
–Set database to normal state
sp_dboption ‘Procurement’,’dbo use only’,’false’
–Finally, we need to restore the item “allow direct modification of system directory” set in step E
sp_configure ‘allow updates’,0
reconfigure with override
Now your database is allowed to connect. Now you can check whether there is a problem with the data of each table. If there is a problem, you can only find a professional data reply.