MSSQL-based transaction logs

View transaction logs: the tool is as follows:
Lumigent Log Explorer for SQL Server (commercial)
SQL log rescue --- red-gate (free)

Handle the overload of transaction logs:
/** // * Method 1: Separate and attach the database .*/
Declare @ dbname varchar (50)
Set @ dbname = 'db _ name'
-- Step 1: detach a database
Exec [sp_detach_db] @ dbname
-- Step 2: Rename the log physical file xxx_log.ldf.
-- Step 3: append a single file (XXX. MDF)
Exec [sp_attach_single_file_db] @ dbname, 'c: mssqldatadb_name.mdf'
-- Step 4: If the operation succeeds, delete the renamed file in step 2. If it fails, change it back to the original file name. Use sp_attach_db to attach the database to the server, and then use method 2.

/** // * Method 2: Clear and compress the database .*/
Declare @ dbname varchar (50)
Set @ dbname = 'db _ name'
-- Step 1: Clear logs
Dump transaction @ dbname with no_log
-- Step 2: truncate transaction logs:
Backup log @ dbname with no_log
-- Step 3: shrink the database
DBCC shrinkdatabase (@ dbname)
-- Step 4: contract the specified data file. 1 indicates the file number. You can use this statement to query: Select * From sysfiles.
DBCC shrinkfile (1)
-- Step 5: Due to log truncation, media fault protection cannot be provided. Please back up the database immediately.
Backup Database @ dbname to disk = n 'd: path *. Bak 'with noformat, noinit, name = n 'full Database Backup', Skip, norewind, nounload, stats = 10
Go
Transaction Log theory:
Transaction logs, or transaction logs, are very important but often ignored in the database structure. Since it is not as active as the schema in the database, few people are concerned about transaction logs.
Transaction logs are records of database changes. They can record any operations on the database and save the record results in an independent file. Transaction logs are fully recorded for every transaction process. Based on these records, data files can be restored to the pre-transaction status. Starting from the transaction action, the transaction log is recorded, and any operations on the database during the transaction are recorded until the user clicks submit or return. Each database has at least one transaction log and one data file.

For performance considerations, SQL Server saves user changes to the cache. These changes are immediately written into the transaction log, but are not immediately written into the data file. The transaction log uses a tag to determine whether a transaction has written cached data to a data file. After the SQL server restarts, it will view the latest mark points in the log and erase the transaction records behind the mark points, because these transaction records do not actually write data in the cache into the data file. This prevents the interrupted transactions from modifying data files.
　　

Maintain transaction logs

Because many people often forget the transaction log, it also brings some problems to the system. As the system continues to run, more and more logs will be recorded, and the size of log files will increase, resulting in insufficient available disk space at the end. Unless logs are frequently cleared during daily work, log files will eventually occupy all available space in the partition. The default log configuration is unlimited capacity. If you use this configuration, it will continue to expand and eventually occupy all the available space. Both cases will cause the database to stop working.

Daily transaction log backup can effectively prevent log files from consuming disk space too much. The backup process removes unnecessary parts of the log. The cut-off method is to first mark the old record as inactive, and then overwrite the new log to the location of the old log, so as to prevent the transaction log from expanding. If you cannot regularly back up logs, it is best to set the database to "simple recovery mode ". In this mode, the system will force the transaction log to automatically cut off each time the mark point is recorded to overwrite the old log with the new log.

The cut-off process occurs when the backup or marking the old tag as inactive, so that the old transaction record can be overwritten, but this does not reduce the disk space actually occupied by the transaction log. Even if you no longer use the log, it still occupies a certain amount of space. Therefore, transaction logs must be compressed during maintenance. Transaction logs can be compressed by deleting non-active records to reduce the physical hard disk space occupied by log files.

You can use the DBCC shrinkdatabase statement to compress the transaction log file of the current database. The DBCC shrinkfile statement is used to compress the specified transaction log file. In addition, you can activate automatic compression in the database. When logs are compressed, the old records are marked as inactive and deleted permanently. Depending on the compression method used, you may not see the result immediately. Under ideal conditions, the compression should be performed when the system is not very busy; otherwise, the database performance may be affected.

Restore database

Transaction Record backup can be used to restore the database to a specified state, but the transaction record backup itself is not enough to complete the task of restoring the database, and the backup data files need to be involved in the recovery. When restoring the data database, the data file is first restored. Do not set the data file to the finished state before it is restored. Otherwise, the transaction log will not be restored. When the data file is restored, the system restores the database to the desired state through the transaction log backup. If multiple log files are backed up after the last database backup, the backup program will restore them according to their creation time.

Another process called Log shipping can provide stronger database backup capabilities. After log shipping is configured, it can copy the entire database to another server. In this case, transaction logs are also regularly sent to the backup server for data recovery. This keeps the server in the Hot Backup state and updates the server when the data changes. Another server is called a monitoring server. It can be used to monitor shipping signals sent at specified intervals. If no signal is received within the specified time, the monitoring server records the event to the event log. This mechanism makes log shipping often a solution used in disaster recovery plans.

Performance Optimization

Transaction logs play an important role in the database and also affect the overall performance of the system. With several options, we can optimize the transaction log performance. Transaction logs are a continuous disk write process, so no read operation will occur. Therefore, storing log files on an independent disk can optimize performance.

Another optimization is related to the volume of log files. We can set the size of the log file to no more than a few percent of the hard disk space, or determine its size. If you waste disk space by setting it too much, and if it is set too small, it will force the record file to continue to try to expand, resulting in a decline in database performance.
　　
Transaction log file is a file used to record database updates with the extension LDF.
In SQL Server 7.0 and SQL Server 2000, if the automatic growth function is set, the transaction log file is automatically expanded.
Generally, when the maximum number of transactions between two transaction log truncation occurs, the transaction log size is stable. Transaction Log truncation is triggered by the checkpoint or transaction log backup.
However, in some cases, the transaction log may become very large, resulting in exhausted space or full. Generally, when the transaction log file occupies sufficient disk space and cannot be expanded, you will receive the following error message:
Error: 9002, severity: 17, state: 2
The log file for database '%. * ls' is full.
In addition to this error message, SQL Server may mark the database as suspect due to the lack of transaction log extended space. For more information about how to recover from this situation, see the "insufficient disk space" topic in SQL Server online help.
　　
In addition, transaction log extensions may cause the following situations:
· Very large transaction log files.
· Transactions may fail and may start to roll back.
· Transactions may take a long time to complete.
· Performance problems may occur.
· Blocking may occur.
　　
Cause
Transaction Log extensions may occur for the following reasons or situations:
· Uncommitted transactions
· Very large transactions
· Operation: DBCC dbreindex and create Index
· During restoration from transaction log backup
· Client applications do not process all results
· Query times out before the transaction log is extended. you receive a false "log full" error message.
· Unreplicated transactions

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/10/24/1841187.aspx