Microsoft SQL Server transaction log application

Source: http://www.study-code.com/database/sql-server/76961.htm

 

We know that the SQL Server transaction log is mainly used to record all the changes made to the database by all transactions. If the system fails, it will become the only source of the latest data. Log operations often have the following applications:

I. Loss of transaction log file LDF

When we delete the LDF file or the LDF file is lost, only the MDF file is left in the database. In this case, adding MDF directly cannot restore the database. How can we restore the database? We can divide SQL Server log files into two types: one is the log with no active transactions, and the other is the log with active transactions, we perform database recovery in two cases.

1. log recovery without active transactions

When there is no active log in the file, we can easily use the MDF file to directly restore the database. The specific operation is as follows:

(1) If the database does not have logs, it will be in a questionable state. First, right-click the corresponding database in the Enterprise Manager, then select "detach Database" under "all tasks" to separate the database;

(2) Use the MDF file to append the database to generate a new log file. In the Enterprise Manager, right-click the database and choose "Attach Database" under "all tasks" to append the database.

In this way, the database can be restored directly. If the log file of the database contains active transactions, the database cannot be recovered using this method. Therefore, you must use the following method.

2. Log Recovery with active transactions

When a transaction record is lost in the log, we use the following method:

(1) Create a database with the same name. For example, if the original database name is MYDB, stop the SQL Server, remove the MDF file of the primary data of the database, and restart the SQL Server, create a database named MYDB, stop the SQL Server, overwrite the removed MDF file, and restart the SQL Server. By default, the system table cannot be modified. You need to run the following statement. In the query analyzer, select the Master database and execute:

Sp_configure 'Allow updates', 1

Reconfigure With Override

Run the following statement to set the status attribute of the MYDB database in the Sysdatabases table to '2013' and set the MYDB database to the emergency mode.

Update sysdatabases set status = 32768 where name = 'mydb'

Then, set MYDB to single-user mode, restart the SQL Server, and set MYDB to single-user mode.

Sp_dboption 'mydb', 'single user', 'true'

Run the following statement to check the database MYDB

Dbcc checkdb ('mydb ')

(2) restore the database status

Run the following statement to restore the database status:

Update Sysdatabases Set status = 28 Where name = 'mydb'

Sp_Configure 'Allow updates', 0

Reconfigure With Override

At this time, the database still does not work. You must perform the following operations to restore the database.

(3) import and export the database MYDB to a new database MYDBNEW using the DTS Import and Export wizard, and then create a new database MYDBNEW, right-click MYDBNEW, select the "export data" function under "all tasks", open the import wizard, and import the table structure, data view, and stored procedure to MYDBNEW, use this function to replace the MYDBNEW database with the original MYDB database.

It can be seen that it is much more troublesome to restore a log with active transactions. Therefore, during database maintenance, do not underestimate the transaction log.

Ii. How to narrow down the log as the transaction grows

When the data is frequently modified or deleted, the transaction logs will continuously increase, or even exceed the size of the hitting disk, at this time, you cannot directly Delete the LDF file of the transaction log. Otherwise, it may cause a lot of trouble. To avoid this situation, we need to perform the following operations:

(1) try to avoid placing tempdb logs and user database logs on the same disk. The tempdb database and transaction logs have sufficient space to process index operations. Transaction logs of tempdb cannot be truncated before the index operation is completed.

(2) run the following command to narrow down transaction logs.

DBCC SHRINKDATABASE

DBCC SHRINKFILE

The Operation will immediately try to shrink the physical log file to the required size.

If the logical log in the virtual log file does not exceed the target_size tag, the virtual log file marked with target_size is released and the DBCC statement is successfully completed without any information displayed.

If the logical log in the virtual log exceeds the target_size mark, SQL server Database Engine releases as much space as possible and displays an informative message. This message indicates what operations must be performed to delete logical logs from virtual logs at the end of the file. After this operation is completed, you can re-issue the DBCC statement to release the remaining space.

The dbcc shrinkfile statement also displays an information message indicating that it cannot release all required space and tells you to execute the backup log statement to release the remaining space.

Iii. Restoration of transaction logs

When restoring transaction logs, you can select three recovery modes: simple mode, full mode, and large-capacity log mode.

Simple recovery mode

This mode records most transactions in a simple way, only to ensure Database Consistency after a system crash or data backup is restored.

Because the old transaction has been committed and no longer needs its logs, the logs will be truncated. Truncation logs will delete the backup and restore the original transaction logs. However, such simplification is costly, and data loss may occur in disaster events. Without log backup, the database can only restore to the latest data backup time. If you are using SQL server Enterprise Edition, consider this issue. In addition, this mode does not support restoring a single data page.

Full recovery mode

This mode completely records all transactions and retains all transaction log records until they are backed up. In SQL server Enterprise Edition, the full recovery mode restores the database to the fault time point.

Large-capacity log recovery mode

This mode briefly records most large-volume operations (for example, index creation) and completely records other transactions.

Large-capacity Log Recovery improves the performance of large-capacity operations and is often used as a supplement to the full recovery mode. Large-capacity log recovery mode supports all types of recovery, but there are some restrictions. When backing up logs that contain large-capacity log records, you need to access all data files in the database. If the data file is not accessible, the last transaction log cannot be backed up, and all submitted operations in the log will be lost.