Shulou(Shulou.com)06/01 Report--

This article is about how to solve the problem of shrinking transaction logs in Mysql. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

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

-- to clear the log in SQL2008, you must do so in simple mode, and then return to full mode after the cleanup action is completed. USE [master] GOALTER DATABASE database name SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE database name SET RECOVERY SIMPLE-- simple schema GOUSE database name GO--crm50sp1_log is the database log file logical name DBCC SHRINKFILE (Npropricrm50sp1log log', 11, TRUNCATEONLY) GOUSE [master] GOALTER DATABASE database name SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE database name SET RECOVERY FULL-restore to full schema GO

three。 The problem that the log file is unusually large and cannot be shrunk

If the log file is unusually large and cannot shrink when it shrinks, you need to check to see if there are any uncommitted or rollback transactions

Execute the DBCC OPENTRAN command to see if there is a transaction that runs very early (the transaction start time shown in the message), which indicates that the transaction has not been committed or rolled back, thus causing MinLSN to be unable to roll forward.

If there is such a situation, there are two ways to kill the process, one is through the process number displayed by the message. Kill process number; (of course, it is OK to restart the SQL service, if downtime is allowed)

If there are no transactions that have not been closed for a long time, simple mode: first back up the database, then execute the BACKUP LOG database name WITH NO_LOG full mode: if you have not done a full backup, first make a full backup, then back up the log files, and finally execute DBCC SHRINKFILE (N'log file logical name, 0, TRUNCATEONLY) to shrink only the log files

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

Thank you for reading! On how to solve the problem of Mysql shrinking transaction log is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.