SQL Server transaction log Introduction

Databases in SQL Server are composed of one or more data files and one or more transaction log files.

Data Files mainly store database data, including the database content structure, data pages, and index pages. So what is transaction log? It is mainly used to save database modification records, such:

Why does SQL Server work like this? Why not write data into the data file immediately? The reason is simple: to achieve higher efficiency and performance. Data files may be extended to adapt to new data, and pages may be re-allocated and new space allocated. Logs are continuously recorded, so it is much faster to record transaction logs. This is why we recommend dividing the physical disk into a single partition to store transaction logs. This allows the disk to write the Maximum program continuously. The reading and writing of data files is very random.

So what exactly are transaction logs stored? Let's take a look at this very simple example:

In transaction logs, data changes are recorded in a continuous log record, and each record has a number called Log sequence number (LSN ).

In transaction logs, each log record is stored in a virtual log file. Transaction logs can contain any number of virtual log files. The number of virtual log files depends on the database engine, and the size of each virtual log file is not fixed.

As shown in, active portion logs are the regions that contain transactions. This interval is required to completely restore the database. When more transactions are created, logs between the active regions also grow.

So what changes will happen when the checkpoint is executed? The answer is: write all the changed data to the data file and create a checkpoint record ).

Now. Changes caused by transactions 1, 2, and 3 will be written to the data file. Because transaction 3 is not committed, the range of logs in the active zone is changed from lsn50 to lsn52. If you use a simple recovery model, the areas between lsn45 and lsn49 can be reused because those records are no longer needed.

When SQL server uses virtual log files 1 and 2 as reusable regions, transaction logs are truncated accordingly (truncate ). Note that the physical log size also changes. If the database runs in a full or batch log recovery model, the region from lsn45 to 49 will be deleted until the transaction log is backed up, the space in this region will be reused.

What happens when an updated transaction is created? In simple mode, the start space of the log will be reused.

In the full or batch log recovery model, the space of transaction logs is expanded.

If the transaction log is a fixed-size log, you will receive the following error message in the SQL Server2000 system:

Server: MSG 9002, Level 17, state 6, line 1
The log file for database 'adventureworks' is full. Back up the transaction log for the database to free up some log space.

In SQL Server 2005, the error is displayed:

MSG 9002, Level 17, state 4, line 1
The transaction log for database 'adventureworks' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in SYS. Databases

Note: This does not mean that the database running in simple recovery mode will never encounter a 9002 error. If you have a long, running, and uncommitted transaction, your transaction logs will still be filled up, because SQL Server cannot delete any log created after it has started running. That is to say, the logs in the active zone are recorded from the beginning of the transaction, and no active zone can be deleted or reused.

Therefore, you need to maintain a manageable range of transaction logs:

• When the change has been confirmed or the rollback due to an error has been completed, you need to submit your transaction immediately.
• If the database is running in a full or batch log recovery model, you must regularly back up your transaction logs.

To find the most initial active transaction in the database, especially the start time of the transaction, you can use the DBCC opentran command, for example:

DBCC opentran

Result:

Transaction information for database 'adventureworks '.
Oldest active transaction:
Spid (server process ID): 52
UID (User ID): 1
Name: user_transaction
LSN: (754: 531: 1)
Start Time: Jul 14 2008 5: 43: 55: 390pm

To find the size of the log space used by each database, run the DBCC sqlperf command:

DBCC sqlperf (logspace)

To find the number of virtual logs used by transaction logs, you can use the DBCC loginfo command. The details displayed are the content of the database you are currently connected to. below is the output of the adventureworks database:

We can get the following information: Your transaction log contains four virtual log files (one row), and all virtual log files are included in a single physical file (fileid = 2 ). The first, second, and third virtual log files are 458752 bits, and the last virtual log file is 712704 bits. 1 ~ 3. The virtual file has never been used or reused (status = 0), and the fourth virtual log file is in use (status = 2 ). The physical layout of the virtual log file has a connection number (fseqno is incremental). The actual situation may be different from this.

 

This article translated from sqlbackuprestore, more exciting content please visit http://www.sqlbackuprestore.com