SQL database log Full resolution

The current solution is: Remove the other files on the hard disk, to empty the hard disk space, the database log file size to unrestricted, and then detach the database, to ensure that the separation is successful, and then delete the database log files (preferably renamed first, just in case). Attach the database again, which rebuilds the log file.

Added back, the database will be converted to automatic contraction, the log file into MB growth, increase the maximum set to a fixed value, the value of the size of the data processing, there are Da Cong update operation, should be set to a larger point.

To operate on the Query Analyzer:

Empty log

DUMP TRANSACTION Library name with NO_LOG

Truncate transaction log:

BACKUP LOG database name with NO_LOG

Troubleshooting SQL database Log full

1, right key database → attributes → options → failure reduction model → set to simple → determine;

2, right key database → All tasks → shrink database → OK;

3, right key database → attributes → options → failure to restore model → set to bulk log records → OK.

Ii. Complex methods

1, empty the log

DUMP TRANSACTION Library name with NO_LOG

2. Truncate 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--the database you want to compress--all tasks--shrink the database--Shrink the file

--Select Log file--in the contraction of the choice to shrink to XXM, here will give a permit to shrink to the minimum m number, directly enter this number, OK.

--Select the data file--in the contraction way to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, OK

You can also use SQL statements to complete

--Shrinking the database

DBCC shrinkdatabase (Customer information)

--Shrink the specified data file, 1 is the file number, you can query through this statement: SELECT * from Sysfiles DBCC shrinkfile (1)

4, in order to maximize the reduction of log files (if it is SQL 7.0, this step can only be in the Query Analyzer)

A. Detaching a database

Enterprise Manager--server--database--right key--detach database

B. Delete log files on my Computer

C. Additional databases

Enterprise Manager--server--database--right Key--additional database

This method will generate a new log with a size of more than 500 k

or in 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. Deleting a log file

C. Additional

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

FilesMicrosoft SQL Servermssqldatapubs.mdf '

5, in order to be able to automatically shrink, do the following settings

Enterprise Manager--server--right key database--Properties--Options--select "Auto Shrink"

--sql Statement setting:

EXEC sp_dboption ' database name ', ' autoshrink ', ' TRUE '

6, if you want to not let it grow too big log

Enterprise Manager--server--right key database--attribute--transaction log

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

How the--sql statement is set:

ALTER DATABASE name modify file (name= logical filename, maxsize=20)

Special attention:

Follow the steps, do not follow the steps, or you may damage your database.

Generally do not recommend to do 4th, 62, 4th step unsafe, it is possible to damage the database or loss of data, 6th step if the log reached the upper limit, then the database processing will fail, after the log can be recovered.

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

One way: Empty the log.

1. Open Query Analyzer, enter command

DUMP TRANSACTION database name with NO_LOG

2. Then open Enterprise Manager-right you want to compress the database-all tasks-shrink the database-shrink the file-Select the log file-in the shrink option to shrink to XXM, this gives you a minimum m number that you want to shrink to, just enter the number and make sure.

Another approach has a certain risk, because SQL Server log files are not immediately written to the database master file, such as improper handling, can result in loss of data.

1: Delete Log

Detach Database Enterprise Manager-> Server-> database-> right key-> Detach database

2: Delete log file

Additional database Enterprise Manager-> Server-> database-> right key-> attached database

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

Note: The first method is recommended.

If you don't want it to get bigger later.

Use under SQL2000:

Right-click on the Database-> properties-> option-> failure recovery-model-select-Simple model.

or with SQL statements:

ALTER DATABASE name set recovery simple

In addition, Truncate log on checkpoint (this option is used in Sql7.0,sql 2000, the recovery model is selected as a simple model) when the checkpoint command is executed if the transaction log file exceeds its size of 70%

Then clear its contents often set this option to true auto shrink periodically check the database when the unused space of the database file or log file exceeds 25% of its size, the system automatically shrinks the file so that its unused space equals 25%

When the file size does not exceed its initial size when it is established, the file reduction file must also be greater than or equal to its initial size to reduce the transaction log file only if you are backing it up or truncate

The Log on Checkpoint option is set to true.

Note: Generally established database default properties have been set, but encountered unexpected circumstances so that the database properties have been changed, please empty the log, check the database above properties, in case the transaction log again full.