最小化日志测试结果
最小化日志测试结果
--最小化日志测试 --------------------------------------- --1、查看活跃的vlf status为2，和vlf个数 DBCC LOGINFO('xxxxxMinimalLogged') --http://www.cnblogs.com/shanksgao/p/3865250.html?ADUIN=745431401&ADSESSION=1406104346&ADTAG=CLIENT.QQ.5335_.0&ADPUBNO=26366 --最小化日志的操作 --Create Index,Alter Index Rebulid --Bulk import操作(BCP,Bulk insert) --Select into --Blob数据操作(使用Write等) --Insert select(sql 2008后特定条件下可以) --Merge(特定条件) --应用:实际应用过程中我们实际使用insert select的时候居多,就此介绍 --关于insert select操作的最小化日志 --聚集表 --当聚集表为空时,使用TABLOCK 锁提示将会最小化日志 --当聚集表非空时,无论如何将不会最小化日志 --非聚集表 --当堆表为空时,使用TABLOCK锁提示,表中行数据,索引数据(非聚集索引)都会最小化日志 --当堆表非空时,使用TABLOCK锁提示,表中存在非聚集索引,则行数据,索引数据均非最小化日志 --注:最小化日志中表非复制表 --一些文档中在堆表有索引非空的情况认为堆行数据会最小化日志,实际是错误的. --------------------------------------------------------------- --2、 USE [master] RESTORE DATABASE [xxxxxMinimalLogged] FROM DISK = N'E:\DBBackup\xxxxxFULLBACKUP_2014-4-8.bak' WITH FILE = 1, MOVE N'Barefoot.Opinion.12' TO N'E:\DataBase2\Barefoot.Opinion.90952.mdf', MOVE N'FG_ClassifyResult_ClassId_01_data' TO N'E:\DataBase2\xxxxx\FG_ClassifyResult_ClassId_01.ndf', MOVE N'FG_ClassifyResult_ClassId_02_data' TO N'E:\DataBase2\xxxxx\FG_ClassifyResult_ClassId_02.ndf', MOVE N'FG_ClassifyResult_ClassId_03_data' TO N'E:\DataBase2\xxxxx\FG_ClassifyResult_ClassId_03.ndf', MOVE N'FG_ClassifyResult_ClassIdArchiveId_Index' TO N'E:\DataBase2\xxxxx\FG_ClassifyResult_ClassIdArchiveId.ndf', MOVE N'Barefoot.Opinion.12_log' TO N'E:\DataBase2\xxxxx52.ldf', NOUNLOAD, REPLACE, STATS = 5 GO --消息 3122，级别 16，状态 1，第 2 行 --文件初始化失败。RESTORE 操作无法继续。 --消息 3204，级别 16，状态 1，第 2 行 --备份或还原操作已中止。 --消息 3013，级别 16，状态 1，第 2 行 --RESTORE DATABASE 正在异常终止。 --用户取消了查询。 --可能备份文件有问题 ---------------------------------------------------------------------------------------------------- --3、中间表table IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClassifyResult_T]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ClassifyResult_T]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [ClassId] [int] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_ClassID] DEFAULT ((0)), [ArchiveId] [int] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_ArchiveID] DEFAULT ((0)), [PublishOn] [datetime] NOT NULL, [SiteId] [int] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_SiteID] DEFAULT ((0)), [Title] [nvarchar](4000) NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_Title] DEFAULT (''), [Url] [nvarchar](400) NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_Url] DEFAULT (''), [Source] [nvarchar](30) NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_Source] DEFAULT (''), [Summary] [nvarchar](1000) NULL CONSTRAINT [DF_ClassifyResult_Temp_Summary] DEFAULT (''), [ImageCount] [int] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_ImageCount] DEFAULT ((0)), [Sensitive] [bit] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_Sensitive] DEFAULT ((0)), [HarmStatus] [int] NOT NULL, [UrlStatus] [int] NOT NULL, [MatchType] [int] NOT NULL, [IsDelete] [bit] NOT NULL, [ArchiveMD5] [uniqueidentifier] NULL, [AddOn] [datetime] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_GatherDate] DEFAULT (getdate()), [SameCount] [int] NOT NULL CONSTRAINT [DF_ClassifyResult_Temp_SameCount] DEFAULT ((0)), [IsReply] [bit] NOT NULL DEFAULT ((0)), [Author] [nvarchar](200) NULL DEFAULT (''), [Category] [nvarchar](128) NULL DEFAULT (''), [Opinion] [int] NULL DEFAULT ((3)), [Layout] [nvarchar](128) NULL DEFAULT (''), [IsFrontPage] [bit] NULL DEFAULT ((0)), [ContentLength] [int] NULL DEFAULT ((0)), [weight] [decimal](18, 4) NOT NULL DEFAULT ((0)), [SiteType] [int] NOT NULL DEFAULT ((1)), [IsOverseas] [bit] NOT NULL DEFAULT ((0)), [ParentId] [bigint] NOT NULL DEFAULT ((0)), [Taged] [bit] NOT NULL DEFAULT ((0)), [AreaId] [int] NULL, CONSTRAINT [PK_ClassifyResult_Temp] PRIMARY KEY CLUSTERED ( [Id] ASC, [ClassId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Sch_ClassifyResult_ClassId]([ClassId]), CONSTRAINT [IX_ClassifyResult_Temp_ClassIdUrl] UNIQUE NONCLUSTERED ( [ClassId] ASC, [Url] ASC )WITH (IGNORE_DUP_KEY = ON) ON [Sch_ClassifyResult_ClassId]([ClassId]) ) ON [Sch_ClassifyResult_ClassId]([ClassId]) END GO --FK IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ClassifyResult_Temp_ClassifyRule]') AND parent_object_id = OBJECT_ID(N'[dbo].[ClassifyResult_T]')) ALTER TABLE [dbo].[ClassifyResult_T] WITH CHECK ADD CONSTRAINT [FK_ClassifyResult_Temp_ClassifyRule] FOREIGN KEY([ClassId]) REFERENCES [dbo].[ClassifyRule] ([ClassId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[ClassifyResult_T] CHECK CONSTRAINT [FK_ClassifyResult_Temp_ClassifyRule] GO --Id IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ClassifyResult_T]') AND name = N'IX_ClassifyResult_Temp_Id') CREATE NONCLUSTERED INDEX [IX_ClassifyResult_Temp_Id] ON [dbo].[ClassifyResult_T] ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Sch_ClassifyResult_ClassId]([ClassId]) GO ----------------------------------------------------------------------------------------------- --4、 SET IDENTITY_INSERT [ClassifyResult_T] ON INSERT INTO [dbo].[ClassifyResult_T] with(tablockx) ( [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] ) SELECT TOP 10000 [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] FROM [dbo].[ClassifyResult] WHERE ClassId = 7806 ORDER BY id DESC SET IDENTITY_INSERT [ClassifyResult_T] OFF --------------------------------------------------------------------------------- --5、 SELECT * FROM [ClassifyResult_T] --不过滤的话[Log Record Length]<100会有32265条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' ORDER BY [Log Record Length] --过滤的话[Log Record Length]<100会有7091条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' AND [Log Record Length]<100 ORDER BY [Log Record Length] [Log Record Length] LOP_INSERT_ROWS LCX_INDEX_INTERIOR 92 dbo.ClassifyResult_T.IX_ClassifyResult_Temp_Id LOP_INSERT_ROWS LCX_INDEX_INTERIOR 92 dbo.ClassifyResult_T.IX_ClassifyResult_Temp_Id LOP_INSERT_ROWS LCX_INDEX_INTERIOR 92 dbo.ClassifyResult_T.IX_ClassifyResult_Temp_Id DBCC LOGINFO('xxxxxMinimalLogged') --67行记录 -------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------- --删除日志重新测试 关掉sqlserver 删除ldf文件 ALTER DATABASE [xxxxxMinimalLogged] SET EMERGENCY ALTER DATABASE [xxxxxMinimalLogged] REBUILD LOG ON (NAME='Barefoot.Opinion.12_log',FILENAME='E:\DataBase2\xxxxx52.ldf') ALTER DATABASE [xxxxxMinimalLogged] SET MULTI_USER --1、 --TRUNCATE原表 TRUNCATE TABLE [ClassifyResult_T] --消息 3908，级别 16，状态 1，第 1 行 --无法在数据库 'xxxxxMinimalLogged' 中运行 BEGIN TRANSACTION，因为该数据库处于回避恢复模式。 --------------------------------------------------------------- --------------------------------------- --2、查看活跃的vlf status为2，和vlf个数 DBCC LOGINFO('xxxxxMinimalLogged') ------------------------------------------------------------------ --3、 SET IDENTITY_INSERT [ClassifyResult_T] ON INSERT INTO [dbo].[ClassifyResult_T] --WITH(tablockx) ( [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] ) SELECT TOP 10000 [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] FROM [dbo].[ClassifyResult] WHERE ClassId = 7806 ORDER BY id DESC SET IDENTITY_INSERT [ClassifyResult_T] OFF ------------------------------------------------------------------------ --4、 --测试结果 SELECT * FROM [ClassifyResult_T] --不过滤的话[Log Record Length]<100会有37265条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' ORDER BY [Log Record Length] --过滤的话[Log Record Length]<100会有7091条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' AND [Log Record Length]<100 ORDER BY [Log Record Length] [Log Record Length] LOP_INSERT_ROWS LCX_INDEX_INTERIOR 92 dbo.ClassifyResult_T.IX_ClassifyResult_Temp_Id LOP_INSERT_ROWS LCX_INDEX_INTERIOR 92 dbo.ClassifyResult_T.IX_ClassifyResult_Temp_Id LOP_INSERT_ROWS LCX_INDEX_INTERIOR 92 dbo.ClassifyResult_T.IX_ClassifyResult_Temp_Id DBCC LOGINFO('xxxxxMinimalLogged') --71行记录 --------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- --删除日志重新测试 关掉sqlserver 删除ldf文件 ALTER DATABASE [xxxxxMinimalLogged] SET EMERGENCY ALTER DATABASE [xxxxxMinimalLogged] REBUILD LOG ON (NAME='Barefoot.Opinion.12_log',FILENAME='E:\DataBase2\xxxxx52.ldf') ALTER DATABASE [xxxxxMinimalLogged] SET MULTI_USER --1、 --TRUNCATE原表 TRUNCATE TABLE [ClassifyResult_T] --消息 3908，级别 16，状态 1，第 1 行 --无法在数据库 'xxxxxMinimalLogged' 中运行 BEGIN TRANSACTION，因为该数据库处于回避恢复模式。 --------------------------------------------------------------- --------------------------------------- --2、查看活跃的vlf status为2，和vlf个数 DBCC LOGINFO('xxxxxMinimalLogged') ------------------------------------------------------------------ --3、 SET IDENTITY_INSERT [ClassifyResult_T] ON INSERT INTO [dbo].[ClassifyResult_T] WITH(tablockx) ( [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] ) SELECT TOP 10 [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] FROM [dbo].[ClassifyResult] WHERE ClassId = 7806 ORDER BY id DESC SET IDENTITY_INSERT [ClassifyResult_T] OFF ------------------------------------------------------------------------ --4、 --测试结果 SELECT * FROM [ClassifyResult_T] --不过滤的话[Log Record Length]<100会有151条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' ORDER BY [Log Record Length] --过滤的话[Log Record Length]<100会有104条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' AND [Log Record Length]<100 ORDER BY [Log Record Length] --[Log Record Length]：60、72、80、84、88、92 DBCC LOGINFO('xxxxxMinimalLogged') --4行记录 ----------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --删除日志重新测试 关掉sqlserver 删除ldf文件 ALTER DATABASE [xxxxxMinimalLogged] SET EMERGENCY ALTER DATABASE [xxxxxMinimalLogged] REBUILD LOG ON (NAME='Barefoot.Opinion.12_log',FILENAME='E:\DataBase2\xxxxx52.ldf') ALTER DATABASE [xxxxxMinimalLogged] SET MULTI_USER --1、 --TRUNCATE原表 TRUNCATE TABLE [ClassifyResult_T] --消息 3908，级别 16，状态 1，第 1 行 --无法在数据库 'xxxxxMinimalLogged' 中运行 BEGIN TRANSACTION，因为该数据库处于回避恢复模式。 --------------------------------------------------------------- --------------------------------------- --2、查看活跃的vlf status为2，和vlf个数 DBCC LOGINFO('xxxxxMinimalLogged') ------------------------------------------------------------------ --3、 SET IDENTITY_INSERT [ClassifyResult_T] ON INSERT INTO [dbo].[ClassifyResult_T] --WITH(tablockx) ( [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] ) SELECT TOP 10 [Id] , [ClassId] , [ArchiveId] , [PublishOn] , [SiteId] , [Title] , [Url] , [Source] , [Summary] , [ImageCount] , [Sensitive] , [HarmStatus] , [UrlStatus] , [MatchType] , [IsDelete] , [ArchiveMD5] , [AddOn] , [SameCount] , [IsReply] , [Author] , [Category] , [Opinion] , [Layout] , [IsFrontPage] , [ContentLength] , [weight] , [SiteType] , [IsOverseas] , [ParentId] , [Taged] , [AreaId] FROM [dbo].[ClassifyResult] WHERE ClassId = 7806 ORDER BY id DESC SET IDENTITY_INSERT [ClassifyResult_T] OFF ------------------------------------------------------------------------ --4、 --测试结果 SELECT * FROM [ClassifyResult_T] --不过滤的话[Log Record Length]<100会有63条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' ORDER BY [Log Record Length] --过滤的话[Log Record Length]<100会有27条log select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%ClassifyResult_T%' AND [Log Record Length]<100 ORDER BY [Log Record Length] --[Log Record Length]：60、80、84、88、92 DBCC LOGINFO('xxxxxMinimalLogged') --4行记录
结论：确实日志是少了
