Truncate Mirrored Database Log File

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue). The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.

On the mirrored database, you cannot backup the log file with TRUNCATE_ONLY. Here the steps to shrink the log file for a database participating in mirroring

  1. Backup the log file to a location

BACKUP Log YourDatabaseName TO DISK ='D:\BACKUP\DBNAME_20090201.TRN'

  1. Check if there is enough free space on perform the shrink operation

SELECT name ,size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed') ASint)/128.0 AS AvailableSpaceInMB 

FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);

If there is no sufficient free space then the shrink operation cannot reduce file size.

  1. Check if all the transactions are written into the disk

DBCC LOGINFO('YourDatabaseName')

The status of the last transaction should be 0. If not, then backup the transaction log once again.

  1. Shrink the log file

DBCC SHRINKFILE(logfilename , target_size)

If the transaction lof file does not shrink after performing the above steps then backup the log file again to make more of the virtual log files inactive.

Also check the column LOG_REUSE_WAIT_DESC in thesys.databases catalog view to check if the reuse of the transaction log space is waiting on anything. 

Check this link to find the factors that can delay log truncation

http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx

당신의 답변

보여지는 당신의 이름 (옵션):
개인정보 보호: 이메일 주소는 이 알림을 발송하기 위해 사용됩니다.
안티-스팸 확인:
앞으로 이 검증을 피하려면,로그인 혹은 가입 하시기바랍니다.
add
...