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

This article is to share with you about the SQLServer database log is full of how to solve, the editor thinks it is very practical, so share it with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

How to solve the problem that the SQLServer database log is full?

1. Simple method

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 it is not compressed, the database file will not be reduced) 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. Here will give a minimum number of M allowed to shrink, and enter this number directly. OK-select data file-choose XXM in the contraction mode, here will give a minimum number of M allowed to shrink, enter this number directly, you can also use the SQL statement to complete-shrink the database DBCCSHRINKDATABASE (customer information)-shrink the specified data file, 1 is the file number, you can query through this statement: select*fromsysfilesDBCCSHRINKFILE (1).

4. In order to maximize the reduction of the log file (if it is sql7.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. Detach EXECsp_detach_db@dbname='pubs'b. Delete the log file c. Attach EXECsp_attach_single_file_db@dbname='pubs',@physname='c:\ ProgramFiles\ MicrosoftSQLServer\ MSSQL\ Data\ pubs.mdf'.

5. In order to automatically shrink in the future, set the enterprise manager-server-right database-properties-options-select "auto shrink"-SQL statement setting method: EXECsp_dboption' database name', 'autoshrink','TRUE'.

6. If you want to prevent its log from growing too big in the future, Enterprise Manager-server-right database-attributes-transaction log-limits file growth to xM (x is the maximum big data file size you allow)-SQL statement setting way: alterdatabase database name modifyfile (name= logical file name, maxsize=20).

Special note: please follow the steps, do not do the previous steps, please do not do the following steps, otherwise your database may be damaged. Generally, it is not recommended to do step 4 and step 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 restored after cleaning the log.

How to solve the problem that the SQLServer database log is full?

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

One way: clear the log.

1. Open query Analyzer and enter the command DUMPTRANSACTION database name WITHNO_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 SQLSERVER 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 in the future, do not want it to get bigger. Use under SQLServer2000: right-click on the database-> Properties-> options-> failure recovery-Model-Select-simple model.

Or use the SQL statement:

Alterdatabase database name

Setrecoverysimple

The above is how to solve the problem that the SQLServer database log is full. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.