-It is best to back up the log, and you can recover the data through the log later. . .

The following is the log processing method

Steps 4 and 6 are generally not recommended.

Step 4 is unsafe, which may damage the database or lose data.

Step 6 If the log reaches the upper limit, the database processing will fail in the future, and it can only be resumed after the log is cleared.

- */

-All the library names below refer to the library names of the database you want to process.

1. Clear the log

Dump the transaction library name with NO_LOG

2. Truncate the transaction log:

Backup log library name with NO_LOG.

3. Shrink the database file (if it is not compressed, the database file will not shrink.

Enterprise Manager-Right-click the database to be compressed-All tasks-Shrink database-Shrink files.

-Select log file-Select shrink to XXM in shrink mode, and the minimum number of m allowed to shrink will be given here. Enter the number directly and confirm.

-Select data file-Select shrink to XXM in shrink mode, and the minimum number of m allowed to shrink will be given here. Enter the number directly and confirm.

It can also be done with SQL statements.

-Shrinking the database

DBCC psychological database (library name)

-Shrink the specified data file, 1 is the file number, which can be queried with this statement: select * from sysfiles.

DBCC psychological file (1)

4. In order to minimize the log file (if it is sql 7.0, this step can only be completed in the query analyzer).

A. separating the database:

Enterprise Manager-Server-Database-Right-Click-Detach Database

Delete the log files in my computer.

C. other databases:

Enterprise Manager-Server-Database-Right-Click-Attach Database

This method will generate a new log with a size of only over 500 K.

Or use the code:

The following example detaches pubs and then attaches files from pubs to the current server.

A. Separation

EXEC sp_detach_db @dbname =' library name'

B. Delete the log file

C. Reattachment

Execsp _ attach _ single _ file _ db @ dbname =' library name',

@ phys name = ' c:\ program files \ Microsoft SQL server \ MSSQL \ data \ library name . MDF '

5. In order to shrink automatically in the future, make the following settings:

Enterprise Manager-Server-Right-click Database-Properties-Options-Select Auto Shrink.

-SQL statement setting method:

EXEC sp_dboption' library name',' automatic shrinking',' TRUE'

6. If you want to prevent it from growing too big in the future.

Enterprise Manager-Server-Right-click Database-Properties-Transaction Log

-Limit file growth to xM(x is the maximum data file size you allow)

-How to set-SQL statement:

Modify the file (name= logical file name, maxsize=20).

-Other answers (5 points)-

SQL code

Clear the log with BACKUP LOG database WITH NO_LOG.

Changing the recovery mode in the database properties to "Simple" will greatly reduce the growth rate of logs.

If the recovery model is set to simple, it will not support point-in-time recovery, but the log file will be very small. If the data is more important, it is recommended to set the recovery mode of the database to complete.

After naming the backup log database with NO_LOG, the inactive log will be truncated, and the size of the physical log file will not be reduced, but the logical log will be reduced. After shrinking the database, inactive virtual logs will be deleted to free up space without damaging the data.

If the log is truncated and the database is shrunk, you cannot directly use the latest full database backup for point-in-time restore. It is recommended to back up the database immediately, just in case.

2. Can I delete the transaction log file of the main database while sql server is running?

The steps are as follows: (1). Detach Database Enterprise Manager-Databases-Right-click the database whose log you want to delete-All.

Task-Detach Database

(2), and then delete the log file

(3), and then attach the database

Enterprise Manager-Databases-Right-click Databases-All Tasks-Attach Databases Only attach at this time. Mdf will do.

3. Detailed methods of compressing SQL database and log.

SQL Server 2000 Basic Course-Compressed Database After a period of use, there is often too much free space in the database because of data deletion. At this time, it is necessary 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 directly change the space occupied by the database file by modifying its attributes, but when there is data in the database, it will destroy the data in the database, so you need to use compression to reduce the database space. You can select the "Auto Shrink" option in the database property options, so that the system can automatically compress the database or manually compress it. There are two ways to manually compact the database:

1. Compress the database with enterprise manager.

Right-click the database to be compressed in the enterprise manager, and select "Shrink Database" option in "All Tasks" in the shortcut menu to open the dialog box as shown in Figure 6- 10. You can choose a database compression method in the dialog box shown in Figure 6- 10, or you can choose to use a compression plan or compress a single file. Click the File button in Figure 6- 10 to open the database file compression dialog box shown in Figure 6- 1 1, and you can set different compression settings for each database file.

Click the "Change" button in Figure 6- 10 to open the compression plan editing dialog box, as shown in Figure 6- 12, where you can specify the execution method of the compression plan. Click the "Change" button in Figure 6- 12 to open the periodical work plan editing dialog box as shown in Figure 6- 13, where you can edit the period or time point of plan execution. After setting, click "OK" to start compressing the database. After compression, a compression information box will be displayed.

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

You can use the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands to compact the database. The DBCC SHRINKDATABASE command compresses the database, and the DBCC SHRINKFILE command compresses the files specified in the database.

(1) DBCC psychological database

The syntax of the DBCC SHRINKDATABASE command is as follows:

DBCC psychological database (database name [,target percentage]

[，{NOTRUNCATE | TRUNCATEONLY}])

The parameters are described as follows:

Target_percent specifies the percentage of unused database size after database compression. If the specified percentage is too large to exceed the proportion of unused space before compression, the database will not be compressed. And the compressed database cannot be smaller than the capacity originally set by the database.

no regret

The remaining space after database reduction remains in the database and will not be returned to the operating system.

. If this option is not selected, the remaining space will be returned to the operating system.

Truncation only

Return the remaining space of the reduced database to the operating system. When using this command, SQL Server will shrink the file to the last file allocation area, but will not move any data files. When this option is selected, the target_percent option is invalid. Example 6- 14: The unused space of compressed database mytest is 20% of the database size.

.

dbcc shrinkdatabase (mytest，20)

The running results are as follows:

The execution of DBCC is finished. If DBCC is printed incorrectly,

Message, please contact your system administrator.

(2) DBCC psychological files

The DBCC SHRINKFILE command compresses files in the current database. Its syntax is as follows:

DBCC psychological file ({file name | file id}

{ [，target_size] |

[，{ empty file | NOTRUNCATE | truncate only }]})

The parameters are described as follows:

fileid

Specifies the identification number (ID) of the file to be compressed.

. The ID number of the file can be passed through the FILE_ID () function, or as mentioned earlier in this chapter.

Sp_helpdb system stored procedure.

Target size

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

null file

Indicates that the file is no longer used, and all data in the file will be moved to other files in the same filegroup. After executing the command with this parameter, you can use the ALTER DATABASE command to delete the file. Other parameters do not run and truncate ONLY and DBCC shrink databases.

The meaning in the order is the same. Example 6- 15: compress the size of database file mydb_data2 in database mydb to 1MB. Use mydb dbcc shrinkfile (mydb_data2, 1).

Methods in 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 with NO_LOG [database name]

DBCC psychological database ([database name])

Click the small green triangle (or press F5) to execute the query, and wait for the prompt in the status bar to finish processing.

Just!

Methods in the program:

Compress database log

-1. Clear the log

exec(' DUMP TRANSACTION['+@ dbname+']WITH NO _ LOG ')

-2. Truncate the transaction log:

exec(' BACKUP LOG['+@ dbname+']WITH NO _ LOG ')

-3. Shrink the database file (if it is not compressed, the database file will not shrink.

Exec('DBCC psychological database (['+@ database name+'])')

4, reduce the log method:

First, we follow the following steps:

1, dump the transaction library name with no_log.

2. dbcc shrinkfile (log file name)

Step 3 shrink the database

4. Set automatic shrinkage.

Second, separate the database, delete the log file, and then attach, OK! Right-click Database-All Tasks-Detach or Attach

Third, 1, the name of the backup log is no_log, 2, dbcc shrinkfile(logfilename), 3, and the database is shrunk.

4. Set automatic shrinkage.