SqlServer日志清除专家使用经验分享

sqlserver中的⼤数据的批量操作（批量插⼊，批量删除）⾸先我们建⽴⼀个测试⽤员⼯表---创建⼀个测试的员⼯表---create table Employee(EmployeeNo int primary key, --员⼯编号EmployeeName nvarchar(50) null, --员⼯名称CreateUser nvarchar(50) null, --创建⼈CreateDate datetime null, --创建时间)执⾏后结果：那么假如我们要批量插⼊10000条数据，应该怎么办？这⾥有四种⽅法（普通循环，事务循环、批量插⼊、cte插⼊）1、普通循环插⼊（while）/**********************************************普通循环(插⼊数据10000，执⾏时间：1283毫秒)********************************************/--开启开关（记录sql语句各个阶段所消耗的时间）---set statistics time on;--声明两个变量---declare@Index int;declare@Timer datetime;--对两个变量进⾏赋值----set@Index=1;set@Timer=GETDATE();--当循环⼩于1000次执⾏添加语句---while@Index<=10000begin--执⾏添加的语句--insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)values(@Index,'员⼯'+cast(@Index as CHAR(5)),'system',GETDATE())--设置循环次数加1set@Index=@Index+1end--获取执⾏的毫秒数--select DATEDIFF(MS,@Timer,GETDATE()) as'执⾏时间（毫秒）'--关闭开关（记录SQL语句各阶段所消耗的时间）set statistics time off;执⾏普通循环插⼊10000条数据，⼤概需要1200多毫秒，结果如图所⽰2、事务循环插⼊/**********************************************事务循环(插⼊数据1000，执⾏时间：460毫秒)********************************************/--开启事务--begin tran;--开启开关（记录sql语句各个阶段所消耗的时间）---set statistics time on;--声明两个变量---declare@Index int;declare@Timer Datetime;--对两个变量进⾏赋值----set@Index=1;set@Timer=GETDATE();--当循环⼩于1000次执⾏添加语句---while@Index<=10000begin--执⾏添加的语句--insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)values(@Index,'员⼯'+cast(@Index as CHAR(5)),'system',GETDATE())--设置循环次数加1set@Index=@Index+1end--获取执⾏的毫秒数--select DATEDIFF(MS,@Timer,GETDATE()) as'执⾏时间（毫秒）'set statistics time off;--提交事务--commit;执⾏事务循环插⼊10000条数据，⼤概需要400多毫秒，结果如下所⽰：3、批量插⼊/**********************************************批量插⼊(插⼊数据10000，执⾏时间：33毫秒)********************************************/--开启开关（记录sql语句各个阶段所消耗的时间）--set statistics time on;--声明⼀个时间变量---declare@Timer datetime;---对时间变量进⾏赋值---set@Timer=GETDATE();---执⾏批量操作的sql语句---insert Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)select top(10000) EmployeeNo=ROW_NUMBER() over( order by c1.[object_id]),'员⼯','system',GETDATE()from sys.columns as c1 cross join sys.columns as c2order by c1.object_id--获取执⾏的毫秒数--select DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];--关闭开关（记录SQL语句各阶段所消耗的时间）--SET STATISTICS TIME OFF;执⾏批量插⼊10000条数据，⼤概只要33毫秒，结果如图所⽰：4、CTE插⼊--/*******************************************--***CTE插⼊(插⼊数据10000，执⾏时间：40毫秒)--********************************************/--开启开关（记录sql语句各个阶段所消耗的时间）--set statistics time on;--声明⼀个时间变量并赋值--declare@Timer datetime=GETDATE();---将要添加10000条语句组合成CTE模块---;with CTE(EmployeeNo,EmployeeName,CreateUser,CreateDate) as (select top(10000) EmployeeNo = ROW_NUMBER() over (order by C1.[OBJECT_ID]), '员⼯', 'system', GETDATE()from SYS.COLUMNS as C1 cross join SYS.COLUMNS as C2order by C1.[OBJECT_ID])--执⾏CTE插⼊语句---insert Employee select EmployeeNo,EmployeeName,CreateUser,CreateDate from CTE;--获取执⾏的毫秒数--select DATEDIFF(MS, @Timer, GETDATE()) as[执⾏时间(毫秒)];---关闭开关（记录sql语句各个阶段所消耗的时间）---set statistics time off;执⾏插⼊10000条数据，⼤概需要40毫秒，结果如图所⽰：最后我们查看⼀下，批量插⼊10000条数据的员⼯表⼩结：1）按执⾏时间，效率依次为：CTE和批量插⼊效率相当，速度最快，事务插⼊次之，单循环插⼊速度最慢；2）单循环插⼊速度最慢是由于INSERT每次都有⽇志，事务插⼊⼤⼤减少了写⼊⽇志次数，批量插⼊只有⼀次⽇志，CTE的基础是CLR，善⽤速度是最快的那么，假如我们要批量删除我们插⼊的数据，怎么办呢？批量删除有3中⽅法（循环删除、批量删除、truncate 删除）1、循环删除--/*******************************************--***循环删除(删除数据10000，执⾏时间：20毫秒)--********************************************/set statistics time on;--声明⼀个时间变量---declare@Timer datetime=GETDATE();--删除语句--delete from Employee--获取执⾏的毫秒数--select DATEDIFF(MS, @Timer, GETDATE()) as[执⾏时间(毫秒)];set statistics time off;删除10000条数据，所需的时间⼤概为20毫秒，如下所⽰：2、批量删除/**********************************************批量删除(删除数据10000，执⾏时间：23毫秒)********************************************/set statistics time on;declare@Timer datetime=GETDATE();SET ROWCOUNT10000;while1=1begin--开启事务--begin tran--执⾏删除--delete from Employee;--提交事务--commit;IF@@ROWCOUNT=0break;endset ROWCOUNT0;--获取执⾏的毫秒数---select DATEDIFF(MS, @Timer, GETDATE()) as[执⾏时间(毫秒)];set statistics time off;删除10000条数据，所需的时间⼤概为23毫秒，如下所⽰：3、truncate删除--/*******************************************--***truncate删除(删除数据10000，执⾏时间：3毫秒)--********************************************/set statistics time on;--声明⼀个时间变量--declare@Timer datetime=getdate();--执⾏truncate语句--truncate table Employee---获取执⾏的毫秒数---select DATEDIFF(MS, @Timer, GETDATE()) as[执⾏时间(毫秒)]set statistics time off删除10000条数据，所需的时间⼤概为3毫秒，如下所⽰：⼩结：1）TRUNCATE太快了，清除10W数据⼀点没压⼒，批量删除次之，最后的DELTE太慢了2）TRUNCATE快是因为它属于DDL语句，只会产⽣极少的⽇志，普通的DELETE不仅会产⽣⽇志，⽽且会锁记录PS:。

让知识带有温度。

关于SQLServer压缩日志及数据库文件大小整理
关于SQL Server压缩日志及数据库文件大小
请按步骤进行,未进行前面的步骤时,请不要做后面的步骤，以免损坏你的数据库.
一般不建议做第4,6两步，第4步担心全,有可能损坏数据库或丢失数据。

第6步假如日志达到上限,则以后的数据库处理睬失败,在清理日志后才能恢复。

1.清空日志
DUMP TRANSACTION 库名WITH NO_LOG
2.截断事务日志
BACKUP LOG 数据库名WITH NO_LOG
3.收缩数据库文件(假如不压缩,数据库的'文件不会减小
企业管理器--右键你要压缩的数据库--全部任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
也可以用SQL语句来完成
第1页/共2页
千里之行，始于足下。

--收缩数据库
DBCC SHRINKDATABASE(客户资料)
--收缩指定数据文件,1是文件号,可以通过这个语句查询到:
select * from sysfiles
DBCC SHRINKFILE(1)
4.为了最大化的缩小日志文件(假如是sql 7.0,这步只能在查询分析器中进行)
a.分别数据库:
企业管理器--服务器--数据库--右键--分别数据库
b.在我的电脑中删除LOG文件
文档内容到此结束，欢迎大家下载、修改、丰富并分享给更多有需要的人。

第2页/共2页。

数据库恢复神器--Log Explorer 4.2使用教程介绍Log Explorer主要用于对MSSQLServer的事务分析和数据恢复。

可以浏览日志、导出数据、恢复被修改或者删除的数据(包括执行过update,delete,drop和truncate语句的表格)。

一旦由于系统故障或者人为因素导致数据丢失，它能够提供在线快速的数据恢复，最大程度上保证恢复期间的其他事务不间断执行。

他可以支持SQLServer7.0和SQLServer2000以及SQLServer2005.提取标准数据库的日志文件或者备份文件中的信息。

软件下载地址：/c0ojf3kule主要功能：其中提供两个强大的工具：日志分析浏览，对象恢复。

具体功能如下：●日志文件浏览●数据库变更审查●计划和授权变更审查●将日志记录导出到文件或者数据库表●实时监控数据库事务●计算并统计负荷●通过有选择性的取消或者重做事务来恢复数据●恢复被截断或者删除表中的数据运行SQL脚本LogExplore包含两部分1、客户端软件2、服务器代理服务器端代理是保存在SQLServer主机中的一个只读存储过程，他的作用是接受客户端请求，读取在线事务日志块并通过网络传给客户端软件，由客户端软件来读取这些原始的数据块来完成Log Explore所提供的所有功能。

他依赖来的网络协议包括：1、Named Pipe:局域网中适用2、Tcp/Ip:广域网中适用数据库相关介绍事务日志（Transaction Log）SQLServer的每个数据库都包含事务日志，它以文件的形式存储，可以记录数据库的任何变化。

发生故障时SQLServer就是通过它来保证数据的完整性。

操作（Operation）操作是数据库中定义的"原子行为"，每个操作都在日志文件中保存为一条记录。

它可以是用户直接输入的SQL语句，比如标准的insert 命令，日志文件中便会记录一条操作代码来标志这个insert操作。

sqlserver收缩数据库为什么要收缩数据库SQL Server 2000采取预先分配空间的方法来建立数据库的数据文件或者日志文件，比如数据文件的空间分配了100MB，而实际上只占用了50MB空间，这样就会造成存储空间的浪费。

为此，SQL Server 2000提供了收缩数据库的功能，允许对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。

不能将整个数据库收缩到比其原始大小还要小。

因此，如果数据库创建时的大小为 10 MB，后来增长到100MB，则该数据库最小能够收缩到10MB（假定已经删除该数据库中所有数据）。

不能在备份数据库时收缩数据库，也不能在收缩数据库时创建或备份数据库。

4.8.2 什么是自动收缩和手动收缩SQL Server 2000支持对数据库实行自动收缩和手工收缩。

在如图4.38所示的数据库属性的【选项】选项卡中选择【自动收缩】复选框将实现自动收缩。

SQL Server 2000服务器每30分钟检查数据库的空间使用情况。

如果发现大量闲置的空间，就会自动缩小数据库的文件大小。

图4.38 设置自动收缩功能利用企业管理器或DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 语句可以完成手工收缩操作。

4.8.3 收缩数据文件和日志文件有什么不同不论是收缩数据文件还是日志文件，都会确实减少物理文件的大小，两者不同之处如下。

数据文件可以作为文件组或单独地进行手工收缩。

日志文件收缩分别考虑每个文件，基于整个日志文件进行收缩。

日志文件的收缩将删除非活动的VLF。

4.8.4 收缩操作是如何执行的文件的收缩操作始终从文件末端开始反向进行。

例如，如果要将一个5GB 的文件收缩到4GB，则SQL Server 2000将从文件的最后一个1GB开始释放尽可能多的空间。

如果文件中被释放的部分包含使用过的页面，则SQL Server 2000会将这些页面重新定位到保留的部分。

只能将数据库收缩到没有剩余的可用空间为止。