Solve the problem that Mysql cannot contract because the transaction log and log file are too large.

Solve the problem that Mysql cannot contract because the transaction log and log file are too large.

1. ms SQL SERVER 2005

-- 1. Clear logs
Exec ('dump TRANSACTION database name WITH NO_LOG ')
-- 2. truncate transaction logs:
Exec ('backup LOG database name WITH NO_LOG ')
-- 3. compress the database file (if it is not compressed, the database file will not be reduced
Exec ('dbcc SHRINKDATABASE (Database Name )')
-- 4. Set automatic contraction
Exec ('exec sp_dboption Database Name, autoshrink, true ')

Ii. ms SQL SERVER 2008 & 2008r2 & 2012 & 2016

-- Log cleanup in SQL2008 must be performed in simple mode. After the cleanup is completed, the log can be called back to full mode. USE [master] goalter database name set recovery simple with NO_WAITGOALTER DATABASE name set recovery simple -- SIMPLE mode gouse database name GO -- crm50sp1_log is the logic name of the DATABASE Log File dbcc shrinkfile (N 'crm50sp1 _ log ', 11, TRUNCATEONLY) GOUSE [master] goalter database name set recovery full with NO_WAITGOALTER DATABASE name set recovery full -- restore to FULL mode GO

Iii. log files cannot be shrunk due to exceptions

If the log file is abnormally large and cannot be shrunk, check whether there are uncommitted or rolled back transactions.

Run the dbcc opentran command to check whether there are transactions that run very early (the transaction start time is displayed in the message ), all shown indicate that the transaction has not been committed or rolled back, so the MinLSN cannot be rolled forward.
If this is the case, you can use either of the following methods to kill the process by displaying the process number. Kill the process number. (Of course, it is fine to restart the SQL service, if you are allowed to stop the operation );

If there are no transactions that haven't been closed for a long time, simple mode: Back up the database first, and then execute the backup log database name WITH NO_LOG full mode: If no full BACKUP is performed, perform a full BACKUP first, then back up the log file and run dbcc shrinkfile (n'log file logical name', 0, TRUNCATEONLY) to shrink the log file.

The logical name of the LOG file can be obtained through the following statement in the USE erp Database go SELECT [name] FROM sys. database_files WHERE type_desc = 'log'

Summary

The above section describes how to solve the problem that Mysql cannot contract when the transaction log and log file are too large. I hope it will help you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!