MS SQL Server database cleanup error logging method _mssql

SQL error log records the database running process encountered a variety of problems and some important information, as a problem, we usually do not take the initiative to clean up these log files, only every time the server restart, SQL will automatically delete the oldest log files, and new into a log file.
By viewing the database log file on the server, there is a large number of query notification dialog information, and the frequency is very high, causing the log file to grow very quickly.

By using Google to understand that this error is related to the message mechanism of Service Broker, this information can be eliminated through the use of Trace tokens: DBCC traceon (4133,-1).
But now the urgent matter is how to clear out the log information, the easiest way is to go to the SQL log directory to delete these log files, but consider the need to stop the SQL Server service before the deletion, may cause the data loss in the cache, so this is not recommended practice.
So what should be the right thing to do?
Execute the following statement:
EXEC Sp_cycle_errorlog;
Each execution of SQL automatically initializes a log file, empties the contents of the log, and when SQL has 7 log files (the default), perform 7 of the operation, each time emptying the oldest of the log files.
Readers do not have to worry about the empty will take a long time, my side of the log has 40G, after the command execution, the file immediately emptied. In times of emergency, this approach is particularly handy.
So is there a way to set the fixed size of each log file?
Checked this information, some people said that can be set in the registry errorlogsizeinkb size, but only limited to SQL2012, other versions of the database settings do not take effect, this I have not verified, interested friends can discuss together.
Database No log error recovery
Cause, the client's SQL Server is 2000 version, because the log is too large unmanned management, there is no space, and then the customer split the database to delete the log (supposedly 200G log =.=), and then show the separation error, but after refreshing the database has been separated, delete the log, the database cannot be attached, After the online query, summed up the following methods, fortunately useful tables are not damaged, only statistical data damage, but it does not matter anyway the job will reset these tables.
--Ensure Enterprise Manager does not open any databases
--Set a database emergency
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go
--Set the database as emergency mode
Update sysdatabases set status=-32768 where dbid=db_id (' procurement ')
--Rebuilding database log files
DBCC REBUILD_LOG (' procurement ', ' D:\Procurement_log.ldf ')
--Verifying database consistency (can be omitted)
DBCC CHECKDB (' procurement ')
--Set the database to a normal state
sp_dboption ' procurement ', ' dbo use only ', ' false '
--In the final step, we want to restore the "allow direct modifications to the system directory" set in step E
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go
Now your database allows the connection, now you can check the data for each table is a problem, if there is a problem, can only find professional data back.