SQL SERVER 2012 Broken Log

There is a SQL2012 library of the log reached about 100G, usually developers have not done a transaction log backup, and disk space is almost full. Therefore, it can only be truncated. However, since 2k8, SQL Server seems to no longer provide truncate_only and no_log functionality, and this server does not have enough disk space. Therefore, the only way to switch to the simple recovery mode is to truncate the completed transaction first. Then shrik the log file to reclaim the space.

--Querying the status of the transaction log, you can see the Log_backup status, indicating that the log needs to be backed up.

--http://msdn.microsoft.com/zh-cn/library/ms190925.aspx#truncation

Select Name,log_reuse_wait_desc from sys.databases

--View Log usage

DBCC SQLPERF (LOGSPACE)--percent already in use

DBCC LOGINFO--Viewing the Status column is basically 2 means it is not reusable, and only 0 can be reused.

--Switch to Simple recovery mode

ALTER DATABASE test set recovery simple with no_wait--again using DBCC LOGINFO view, you can see that many of the status of the original is 2 has become 0.

--cut back to full

ALTER DATABASE test set full simple with no_wait

--At this point you can try inserting the data to see the status value. You will find it though the recovery model has been cut back to full, however, its transaction log usage is still simple mode, that is, you can reuse the committed log space. At this point, we only have to make a full-library backup to really cut back to the fully-mode. (Remember that this is the case if you create a new database.) Only after the completion of a full library backup immediately after, is the real fully mode. ）

--this time, if you want to narrow the file by shrink log file, you will find it useless. Only after you have finished the log backup, then go to Shrik to function.

Backup LOG test to disk= ' D:\test.log '

DBCC shrinkfile (N ' Test_log ', 0, truncateonly)

Resources:

Introduction to the growth of database log files

When SQL Server writes dirty pages back to the hard disk

SQL SERVER 2012 Broken Log