Solving Mysql shrink transaction log and problem solutions

This article mainly introduces how to solve the problem of Mysql shrinking transaction logs and log files that are too large to shrink. It is very good and has reference value. Friends who need it can refer to it. I hope it can help everyone.

一.MS SQL SERVER 2005

--1. Clear the log
exec('DUMP TRANSACTION database name WITH NO_LOG')
--2. Truncate the transaction log:
exec('BACKUP LOG database name WITH NO_LOG')
--3. Shrink the database file (if not compressed, the database file will not be reduced
exec('DBCC SHRINKDATABASE(database name) ')
--4. Set automatic shrink
exec('EXEC sp_dboption database name,autoshrink,TRUE')

2.MS SQL SERVER 2008 &2008r2 &2012 &2016

--在SQL2008中清除日志就必须在简单模式下进行，等清除动作完毕再调回到完全模式。
USE [master]
GO
ALTER DATABASE 数据库名 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 数据库名 SET RECOVERY SIMPLE --简单模式
GO
USE 数据库名 
GO
--crm50sp1_log 为数据库日志文件逻辑名
DBCC SHRINKFILE (N'crm50sp1_log' , 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE 数据库名 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 数据库名 SET RECOVERY FULL --还原为完全模式
GO

3. Log The problem of abnormally large files that cannot be shrunk

If the log file is abnormally large and cannot be shrunk when shrinking, at this time, you need to check whether there are uncommitted or rolled back transactions

Execute the DBCC OPENTRAN command , check whether there are transactions running very early (the transaction start time is displayed in the message). What is displayed indicates that the transaction has not been committed or rolled back, so MinLSN cannot be rolled forward.
If this is the case, you can. Two methods, one is to kill the process through the process number displayed in the information. Kill process number; (Of course, restarting the SQL service is also possible, if shutdown operations are allowed);

If there are no transactions that have not been closed for a long time. , Simple mode: Back up the database first, then execute BACKUP LOG database name WITH NO_LOG Full mode: If no full backup has been performed, perform a full backup first, then back up the log file, and finally execute DBCC SHRINKFILE (N'log file logical name' , 0, TRUNCATEONLY) Only shrink the log file

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

Related recommendations ：

About Mysql transaction

php How to use Mysql transaction instance code

MySQL transaction Processing example explanation

The above is the detailed content of Solving Mysql shrink transaction log and problem solutions. For more information, please follow other related articles on the PHP Chinese website!