Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the errors encountered in SQL Server full backup. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

1. Error details

Once encountered the following error while performing a full backup of the database manually:

This error message has been executed many times.

The prompt cannot generate a checkpoint, which may be due to insufficient system resources, such as disk or memory space, or sometimes due to database corruption.

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

two。 Knowledge related to checkpoint

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

In SQL Server, when insert, update and delete are carried out, the data is not directly written to the corresponding mdf file in the database, but is written to the cache. At this time, it is necessary to mention a very important mechanism: CheckPoint, its main function is to write the data in the cache to the mdf file.

This can be illustrated by the following classic picture:

3. Events that trigger CheckPoint checkpoints

1. Before a database backup, the database engine automatically performs a checkpoint to include all changes to the database page in the backup.

two。 The active portion of the log exceeds the amount of time that the server can recover within the time specified in the recovery interval server configuration options.

3. 70% of the log is 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. Perform checkpoints manually

So this error is likely to be a checkpoint problem during the backup, so we perform a checkpoint manually.

In general, we rarely need to execute the checkpoint instruction manually. The syntax for checkpoint is: check point [checkpoint _ duration], and checkpoint_duration is the number of seconds it takes to complete the checkppoint. Normally, instead of specifying this value of checkpoint_duration, we use the checkpoint duration automatically adjusted by the database to reduce the performance impact on the database. Because when the database executes checkpoint, the number of dirty pages, the active transaction that modifies the data, and the specified actual duration checkpoint_duration, all affect the allocation of resources. Assuming that the value of checkpoint_duration is specified as 50s, but normally it takes 150s to complete this operation, then at this time, in order to meet the specified checkpoint_duration 50s, the database will allocate more resources to the instruction to run than normal. Then it will affect the use of resources by other operations under normal circumstances. 5. Effect verification

Run checkpoint for a while.

Hee hee

This is the end of this article on "how to solve the errors encountered in SQL Server full backup". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.