清除sql server的log檔

sql 2008 專用

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

查詢所有db的name 與log file size

WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
SELECT 
    name,
    (SELECT SUM(SIZE) FROM fs WHERE TYPE = 0 AND fs.database_id = db.database_id) DataFileSizeMB,
    (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db order by LogFileSizeMB desc