MicrosoftSQLServer application transaction log

As we know, SQLServer transaction logs are mainly used to record the changes made by all transactions to the database. If the system fails, it will become the only source of the latest data. Log operations often have the following applications: 1. Loss of transaction log files LDF when we delete or LDF files are lost, only MDF files are left in the database.

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: 1. Loss of transaction log files LDF when we delete or LDF files are lost, only MDF files are left in the database.

We know that SQL ServerTransactionsLogs are mainly used to record allTransactionsChanges made to the database, if the system fails, will become the only source of the latest data. Log operations usually include the following:Application:

I,TransactionsLog File LDF loss

When we delete or LDF files are lost, only the MDF file is left in the database. In this case, the database cannot be recovered by attaching MDF directly. How can we restore the database? We can divide SQL Server log files into two forms: one is no activityTransactionsIs active.TransactionsThe database is restored based on two conditions.

1. No activityTransactionsLog Recovery

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 recovered directly, and if the log file of the database contains ActivityTransactionsYou cannot restore the database by using this method. Therefore, you must use the following method.

2. ActivitiesTransactionsLog Recovery

When a log occursTransactionsWhen the record is lost, 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.

You can know that you can resume an activity.TransactionsThe log is much more troublesome, so do not underestimate it during database maintenance.TransactionsLogs.

II,TransactionsHow to narrow down logs as they increase

When data is frequently modified or deleted,TransactionsLogs will be constantly increased, or even exceed the size of the hitting disk. In this case, you cannot directly Delete the logs.TransactionsLog LDF file, 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.TransactionsLogs have enough space to process index operations. Tempdb cannot be truncated before the index operation is complete.TransactionsLogs.

(2) zoom in by executing the following commandsTransactionsLogs

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,TransactionsLog Restoration

TransactionsYou can select three recovery modes when restoring logs: simple mode, full mode, and large-capacity log mode.

Simple recovery mode

This mode briefly records the majorityTransactionsTo ensure Database Consistency after system crash or Data Backup recovery.

Because of the oldTransactionsThe submitted log is no longer needed, so the log will be truncated. Truncation logs will delete backups and restoresTransactionsLogs. 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 allTransactionsAnd keep allTransactionsLog 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-capacity operations (for example, index creation) and completely records otherTransactions.

Large-capacity Log Recovery improves the performance of large-capacity operations and is often used as a supplement to the full recovery mode. The 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 lastTransactionsLogs, and all submitted operations in this log will be lost

Author: