MSSQL database transaction log processing is full

Source: Internet
Author: User

1. Simple Method

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 useCode:
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 Files \ microsoft sqlserver \ MSSQL \ data \ pubs. 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 insecure and may damage the database or cause data loss. If the log reaches the upper limit in step 6th, the data in the future is not recommended.

Database processing will fail and can be restored only after logs are cleared.

Certificate -----------------------------------------------------------------------------------------------------------------------------------

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.