Quickly help you solve the problem of full SQL Server logs

This article provides a complex method to compress logs and database files.

1. Clear logs: dump transaction database name WITH NO_LOG.

2. truncate transaction logs: backup log database name WITH NO_LOG.

3. Compress database files (if not compressed, the database files will not be reduced:

Enterprise Manager-> right-click the database you want to compress-> all tasks-> shrink database-> shrink file-> Select log file-> select shrink to XXM In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.-> select a data file-select to contract to XXM in the contraction mode, here we will provide a minimum number of M that can be reduced. Enter this number directly to confirm the operation. You can also use an SQL statement to complete the operation.

-- Database shrinking: dbcc shrinkdatabase (customer profile)

-- Contract the specified data file. 1 is the file number. You can use this statement to query: select * from sysfiles dbcc shrinkfile (1 ).

4. To minimize the log file size (if SQL 7.0 is used, this step can only be performed in the query analyzer ).

A. Detach a database: Choose "Enterprise Manager"> "server"> "Database"> "right-click"> "detach a Database.

B. Delete the LOG file on my computer.

C. Attach a database: Enterprise Manager -- server -- database -- Right-click -- attach a database.

This method generates a new LOG with a size of more than 500 KB or code:

The following example separates pubs and attaches a file in pubs to the current server.

A. Separation: EXECsp_detach_db @ dbname = 'pubs '.

B. Delete log files

C. append:

EXECsp_attach_single_file_db @dbname = 'pubs',@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

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

Enterprise Manager-> server-> right-click Database-> properties-> options-> select "auto contract"-> SQL statement setting method:

EXEC sp_dboption 'database name', 'autowrite', 'true '.

6. If you want to prevent the log from increasing too much 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 statements:

Alter database name modify file (name = logical file name, maxsize = 20 ).

Note:

Follow these steps. Do not follow these steps. Otherwise, your database may be damaged.

Generally, steps 4 and 6 are not recommended. Step 4 is insecure, which may damage the database or cause data loss. Step 4 fails to process the database if the log reaches the upper limit, logs can be restored after being cleared.

It also provides a simpler method, which is recommended for use.

Simpler Method:

1. Right-built Database Properties window-fault recovery model-set to simple.

2. Right-click all database creation tasks to contract the database.

3. Right-built Database Properties window-fault recovery model-set as a large-capacity log record.