DB研討會 : [轉貼]清除SQL Server Log檔

昨天去上課。晚上下課一回來，同事就抱怨系統太慢了。

今天早上剛好是民國100年元旦，就來公司檢查有無百年蟲問題吧。順便 Check 一下系統太慢的問題。

一檢查，SQL Server 磁碟沒空間了。當下就知道為什麼慢了。

當務之急，就是將超大的資料庫  log 檔備份並 shink。

SQL Server 2005

use master
go
--備份目前的 log
backup log DatabaseName to disk='c:\db.log' WITH NOFORMAT
--將 log 清除
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
--找到 DatabaseNameLog 的值
use DatabaseName
go
select name from sys.database_files
where type_desc = 'log'
--縮減 log file 到 1MB
DBCC SHRINKFILE(DatabaseNameLog, 1)

SQL Server 2008

到了 SQL Server 2008，Trancate_only 的指令正式被取消了。由於資料庫的復原模式為簡單的情形下是不記錄所有的 log資料的。我們可以利用這一點清除 log 資料。因此，替代的方法，是將資料庫的復原模式先換成簡單模式，再調整為完整。指令如下

use master
go
--備份目前的 log
backup log DatabaseName to disk='c:\db.log' WITH NOFORMAT
--將資料庫復原模式切換到簡單模式
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE WITH NO_WAIT
--找到 DatabaseNameLog 的值
use DatabaseName
go
select name from sys.database_files
where type_desc = 'log'
--縮減 log file 到 1MB
DBCC SHRINKFILE(DatabaseNameLog, 1)
--將資料庫復原模式切換到完整模式
USE [master]
GO
ALTER DATABASE [DatabaseName] SET RECOVERY FULL WITH NO_WAIT

• 第一個我想到的方法是把Database的Recovery model設成Simple，簡單來說就是不需要交易記錄，對於Insert Delete Update 很少的系統勉強還說得過去，不過對於交易量大的系統來說就不行了，沒有交易記錄萬一資料庫突然往生，總不能用full backup還原然後要User重新輸入一天的交易吧。
  


• 第二個方法是定時進行備份。為什麼log大小跟備份有關係呢? 簡單來說，資料庫的ldf檔就是用來儲存Full Backup後所發生的所有交易。如果你從今天從來沒有為資料庫進行過備份， 理論上ldf檔會無限的膨脹下去，而且利用Shrink指令也無法把交易檔壓縮。因為沒備份的話就等於ldf檔裡面的東西統統有用，當然沒辦法壓縮了。所以要保持交易檔案的size就是要持續保持備份，在每次備份完了以後自動把交易檔Truncate成初始大小，這樣可以長期保持相對小的交易檔。