SQL Server transaction log processing method

1. Use backup log database with no_log to clear logs

Changing the fault recovery model in database properties to "simple" can greatly slow down log growth.

If you adjust the Restoration Model to a simple one, it does not support time point restoration, but the log file will be small. If the data is important, we recommend that you change the database Restoration Model to full.

After the backup log database with no_log name is used, non-active logs are truncated, and the size of physical log files is not reduced, but logical logs are reduced, after the database is shrunk, the inactive virtual logs are deleted to release the space without damaging the data.

If the log is truncated and the database is shrunk, you cannot use the latest full-database backup for restoration at a time point. We recommend that you back up the database immediately, just in case.

2. Can I delete the transaction log file of the primary database while SQL server is running?
The steps are as follows: (1) detach the Database Enterprise Manager -- database -- Right-click the database where you want to delete logs -- all

Task-Detach Database
(2) Delete the log file.
(3) Attach the database
Enterprise Manager -- database -- Right-click Database -- all tasks -- attach database. MDF is enough.

3. Detailed methods for compressing SQL databases and logs

Basic tutorial on SQL Server 2000-after a database is compressed for a period of time, there may often be too much free space in the database due to data deletion, in this case, you need to reduce the disk space allocated to database files and transaction log files to avoid wasting disk space. When there is no data in the database, you can modify the database file attribute to directly change the occupied space, but when there is data in the database, this will damage the data in the database, therefore, we need to use compression to reduce database space. You can select the "auto shrink" option in the database attribute options to enable the system to automatically compress the database, or manually compress the database. There are two ways to manually compress a database:

1. Use Enterprise Manager to compress Databases

Right-click the database to be compressed in Enterprise Manager, and select the "shrink Database" option from "all tasks" in the shortcut menu, the dialog box shown in 6-10 is displayed. You can select the compression mode of the database in the dialog box shown in Figure 6-10, or click "Files" in Figure 6-10 by using the compression plan or compressing a single file, the compressed database file dialog box shown in 6-11 appears. You can perform different compression settings for each database file.

Click the "change" button in Figure 6-10. The compress plan editing dialog box shown in Figure 6-12 appears. You can specify the execution method of the compress plan. Click the "change" button in Figure 6-12. The "Edit cycle schedule" dialog box shown in Figure 6-13 appears. You can edit the plan execution cycle or time point. After setting, click "OK" to start compressing the database. After the compression is completed, a compressed information box is displayed.


2. Use the transact-SQL command to compress the database

You can use the DBCC shrinkdatabase and DBCC shrinkfile commands to compress the database. The DBCC shrinkdatabase command compresses the database, and the DBCC shrinkfile command compresses the specified files in the database.
(1) DBCC shrinkdatabase
The command syntax of DBCC shrinkdatabase is as follows:
DBCC shrinkdatabase (database_name [, target_percent]
[, {Notruncate | truncateonly}])
Parameters are described as follows:
· Target _ percent indicates the percentage of unused space in the database size after the database is compressed. If the specified percentage is too large and exceeds the proportion of space not used before compression, the database will not be compressed. In addition, the compressed database cannot be smaller than the initial capacity set by the database.
· Notruecate
After the database is reduced, the remaining space is retained in the database and will not be returned to the operating system.

. If this option is not selected, the remaining space is returned to the operating system.
· Truncateonly
After the database is reduced, the remaining space is returned to the operating system. When using this command, SQL Server reduces the file to the last file allocation, but does not move any data files. If this option is selected, the target_percent option is invalid. Example 6-14: compressing the database mytest's unused space is 20% of the database size

.
DBCC shrinkdatabase (mytest, 20)
The running result is as follows:
DBCC execution completed. If DBCC printed Error

Messages, contact your system administrator.
(2) DBCC shrinkfile
The DBCC shrinkfile command compresses files in the current database. The syntax is as follows:
DBCC shrinkfile ({file_name | file_id}
{[, Target_size] |
[, {Emptyfile | notruncate | truncateonly}]})
Parameters are described as follows:
· File _ id
ID of the file to be compressed)

. The file ID can be used through the file_id () function or as described earlier in this chapter

The sp_helpdb system stored procedure.

· Target _ size

Specifies the size of the compressed file. In MB. If this option is not specified, SQL Server will minimize the file size as much as possible.

· Emptyfile

Indicates that this file is no longer used and all data in this file will be moved to other files in the same file group. After the command with this parameter is executed, the file can be deleted using the alter database command. Other parameters notruncate, truncateonly, and DBCC shrinkdatabase

The command has the same meaning. Example 6-15: compress the database file mydb_data2 in the database mydb to 1 MB. Use mydb DBCC shrinkfile (mydb_data2, 1)

Methods In the Enterprise Manager:
1. Open the Enterprise Manager
2. Open the database to be processed
3. Click the top menu> Tools> SQL query analyzer to open the SQL query analyzer.
4. In the input window, enter:

Code:
Dump transaction [database name] With no_log
Backup log [database name] With no_log
DBCC shrinkdatabase ([database name])

Click the green triangle (or press F5) to perform the query, and wait until the status bar prompts that the processing is complete.

You can!

Methods In the program:
Compressing database logs
-- 1. Clear logs
Exec ('dump transaction ['+ @ dbname +'] With no_log ')
-- 2. truncate transaction logs:
Exec ('backup log ['+ @ dbname +'] With no_log ')
-- 3. compress the database file (if it is not compressed, the database file will not be reduced
Exec ('dbcc shrinkdatabase (['+ @ dbname +']) ')

4. Log reduction methods:

1. follow these steps:
1. Dump transaction upload name with no_log
2. DBCC shrinkfile (logfilename)
3. Collect data
4. Set automatic renewal.

2. Remove the database, delete the log file, and then attach it. OK! Right-click Database -- all tasks -- detach or attach

Iii. 1. backup log producer name with no_log, 2. DBCC shrinkfile (logfilename), 3. Collect shard data
4. Set automatic renewal.