清除SQL Server Log檔 (交易紀錄)

明明資料表都刪完了，但檔案還是很大? 試試看檢查以及清除交易紀錄

資料庫使用久了，Log檔(交易紀錄)也會持續增加，如果沒有限制Log的成長，某天可能會發現硬碟滿了，資料庫會出現下面這樣的錯誤訊息

---

訊息 9002 …
資料庫 'TestDB' 的交易記錄已滿。如果要了解為何無法重複使用記錄中的空間，請參閱 sys.databases 中的 log_reuse_wait_desc 資料行。

---

查看資料庫的 資料 和 交易紀錄 兩者的磁碟使用量

如果想知道資料庫用掉多少容量，可以透過 報表 檢查磁碟使用量，在資料庫上按右鍵 > 報表 > 標準報表 > 磁碟使用量

在報表看到保留的交易紀錄空間 就是可以清除的檔案

交易紀錄也可以用來查看資料庫執行的各種操作紀錄
例如 RiCo寫的透過交易紀錄檔找出誰刪除資料表

---

使用指令清除交易紀錄 Log 檔案

有時候操作久了，實際資料沒有很大，但LOG檔高達幾十GB都有可能，在SQL Server 2008以上都可以使用以下的指令來做清除

目前實測 Server 2008~2019 都可以使用

如果已經是在指定的資料庫，可以直接執行這一段語法，就會清除該資料庫的紀錄

DECLARE @LogicalName nvarchar(128)
DECLARE @DataBaseName nvarchar(128)

SELECT @LogicalName =  f.name, @DataBaseName = d.name
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
where d.name = DB_NAME()

ALTER DATABASE CURRENT SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(@LogicalName, 1)
ALTER DATABASE CURRENT SET RECOVERY FULL WITH NO_WAIT

上述的作法會取得目前的資料庫與邏輯紀錄檔名稱，假設要手動指定資料庫也可以，將下列的資料庫名稱與記錄檔邏輯名稱換掉即可

USE [資料庫名稱]
GO
ALTER DATABASE [資料庫名稱] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(記錄檔邏輯名稱, 1)
ALTER DATABASE [資料庫名稱] SET RECOVERY FULL WITH NO_WAIT
GO

換完之後大概長這樣

USE [TestDB]
GO
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDB_log, 1)
ALTER DATABASE [TestDB] SET RECOVERY FULL WITH NO_WAIT
GO

邏輯名稱可在資料庫上按右鍵=>[屬性]=>[檔案]中查看

注意資料和紀錄的兩個邏輯名稱預設通常不相同，例如：

• TestDB
• TestDB_log
  

也可以直接使用這段sql語法，取出所有的資料庫名稱、邏輯名稱、實體路徑等資料

SELECT d.name DatabaseName, f.name LogicalName,
f.physical_name AS PhysicalName,
f.type_desc TypeofFile
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
GO

清除交易紀錄，容量前後差多少?

• 這個清除就真的是清空，我有一個資料庫約使用半年，交易紀錄146MB，清除完就剩1MB
  

---

參考來源

• 清除 SQL Server Log 檔
  https://emn178.pixnet.net/blog/post/76202708
• SELECT @local_variable (Transact-SQL)
  https://docs.microsoft.com/zh-tw/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15
• How can I retrieve the logical file name of the database from backup file
  https://stackoverflow.com/questions/7089627/how-can-i-retrieve-the-logical-file-name-of-the-database-from-backup-file
• ALTER current database without using its name
  https://stackoverflow.com/questions/14275935/alter-current-database-without-using-its-name

tags: 資料庫