how to truncate/clean the sql server 2008 database log

这个问题困扰了我很久很久，今天终于搞定了。

之所以没搞定，一个核心问题就是没搞懂：什么情况下才可以清除db的日志？

先看两篇文章： 

1. 国产的：http://blog.csdn.net/downmoon/article/details/4997925

2. 国外的：http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

 

两篇文章都没提到一个重要的东西：db的option: log_reuse_wait

参考msdn官方解释：http://msdn.microsoft.com/en-us/library/ms345414(SQL.105).aspx

我发现这个问题，也是在根据网上提示，先备份数据时得到的：

BACKUP LOG <your db name> to disk = 'c:/projects/e_log.bak'

备份成功了，但是我得到一个额外信息：

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture.

Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

看第一句话就够了，意思很清楚，之所以不能truncate, 因为我的log还在Pending replication。 一个小库库，咋还replication呢？ 

（我到现在也没想起来什么时候，怎么设置成要replication的，因为新建的数据库，默认根本不replication的）

同时我在msdn forum的提问也得到了答复：
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/294c62bb-6e77-46d2-882c-c1c56804f710

看一下：

SELECT name, log_reuse_wait_desc,log_reuse_wait
FROM sys.databases
ORDER BY name

果然，我的log_reuse_wait是REPLICATION。 这样就导致无法truncate数据库了。

继续，执行下面命令：

exec sp_dboption  '<your_dbname>', 'published'
exec sp_removedbreplication '<your_dbname>'

 

然后再查log_reuse_wait， 现在是NOTHING了。 great. 

 

执行清除日志脚本：

SET NOCOUNT ON

CREATE TABLE #TransactionLogFiles (
DBName VARCHAR(150),
LogFileName VARCHAR(150)
)

DECLARE DBList CURSOR FOR
SELECT name FROM master..sysdatabases
--WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
WHERE name IN ('<change to your db name>')
AND status & 512 = 0

DECLARE @DB VARCHAR(100)
DECLARE @SQL VARCHAR(8000)

OPEN DBList
FETCH NEXT FROM DBList INTO @DB WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'USE [' + @DB + '] INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''+ @DB + ''', RTRIM(Name) FROM sysfiles WHERE FileID In(2,3,4)'
EXEC(@SQL)
FETCH NEXT FROM DBList INTO @DB
END

DEALLOCATE DBList

DECLARE TranLogList CURSOR FOR
SELECT DBName, LogFileName FROM #TransactionLogFiles
DECLARE @LogFile VARCHAR(100)

OPEN TranLogList
FETCH NEXT FROM TranLogList INTO @DB, @LogFile WHILE @@FETCH_STATUS <> -1
BEGIN
--PRINT @DB +',' + @LogFile
SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''True'''
EXEC (@SQL)
SELECT @SQL = 'USE [' + @DB + '] DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'
EXEC (@SQL)
SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''False'''
EXEC(@SQL)
FETCH NEXT FROM TranLogList INTO @DB, @LogFile
END

DEALLOCATE TranLogList
DROP TABLE #TransactionLogFiles

 

再去看看你的ldf文件大小，小了吧？

enjoy it.