shrink log size for large size db

Here is the scripts to backup tran log and shrink log size for large size db

  ----- transaction log total size

DBCC SQLPERF(logspace)

 ----- transaction log free status (0 --- free, 2 --- used)

dbcc loginfo

 checkpoint

go

----- backup transaction log

BACKUP LOG [<user_db>] TO  DISK = N'nul'

go

 ----- shrink log

DBCC SHRINKFILE(<user_db_log_file_name>, EMPTYFILE)

go

 ----- check log wait reasons

Select

name, log_reuse_wait, log_reuse_wait_desc

from sys.databases where name='user_db'

--循环备份收缩日志

--可以做成Job 定期收缩日志

-----------------------backup database and log to nul-----------

BACKUP DATABASE XXXXX TO DISK = N'nul'

DECLARE @i int

SET @i = 1

WHILE @i<5

begin

　　--backup transaction log

　　backup log xxx to disk =N'nul'

　　checkpoint

　　--shrink log

　　dbcc shrinkfile(xxx_log,EMPTYFILE)

　　set @i = @i+1

end

GO