1. Open Query Analyzer, enter the command

DUMP TRANSACTION database name WITH NO_LOG

2. Then open Enterprise Manager - right-click on the database you want to compress - All Tasks - Shrink Database - Shrink Files - Select Log Files - Select the shrinking method in the shrinking mode shrinking to XXM, where it will be give a minimum allowable shrinkage to the number of M, directly enter this number, OK on it.

There are two ways to clear the Log:

1. Automatic Clearing Method

Open the database option Trunc Log on Chkpt, so that the database system automatically clears the Log every once in a while.The advantage of this method is that there is no need to intervene manually, it is performed automatically by the SQLServer, and there is no overflow of the Log in general; the disadvantage is that it only clears the Log without doing anything. The disadvantage is that it only clears the log without making a backup.

2. Manual clearing method

Execute the command "dump transaction" to clear the log. the following two commands can clear the log:

dump transaction with truncate_only

dump transaction with no_log

Usually the inactive portion of the transaction log can be deleted using the "dump transaction with trancate_only" command, which is written to the transaction log with the necessary concurrency checks. SYBASE provides the "dump transaction with no_log" command to deal with some very urgent cases, using this command is very dangerous, SQLServer will pop up a warning message. You should use it as a "last resort" in order to ensure database consistency as much as possible.

The above two methods only clear the logs, but do not backup the logs. If you want to backup the logs, you should execute the command "dump transaction database_name to dumpdevice".

PS: with a better method

first separate the database, directly after deleting the logs, and then in the query analyzer with

exec sp_attach_single_file_db 'database_name', '.mdf file path'

command to attach the database. OVER. seen elsewhere Nice.

Database logging operations

First provide a complex method of compressing logs and database files as follows:

1. Empty the log

DUMP TRANSACTION library name WITH NO_LOG

2. Truncate the transaction log:

BACKUP LOG database name WITH NO_LOG

3. Shrink the database file (if not compressed, the database file will not be reduced

Enterprise Manager - right-click on the database you want to compress - All Tasks - Shrink Database - Shrink the file

- select the log file - in the shrinkage mode select shrinkage to XXM, where it will be given a allowable shrinkage to the The minimum number of M is given here, enter this number directly and confirm

--Select the datafile --Select shrink to XXM in Shrink mode, a minimum number of M is given here, enter this number directly and confirm

This can also be done with SQL statements

--Shrink database

DBCC SHRINKDATABASE(customer data)

--shrink the specified data file, 1 is the file number, can be queried through this statement: select * from sysfiles

DBCC SHRINKFILE(1)

4. In order to maximize the shrinkage of the log file (if sql 7.0), this step can only be done in the query of the log file. , this step can only be done in Query Analyzer)

a. Separate the database:

Enterprise Manager - Server - Databases - Right click - Separate the database

b. Delete the LOG file in My Computer

c. Attach the database:

Enterprise Manager - Server - Databases - Right click - Attach the Database

This method will generate a new LOG, which is just over 500K in size

Alternative code:

The following example detaches pubs and then appends a file from pubs to the current server.

a. Detach

E X E C sp_detach_db @dbname = 'pubs'

b. Delete the log file

c. Attach again

E X E C sp_attach_single_file_db @dbname = 'pubs',

< p> @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

5. In order to auto-shrink in the future, do the following settings:

Enterprise Manager - Server - Right-click on the database - Properties - Options - Select " Autoshrink"

-- SQL statement settings:

E X E C sp_dboption 'database name', 'autoshrink', 'TRUE'

6. If you want to prevent it from logging too much growth in the future

Enterprise Manager - Server - Right click on the database - Properties - Transaction logging

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

--The way to set the SQL statement:

alter database database name modify file(name=logical filename,maxsize=20)

Special attention:

Please follow the steps, don't do the next steps without doing the previous ones

Otherwise you may damage your database.

It is generally not recommended to do steps 4 and 6

Step 4 is not safe and may damage the database or lose data

Step 6 if the log reaches the maximum limit, then future database processing will fail, and can only be recovered after clearing the log.

Another simpler method is provided and recommended.

Simpler method:

1. right build database properties window - Failure Reduction Model - set to Simple

2. right build database All Tasks - Shrink Database

3. right build database properties window - Failure Reduction Model - set to Large Volume Logging

Possibly have encountered such a problem:

update or delete statement forgot to bring where clause, or where clause precision is not enough, after the execution of the serious consequences,

This case of data recovery can only be carried out using the transaction log of the backup, so if your SQL does not have the appropriate full library backup

or can not backup log (truncate) log on checkpoint option is 1), then data recovery is not possible, or

can only be restored to the most recent backup.

When the log file is full and the SQL database is unable to write to the file, there are two methods available:

One method: empty the log.

1. Open the query analyzer, enter the command

DUMP TRANSACTION database name WITH NO_LOG

2. And then open the Enterprise Manager - right-click on the database that you want to compress - all the tasks - shrink the database - shrink the file - select the log file - in the shrinkage mode choose to shrink to XXM, the log file will be allowed to shrink to XXM. Here will be given a minimum allowable shrinkage to the number of M, directly enter this number, OK on it.

The other method has a certain risk, because the SQL SERVER log file is not instantly written to the main database file, such as improper handling, will cause data loss.

1: Delete LOG

Separate Database Enterprise Manager->Server->Database->Right-click->Separate Database

2: Delete LOG file

Attach Database Enterprise Manager->Server->Database->Right-click->Attach Database

This method generates a new LOG with a size of just over 500K.

Note: It is recommended to use the first method.