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

First of all, the log of this sql server is the log file at the end of * log.ldf. Its log architecture is actually very strict. The original intention is that if you do not make a log backup, you will not delete the log, and then there is a script to recycle the log space for you, which is quite safe and convenient.

It seems quite reasonable, but if you encounter careless developers or users, if the log expands day by day, and you forget to recycle it, it will be sad. At this time, you will not be able to back up, and then you will not be able to empty the log.ldf log, because there is not enough hard disk space, ah, you cannot delete the log if you cannot back up, and it will become an endless cycle.

This is the kind of thing that happens to me. The log is up to 170g, and the hard disk has only 200g of space in total. How to do it well?

Finally, after the guidance of the two behind-the-scenes masters of Baidu and google, I got the following method for reference only, because there may also be some inconsistencies.

Our common method is to contract directly:

-SQL Server contraction method

The most direct operation is in the sql server control interface:

Right-click database → task → shrink database → confirm

If it's still not ideal, or try this:

1. Right-click the database → property → option → failure recovery model → set to simple → to determine

2. Right-click the database → task → shrink database →

3. Right-click the database → property → option → failure recovery model → is set to mass logging → to determine.

If you don't, you'll have to use the following methods:

First of all, let's look at the current status of the log.

DBCC LOGINFO (test9572)

You can see the log of status=0, which represents the log file that has been backed up to disk, while the log of status=2 has not been backed up yet. When shrinking log files, the shrinking space is actually status=0 space. If the log physical files cannot be reduced, there must be a lot of status=2 records here.

Then let's look at the reasons for the delay in log truncation.

SELECT [name], [database_id], [log_reuse_wait], [log_reuse_wait_desc] FROM [sys]. [databases]

The reasons and explanations are as follows:

Log_reuse_wait_desc value

NOTHING currently has one or more reusable virtual log files.

"CHECKPOINT has not had a checkpoint since the last log truncation, or the log header has not been moved across a virtual log file (all recovery models)."

This is a common cause of log truncation delays.

LOG_BACKUP requires a log backup to move the head of the log forward (for the full or bulk-logged recovery model only).

Note: log backups do not interfere with truncation.

After the log backup is completed, the head of the log will be moved forward and some log space may become reusable.

Backup or restore of ACTIVE_BACKUP_OR_RESTORE data is in progress (all recovery models). Data backup operates in the same way as active transactions. Data backup prevents truncation when it is running.

The ACTIVE_TRANSACTION transaction is active (all recovery models). A long-running transaction may exist at the beginning of a log backup. In this case, another log backup may be required to free up space.

After reading the status, my problem is also this LOG_BACKUP, the log is not backed up, that is, the endless cycle mentioned at the beginning, because there is not enough hard disk space, ah, you can't delete the log if you can't back up, so it becomes an endless cycle.

There is always a solution. The principle of the solution is to do it in simple mode, and then return to full mode after the cleanup is completed. Let's see below:

First of all, we need to confirm the file name of the log, because the file name on the hard disk is not necessarily the file name in the data dictionary, so make sure

USE test9572GOSELECT file_id,name FROM sys.database_files;GO

Then you are ready to delete:

USE [test9572] GOALTER DATABASE test9572 SET RECOVERY SIMPLE WITH NO_WAITGO-- simple mode ALTER DATABASE test9572 SET RECOVERY SIMPLE GOUSE test9572GODBCC SHRINKFILE (Number9572log', 11, TRUNCATEONLY) GOUSE [test9572] GOALTER DATABASE test9572 SET RECOVERY FULL WITH NO_WAITGO-- reverts to full mode ALTER DATABASE test9572 SET RECOVERY FULL GO

After you delete it, you can take a look at the hard disk space. Everything is getting better.

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.