SQL database log full solution

The solution is to delete other files on the hard disk to empty the hard disk space, change the size of the Database Log File to unlimited, and then separate the database to ensure that the separation is successful, and then delete the Database Log File (it is best to change the name first, just in case ). Attach the database to recreate the log file. After adding it back, change the database to automatic

The solution is to delete other files on the hard disk to empty the hard disk space, change the size of the Database Log File to unlimited, and then separate the database to ensure that the separation is successful, and then delete the Database Log File (it is best to change the name first, just in case ). Attach the database to recreate the log file. After adding it back, change the database to automatic

The solution is to delete other files on the hard disk to empty the hard disk space, change the size of the Database Log File to unlimited, and then separate the database to ensure that the separation is successful ,, and then delete the Database Log File (it is best to change the name first, just in case ). Attach the database to recreate the log file.

After adding it back, change the database to auto-shrinking, and change the log file to mb-based growth. The maximum growth is set to a fixed value. The value size depends on the data processing status, if there is a big finger update operation, it should be larger.

Perform the following operations on the query Analyzer:

Clear logs

Dump transaction database name WITH NO_LOG

Transaction Log truncation:

Backup log database name WITH NO_LOG

Solve the problem that SQL database logs are full

1. Right-click Database → properties → options → fault recovery model → set to simple → OK;

2. Right-click Database → all tasks → contract database → OK;

3. Right-click Database → properties → options → fault recovery model → set as a large-capacity log record → OK.

2. Complex methods

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 -- contract database -- contract file

-- Select log file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete

-- Shrink Database

Dbcc shrinkdatabase (customer profile)

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

4. To minimize log files (for SQL 7.0, this step can only be performed in the query analyzer)

A. Detach a database

Enterprise Manager -- server -- database -- Right-click -- detach Database

B. Delete LOG files in my computer

C. Additional database

Enterprise Manager -- server -- database -- Right-click -- attach Database

This method generates a new LOG with a size of more than 500 K.

Or use the code:

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

A. Separation

EXEC sp_detach_db @ dbname = 'pubs'

B. Delete log files

C. Attach

EXEC sp_attach_single_file_db @ dbname = 'pubs', @ physname = 'C: Program

FilesMicrosoft SQL ServerMSSQLDatapubs. mdf'

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

Enterprise Manager -- server -- Right-click Database -- Property -- option -- 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)

-- SQL statement settings:

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 4th and 6 are not recommended. Steps 4th are not secure and may damage the database or cause data loss. If the number of logs reaches the upper limit in step 6th, database processing will fail in the future, logs can be restored after being cleared.

When the log file is full and the SQL database cannot write the file, you can use either of the following methods:

One method: Clear logs.

1. Open the query analyzer and enter the command

Dump transaction database name WITH NO_LOG

2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- Select log file -- select to 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.

Another method is risky because the log files of SQL SERVER are not immediately written to the master database file. improper processing may cause data loss.

1: Delete LOG

Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database

2: delete LOG files

Attach Database Enterprise Manager-> server-> database-> right-click-> attach Database

This method generates a new LOG with a size of more than 500 K.

Note: The first method is recommended.

If you do not want it to become larger later.

Use in SQL2000:

Right-click the database and choose Properties> Options> fault recovery> model> select simple model.

Or use an SQL statement:

Alter database name set recovery simple

In addition, Truncate log on checkpoint (this option is used for SQL7.0. in SQL 2000, the fault recovery model is selected as a simple model). When the CHECKPOINT command is executed, if the transaction log file exceeds 70% of its size

This option is often set to True Auto shrink when the development database regularly checks the database. When the unused space of the database file or log file exceeds 25% of its size, the system will automatically reduce the file so that the unused space is equal to 25%.

When the file size does not exceed the initial size when it is created, the file size after the file size is not reduced must be greater than or equal to the initial size of the file to reduce the transaction log file only when it is backed up or truncate

The log on checkpoint option is set to True.

Note: Generally, the default attribute of the database established is set, but the database attribute is changed in case of exceptions. please clear the log and check the preceding attributes of the database to prevent transaction logs from being full again.