--建庫腳本，刻意將日誌文件的增長設置為1MB（FILEGROWTH = 1024KB）
CREATE DATABASE [DB01]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DB01', FILENAME = N'D:\MSSQL\DB01.mdf' , SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'DB01_log', FILENAME = N'D:\MSSQL\DB01_log.ldf' , SIZE = 64MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO

--新建的數據庫默認是完整恢復模式，建庫之後做一次完整的備份，否則數據庫還是簡單恢復模式
backup database DB01 to disk = 'D:\MSSQL\DB01.bak';


-- 查看 DB 增長信息
use DB01;
go

SELECT 
    name,
    type_desc ,
    size/128.0 AS current_size,
    -- growth: 8kb page number
    CASE is_percent_growth 
        WHEN 1 THEN CAST(growth AS VARCHAR) + '%' 
        ELSE CAST(growth*8/1024.0 AS VARCHAR) + ' MB' 
    END AS 'growth',
    physical_name
FROM sys.database_files;



--創建一個測試表
create table t1
(
	c1 int identity(1,1),
	c2 varchar(50),
	c3 varchar(50),
	c4 varchar(50),
	c5 varchar(50),
	c6 varchar(50),
	c7 varchar(50),
	c8 datetime2,
	constraint pk_t1_c1 primary key(c1)
);

--往測試表寫入數據
insert into t1 
select newid(),newid(),newid(),newid(),newid(),newid(),GETDATE() from sys.objects a , sys.objects b, sys.objects c;

--再多執行幾次這個語句，生成足夠多的事務日誌
insert into t1 
select  newid(),newid(),newid(),newid(),newid(),newid(),GETDATE() from t1;


-- sqlserver 事務日誌和包含的虛擬日誌文件個數
SELECT db.name, count(dbl.database_id) as Total_VLF_count, convert(decimal (10,2), avg(dbl.vlf_size_mb)) as Avg_VLF_Size_MB
FROM sys.databases db
 CROSS APPLY sys.dm_db_log_info(db.database_id) dbl
where name = 'DB01'
GROUP BY db.name
ORDER BY Total_VLF_count DESC;


--查看數據文件使用比例
SELECT file_id, name,type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;


-- 事務日誌使用情況
DBCC SQLPERF(LOGSPACE);

-- 使用DBCC LOGINFO查看虛擬日誌文件(VLF)狀態,(0=空閒,2=活動)
DBCC LOGINFO;


-- 查看日誌截斷原因
SELECT 
    name AS '數據庫名稱',
    log_reuse_wait_desc AS '日誌重用等待原因'
FROM sys.databases;


-- DB Size 查看
EXEC sp_helpdb 'DB01';



--修復包含大量 VLF 的數據庫

solution1:
	1,備份事務日誌
	backup log DB01 to disk = 'D:\MSSQL\db01.trn2';
	2,手動收縮事務日誌文件，如下單位是MB
	DBCC SHRINKFILE (N'DB01_log' , 64);
	--這種方式收縮無效，需要收縮到指定大小才行
	DBCC SHRINKFILE (N'DB01_log' , 0, TRUNCATEONLY);
	******特別注意******
	1，手動收縮的目的是消除已經分配給事務日誌文件的那些小的VLF
	2，個人在SQLServer 2019標準版下實測，備份事務日誌後收縮，並不能一次就達到目的，需要執行2~3次備份+收縮操作才能將日誌收縮至最小，
	   按道理第一次備份玩日誌就可以截斷了（測試環境，備份後沒有繼續寫入），實際需要執行2~3次備份和收縮才行
       
	3,使用以下 T-SQL 腳本在一個步驟中手動將文件增長到所需的大小,避免頻繁增長導致過多的VLF：
	ALTER DATABASE DB01 MODIFY FILE (NAME='DB01_log', SIZE = 1024MB);
	請注意：上述直接修改DB01_log的大小的時候，生成的VLF的個數，不受限於上述默認自動增長生成VLF個數的規則


solution2:
	1，備份事務日誌
	2，手動收縮事務日誌文件。
	3，ALTER DATABASE DB01 MODIFY FILE (NAME = 'DB01_log', FILEGROWTH = 512MB);