sqlserver日志:SQL Server 事务日志

on 2009-2-12 in 数据库 | 0 Comment


SSYBASE SQL Server 每个数据库无论是系统数据库(master,model, sybsystemprocs, tempdb)还是用户数据库都有自己transaction log每个库都有syslogs表Log记录用户对数据库修改操作所以如果不用命令清除 log会直增长直至占满空间清除log可用dump transaction 命令；或者开放数据库选项trunc log _disibledevent=>　　下面就几个方面谈谈log及其管理:

　　、SQL Server 如何记录及读取日志信息

　　我们知道SQL Server是先记log机制Server Cache Memory中日志页总是先写于数据页:

　

\" width=\"371\" alt=\"\"/>

Log pages 在commit ,checkpo,space needed 时写入硬盘

　　Data pages 在checkpo,space needed 时写入硬盘

　　系统在recovery 时读每个database syslogs 表信息回退未完成事务(transaction)(数据改变到事务前状态)；完成已提交事务(transaction)(数据改变为事务提交后状态)在Log中记下checkpo点这样保证整个数据库系统致性和完整性

　　 2、Transaction logs 和checkpo 进程

　　checkpo 命令功能是强制所有“脏”页(自上次写入数据库设备后被更新过页)写入数据库设备自动checkpo 间隔是由SQL Server 根据系统活动和系统表sysconfigures中恢复间隔(recovery erval)值计算出通过指定系统恢复所需时间总量恢复间隔决定了checkpo 频率

　　如果数据库开放trunc log _disibledevent=>　　 3、Transaction log 大小

　　没有个十分严格和确切思路方法来确定个数据库log应该给多大空间对个新建数据库来说log大小为整个数据库大小20%左右log记录对数据库修改如果修改动作频繁则log增长十分迅速所以说log空间大小依赖于用户是如何使用数据库

　　例如:

  1. update,insert和delete 频率
  2. 每个transaction 中数据修改量
  3. SQL Server系统参数recovery erval 值
  4. log是否存到介质上用于数据库恢复
　　还有其它原因影响log大小我们应该根据操作估计log大小并间隔个周期就对log进行备份和清除

　　 4、检测log 大小

　　若log 在自己设备上dbcc checktable (syslogs) 有如下信息:

例:***NOTICE:space used _disibledevent=>　　根据log剩余空间比例来决定是否使用dump transaction 命令来备份和清除log

　　用快速思路方法来判断transaction log 满程度

1>use database_name
2>go
1>select data_pgs (8,doa mpg)
2>from sysindexes where id=8
3>go
Note:this query may be off by as many as 16 pages.

　　在syslogs 表用sp_spaceused 命令

　　 5、log 设备

　　般来说应该将个数据库data和log存放在区别数据库设备上这样做好处:

  1. 可以单独地备份(back up)transaction log
  2. 防止数据库溢满
  3. 可以看到log空间使用情况[dbcc checktable (syslogs)]
  4. 可以镜像log设备
　　 6、log 清除

　　数据库log是不断增长必须在它占满空间的前清除前面已经讨论过清除log可以开放数据库选项trunc log _disibledevent=>　　 7、管理大transactions

　　有些操作是大批量地修改数据log增长速度十分快如:

  1. 大量数据修改
  2. 删除个表所有记录

  3. 基于子查询数据插入
  4. 批量数据拷贝
　　下面讲述怎样使用这些transaction 使log 不至溢满:

　　大量数据修改

　　例 :

1>update large_tab col_1=0
2>go

　　若这个表很大则此update动作在未完成的前就可能使log满引起1105(log full)而且执行这种大transaction所产生exclusive table loc阻止其他用户在update期间修改这个表这可能引起死锁为避免这些情况我们可以把这个大transaction分成几个小transactions并执行dump transaction 动作

　　上述例子可以分成两个或多个小transactions.

　　例如:

1>update large_tab col1=0
2>where col2 3>go
1>dump transaction database_name with truncate_only
2>go
1>update large_tab col1=0
2>where col2>=x
3>go
1>dump transaction database_name with truncate_only
2>go

　　若这个transaction 需要备份到介质上则不用with truncate_only 选项若执 行dump transaction with truncate_only应该先做dump database 命令

　　删除个表所有记录:

　　例:

1>delete table large_tab
2>go

　　同样把整个table记录都删除要记很多log我们可以用truncate table命令代替上述语句完成相同功能

1>truncate table large_tab
2>go

　　这样表中记录都删除了而使用truncate table 命令log只记录空间回收情况而不是记录删除表中每行操作

　　基于子查询数据插入

　　例:

1>insert _tab select col1,col2 from large_tab
2>go

　　同样思路方法对这个大transaction我们应该处理为几个小transactions

1>Insert _tab
2>select col1,col2 from large_tab where col1<=y
3>go
1>dump transaction database_name with truncate_only
2>go
1>insert _tab
2>select col1,col2 from large_tab where col1>y
3>go
1>dump database database_name with truncate_only
2>go

　　同样若想保存log到介质上则dump transaction 后不加with truncate_only 选项若执行dump transaction with truncate_only应该先做dump database 动作

　　批量数据拷贝

　　在使用bcp把数据拷入数据库时我们可以把这个大transaction变成几个小transactions处理避免log剧增

　　开放trunc log _disibledevent=>1>use master
2>go
1>sp_dboption database_name,trunc,true
2>go
1>use database_name
2>go
1>checkpo
2>go

bcp... -b 100 (on unix)
bcp... /batch_size=100(on vms)

　　关闭trunc log _disibledevent=>　　在这个例子中个批执行100行拷贝也可以将bcp输入文件分成两或多个分开文件在每个文件执行后做dump transaction 来避免log 满

　　若bcp使用快速方式(无索引无triggers)这样操作不记log换句话说log 只记载空间分配情况在这种情况下要先做dump database(为恢复数据库用)若log太小可置trunc log _disibledevent=>　　 8、Threshold 和transaction log 管理

　　SQL Server提供阈值管理功能它能帮助用户自动监视数据库log设备段自由空间这方面详细讨论见NO.5技术支持杂志log管理是灵活而复杂我们应该在实战中摸索经验针对每个数据库区别情况区别操作做区别处理


Tags:  sqlserver日志满 删除sqlserver日志 sqlserver事务 sqlserver日志

延伸阅读

最新评论

发表评论