Shulou(Shulou.com)05/31 Report--

This article will explain in detail about the SQL database log is full of solutions, the content of the article is of high quality, so the editor to share with you to do a reference, I hope you have a certain understanding of the relevant knowledge after reading this article.

The current solution is to delete other files on the hard disk to free up the hard disk space, change the log file size of the database to unlimited, and then detach the database to make sure the separation is successful. then delete the database log file (preferably rename first, just in case). Attach the database, which rebuilds the log file. After adding it back, change the database to automatically shrink, change the log files to grow according to mb, the maximum growth is set to a fixed value, the size of the value depends on the data processing, if there is a big finger update operation, it should be set to a little larger.

Operate on the query analyzer:

Clear log DUMP TRANSACTION library name WITH NO_LOG truncate transaction log: BACKUP LOG database name WITH NO_LOG solves the problem that SQL database log is full

1. Right-click the database → property → option → failure recovery model → set to simple → to determine

2. Right database → all tasks → shrink database → confirm

3. Right-click the database → property → option → failure recovery model → is set to mass logging → to determine.

Second, complex methods

1. Clear 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 you want to compress the database-all tasks-shrink database-shrink file-select log file-choose to shrink to XXM in the contraction mode, here will give a minimum number of M allowed to shrink, enter this number directly and determine it. -- Select the data file-- choose to shrink to XXM in the contraction mode. Here, you will give a minimum number of M allowed to shrink. Enter this number directly, and you can also use the SQL statement to complete-- shrink the database DBCC SHRINKDATABASE (customer data)-- shrink the specified data file. 1 is the file number. You can query it through this statement: select * from sysfiles DBCC SHRINKFILE (1)

4. In order to maximize the reduction of log files (in the case of sql 7. 0, this step can only be done in the query analyzer) a. Detach database enterprise manager-server-database-right-detach database b. Delete the LOG file c. Attach Database Enterprise Manager-Server-Database-right-attach Database this method generates a new LOG with a size of just over 500K or with code: the following example detaches the pubs and then attaches a file from the pubs to the current server. a. Separate EXEC sp_detach_db @ dbname = 'pubs' b. Delete the log file c. Add EXEC sp_attach_single_file_db @ dbname = 'pubs',@physname =' c:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data\ pubs.mdf'

5. In order to automatically shrink later, set Enterprise Manager-Server-right database-Properties-options-Select "Auto shrink"-SQL statement setting method: EXEC sp_dboption 'database name', 'autoshrink',' TRUE'

6. If you want to prevent its log from growing too much in the future, Enterprise Manager-- Server-- right-click database-- Properties-- transaction log-- limit file growth to xM (x is the maximum big data file size you allow)-- SQL statement setting method: alter database database name modify file (name= logical file name, maxsize=20) Note: please follow the steps, not the previous steps Please do not do the following steps. Otherwise, it 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 upper limit, the future database processing will fail and can only be recovered after cleaning the log.

When the log file is full and the SQL database cannot write to the file, you can use two methods:

One way: clear the log.

1. Open the query analyzer and enter the command DUMP TRANSACTION database name WITH NO_LOG

two。 Then open the enterprise manager-- right-click the database you want to compress-- all tasks-- shrink the database-- shrink the file-- select the log file-- choose to shrink to XXM in the shrink mode, and here will give a minimum number of M allowed to shrink. Enter this number directly and make sure.

Another method has a certain risk, because the log file of SQL SERVER is not immediately written to the main file of the database, if not handled properly, it will cause data loss.

1: delete LOG detach Database Enterprise Manager-> Server-> Database-> right-click-> detach Database

2: delete LOG file additional database enterprise manager-> server-> database-> right-click-> attach database this method generates a new LOG, the size is only 500K.

Note: the first method is recommended.

If you don't want it to get bigger in the future.

Use under SQL2000:

Right-click on the database-> Properties-> options-> Fault recovery-Model-Select-simple model.

Or use the SQL statement: alter database database name set recovery simple

In addition, Truncate log on checkpoint (this option is used in SQL7.0,SQL 2000, that is, the failure recovery model is selected as a simple model) clears the contents of the transaction log file if it exceeds 70% of its size when executing the CHECKPOINT command. When developing the database, this option is often set to True Auto shrink to check the database periodically when the unused space of the database file or log file exceeds 25% of its size The system will automatically shrink the file so that its unused space is equal to 25% when the file size does not exceed the initial size when it was established. The reduced file must also be greater than or equal to its initial size. The reduction of transaction log files can only be done when it is backed up or when the Truncate log on checkpoint option is set to True.

Note: generally, the default properties of the established database have been set, but the database properties have been changed due to unexpected circumstances. After emptying the log, please check the above properties of the database to prevent the transaction log from filling up again.

About the SQL database log is full of solutions to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.