Solve the problem of shrinking Mysql transaction log and log files being too large to shrink

1. 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

--In SQL2008, log clearing must be done in simple mode, and then switched back to full mode after the clearing operation is completed.
USE [master]
GO
ALTER DATABASE database_name SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE database name SET RECOVERY SIMPLE --Simple mode GO
USE database name GO
--crm50sp1_log is the logical name of the database log file DBCC SHRINKFILE (N'crm50sp1_log', 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE database_name SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE database name SET RECOVERY FULL --Restore to full mode GO

3. The log file is abnormally large and cannot be shrunk

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

Execute the DBCC OPENTRAN command to check whether there are any transactions that have been run very early (the transaction start time is displayed in the message). If there are any transactions that have been displayed, it means that the transaction has not been committed or rolled back, so MinLSN cannot be rolled forward.
If this happens, there are two ways to do it. One is to kill the process using the process number displayed in the information. kill process number; (Of course, restarting the SQL service is also OK if downtime is 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 Complete mode: If a full backup has not been performed, first perform a full backup, then back up the log file, and finally execute DBCC SHRINKFILE (N'log file logical name', 0, TRUNCATEONLY) to shrink only the log file.

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

Summarize

The above is what I introduced to you about how to solve the problem of shrinking Mysql transaction logs and log files being too large to shrink. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!