数据库完整恢复模式下的日志增长问题

--创建数据库
CREATE DATABASE db_test;
GO

--创建表
USE db_test
CREATE TABLE T(ID INT IDENTITY(1,1),name NVARCHAR(50));

创建环境后数据库日志文件大小：

由于事务日志备份的话，首先需要进行一次完整备份，否则会提示

“System.Data.SqlClient.SqlError: 无法执行 BACKUP LOG，因为当前没有数据库备份。 (Microsoft.SqlServer.Smo) ”

数据库创建好以后，默认是完整恢复模式，所以为了方便测试，我们首先创建一个完整数据库备份。如下：

BACKUP DATABASE [db_test] TO  DISK = N‘D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\db_test.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘db_test-完整 数据库 备份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--------------------------开始测试-----------------------------

编写测试数据，并查看编写后的数据库日志文件大小

DECLARE @I INT=0;
WHILE @I<10000
BEGIN

    INSERT INTO t(name) VALUES(NEWID()),(NEWID()),(NEWID()),(NEWID()),(NEWID())

IF(@I%20=0)
BEGIN

    UPDATE T SET name=GETDATE()

END
IF(@I%100=0)
BEGIN 

    DELETE t WHERE ID IN (SELECT  TOP 10 ID FROM t WHERE ID%3=0);

END
SET @I=@I+1;
END

先进行一次收缩，看看收缩后的分配空间以及可用空间是否都会变小。如下

USE [db_test]
GO
DBCC SHRINKFILE (N‘db_test_log‘ , 0)
GO

根据上图得知，收缩数据库并没有缩小日志分配空间，这次尝试用事务日志备份的方式来截断日志。如下

BACKUP LOG [db_test] TO  DISK = N‘D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\db_test.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘db_test-完整 数据库 备份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

根据上图可得知，我们的数据库日志分配可用空间由原来的45%提高到89%。 虽然没有减少物理内存，但是逻辑空间的确是变大了。

--------------------------结论-----------------------------

所以，采用完整恢复模式下的数据库的时候，要定时做事务日志备份。

???

待研究的问题，为毛做数据库收缩的时候，空间反而增大了，是因为往日志文件里面插入了日志导致的吗。

数据库完整恢复模式下的日志增长问题

标签：where   set   文件大小   table   创建   测试数据   entity   base   缩小