About the removal solution for SQL Server 2012 logs getting super large

First of all, this SQL Server mechanism is actually very rigorous, his intention is to---if you do not log backup, you do not delete the log, and then there are scripts for you to reclaim the log space, it is very safe and convenient and practical.

Seems to be quite reasonable, but encountered the development or users of the lack of mind, the log day by day, and forget to recycle, then the sad urge, this time you will not be able to back up, because hard disk space is not enough to use AH, can not backup also can not delete the log, it became a dead loop.

My side of this kind of thing, the log was propped up to 170G, hard disk total 200G space, how to do well?

Finally after Baidu and Google two behind the chef of the guidance, got the following method, for reference only, because there may be some circumstances are not too consistent.

First we look at the current state of the log,

DBCC Loginfo (test9572)

You can see the Status=0 log, which represents the log files that have been backed up to disk, and the status=2 logs are not backed up. When shrinking the log file, the shrinking space is actually status=0 space, if the log physical files can not be reduced, there will be a lot of status=2 to see the record.

Then we look at the reason for the log truncation delay,

SELECT [name], [database_id], [log_reuse_wait], [log_reuse_wait_desc] from [SYS]. [Databases];

Various reasons and explanations are as follows:

Log_reuse_wait_desc value

Nothing currently has one or more reusable virtual log files.

CHECKPOINT The checkpoint has not occurred 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 (only for the full or bulk-logged recovery model).

Note: Log backups do not interfere with truncation.

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

Active_backup_or_restore data Backup or restore is in progress (all recovery models). The data backup runs the same way as the active transaction. Data backup will block truncation at run time.

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 this log_backup, the log is not backed up, that is, the beginning of the dead loop, because hard disk space is not enough, can not be backed up can not delete the log, it became a dead loop

There is always a solution, the principle of the method is in the simple mode, and so on after the removal of the action to adjust back to full mode, the following:

First, we want to confirm the log file name, because the file name on the hard disk is not necessarily the file name in the data dictionary, so confirm the following

Use Test9572goselect file_id,name from sys.database_files; GO

Then you are ready to delete:

Use [test9572]goalter database test9572 set RECOVERY easy with no_waitgo--simple mode alter DATABASE test9572 SET RECOVERY Simpl E gouse test9572godbcc shrinkfile (N ' Test9572_log ', one, truncateonly) gouse [test9572]goalter DATABASE test9572 SET RECOV ERY full with no_waitgo--revert to complete mode alter DATABASE test9572 SET RECOVERY fully GO

After the deletion can look at the hard disk space, everything has become better,

About the removal solution for SQL Server 2012 logs getting super large