Resolving log growth resulting from the simple recovery model

Brief introduction

The recent test server for data archiving, during which the programmer found a problem, lack of space, I look for reasons to find the log file explosion. The database is then changed to a simple recovery model, but this problem still exists. After querying the data, it was found that the log file still increased in simple mode.

Simple concept

Simple recovery mode is also called "Checkpoint with Truncate log", in fact, the name is more image, in simple mode, SQL Server will automatically truncate log after each Checkpoint or backup, That is, discarding all idle log records, leaving only the small amount of log required for the instance recovery that occurs automatically when the instance starts, the advantage is that the log file is very small and does not require the DBA to maintain and back up log, but the downside is obvious, once the database has an exception, When recovery is required, you can recover up to the last backup and cannot revert to the last available state because log is missing.

Checkpoint

　　Like checkpoint and LazyWriter, the buffer viscera data is written to the disk while the log is truncated under the simple recovery model, and execution is triggered when the lazywriter cache is insufficient, and we will not discuss this for the time being.

For checkpoint I consulted Careyson later summed up the following several reasons for triggering its execution:

1. Some internal checkpoint, such as shutting down the database instance.
2. Database full or differential backups (log backups do not trigger checkpoint).
3. The database recovery model is more than 70% when log files are used in the simple recovery model.
4. The time interval threshold that checkpoint executes is more than enough log records.
5. Perform checkpoint manually.

Scenario Description:

Simple mode is mainly used for non-critical business, such as the development of libraries and test libraries, so this time due to the test environment of disk tension we have adopted a simpler mode. But what is the reason that data archiving still generates a lot of logs and increases disk usage? because we used a lot of insert and delete and update operations in the archive processing, so that a large number of logs were generated in a short time, this time the log increased rapidly, and because in SQL Server, checkpoint is a complete process, The time-consuming process depends on the size of the dirty data. Once in a short period of time, the log checkpoint is not completed when the log increased beyond the limit of the log. More logs will be generated.

As mentioned above, the problem arises because the checkpoint interval threshold is exceeded by enough log records to trigger checkpoint to write to the disk.

The following example is from:

http://sqlblog.com/blogs/linchi_shea/archive/2009/04/12/ A-race-condition-in-the-simple-recovery-mode-why-is-my-log-autogrowing.aspx

Let's use a script to actually illustrate this behavior. Start by running a script in a test database to create a test table and populate some data.

To test the database settings:

1. Set to Simple recovery mode.

2. The size of the log is 100M.

3. Automatic growth of log files is disabled (because it is easier to observe the log space being exhausted than to check for automatic growth).

--Create a table and initialize the data
CreateTableTest (iIntCChar(1000))
Go
Declare@iInt
Set@i=1
While@i<10000--Insert 9,999 test data
Begin
InsertTestvalues@i,  ' abc ")          
Set @i =< Span style= "color: #000000;" > @i +< Span style= "color: #000000;" > 1     &NBSP;&NBSP
end

Run the following script to observe the resource competition:

SetNocountOn Go
Declare@change_sizeInt
Set@change_size=100--Adjust this value as needed
Declare@iInt
Set@i=1
While@i<100
Begin
If@i%2=0
UpdateTestSetC=Replicate(‘A‘,@change_size)
Else
UpdateTestSetC=Replicate( ' b ,  @change_size )                        
Span style= "color: #0000ff;" >select @i  = @i + 1       
end

Repeatedly based on the changes @change_size to see the results, when I changed @change_size to 120 or even larger, got 9002 error message, very accurate to tell me that the database transaction log is full.

With this example of the above reference, it is good to reproduce the mechanism of the problem, so how do we deal with the situation?

Solve

Scenario 1:

Enforces checkpoint. However, there is a bad effect after execution, which seriously affects the execution time of the stored procedure. This is a very draining performance.

Scenario 2:

Shorten the checkpoint time interval threshold.

The default value is 0, which means that the reply interval is managed by SQL Server.

You can also implement this function with SQL statements:

Scenario 3:

Increase the log file size.

Summarize:

The log file is a double-edged sword, and the Wal mechanism is a good guarantee of consistency and maintenance of the data. But there is also an increase in the cost of additional performance and maintenance. We need to deal with these different situations according to the actual situation. It is important to note that the log is not generated in tempdb unless it is performed manually. In addition, logs are not generated after all time intervals, because checkpoint execution may not be triggered when there is little data.

Resolving log growth resulting from the simple recovery model