An Uncommon Error and Solution for SQL Server Full Backup

1. Error details

Once when manually performing a full database backup, I encountered the following error:

This error message appears when executing multiple times.

A checkpoint cannot be generated because of insufficient system resources (such as disk or memory space) or sometimes because of database corruption.

We check the database resources to rule out insufficient disk resources.

2. Checkpoint related knowledge

The relationship between transaction logs, data files, and checkpoints.

In SQL Server, when inserting, updating, or deleting, the data is not directly written into the mdf file corresponding to the database, but is written into the cache. At this time, we need to mention a very important mechanism: CheckPoint, which is mainly used to write the data in the cache into the mdf file.

This can be illustrated by the following classic diagram:

3. Events that trigger CheckPoint

1. Before a database is backed up, the database engine automatically performs a checkpoint so that all changes to the database pages are included in the backup.

2. The active portion of the log exceeds the size that the server can recover within the time specified in the recovery interval server configuration option.

3. The log is 70% full and the database is in log truncation mode.

4. Stopping the server also issues a checkpoint command in each database on the server.

4. Manually perform checkpoints

Therefore, this error is most likely caused by a checkpoint problem during backup, so we manually perform a checkpoint.

Normally, we rarely need to execute the checkpoint command manually. The syntax of checkpoint is: CHECKPOINT[checkpoint_duration], where checkpoint_duration is the number of seconds required to complete the checkpoint. Normally, we do not specify the checkpoint_duration value, but use the checkpoint duration automatically adjusted by the database to reduce the performance impact on the database. When the database executes a checkpoint, the number of dirty pages, active transactions that modify data, and the specified actual duration checkpoint_duration will affect the allocation of resources. Suppose the checkpoint_duration value is 50s, and it normally takes 150s to complete this operation. In order to meet the specified checkpoint_duration of 50s, the database will allocate more resources to the instruction than normal, which will affect the resource utilization of other operations under normal circumstances. 5. Effect verification

Run the checkpoint.

whee

Summarize

The above is an uncommon error and solution encountered in SQL Server full backup introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time!