Database error Log Trouble _ database

Click to have a surprise

The day before yesterday received a colleague phone: SQL Server full disk space caused the database inaccessible. Remote to the server, found that the original SQL error log files caused the trouble, the database in 1 seconds to produce 100M log, not how long will disk space blocked.

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.

Click to have a surprise