Shulou(Shulou.com)06/01 Report--

Brief introduction to Log

The transaction log in SQL Server is undoubtedly one of the most important parts of SQL Server. Because SQL SERVER uses transaction logs to ensure persistence (Durability) and transaction rollback (Rollback). This also partially ensures the ACID attribute of the transaction. DBA can also recover data to a specified point in time through the transaction log in the event of a SQL Server crash. When SQL Server is working well, it is not that important to learn more about the principles and concepts of transaction logging. However, once the SQL SERVER crashes, it is very important to understand the principle and concept of transaction log for making the right decision to recover data quickly. This series of articles will talk about SQL Server transaction logs from the concept and principle of transaction logs, and how SQL Server uses logs to ensure persistence properties.

With the continuous growth of the database, the database logs (ldf files) continue to grow, and even the disk space is full, so the log files need to be shrunk if necessary.

Transaction log truncation

Routine backups are critical to avoid filling up the transaction logs of the database. Under the simple recovery model, the log is automatically truncated after the database is backed up, while in the full recovery model, the log is truncated only after the transaction log is backed up. However, delays can sometimes occur in the truncation process. For information about identifying and responding to various delay factors, see factors that may delay log truncation.

-- BACKUP log testDB with no_logUSE testDB; GO-- truncates the log and changes the database backup mode to simple. Alter DATABASE testDB SET RECOVERY SIMPLE;GO-- shrinks the log and adjusts the size to 1m DBCC SHRINKFILE test database log is the log logical name. DBCC SHRINKFILE (testDB_Log, 1); GO-- reply backup mode is complete. ALTER DATABASE testDB SET RECOVERY FULL; GO

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.