sql server 存储过程分页
2007-11-11 10:55:40 来源:WEB开发网SSYBASE sql server(WINDOWS平台上强大的数据库平台) 的每一个数据库,无论是系统数据库(master,model, sybsystemprocs, tempdb),还是用户数据库,都有自己的transaction log,每个库都有syslogs表。Log记录用户对数据库修改的操作,所以如果不用命令清除, log会一直增长直至占满空间。清除log可用dump transaction 命令;或者开放数据库选项trunc log on chkpt,数据库会每隔一段间隔自动清除log。管理好数据库log是用户操作数据库必须考虑的一面。
下面就几个方面谈谈log及其管理:
一、sql server(WINDOWS平台上强大的数据库平台) 如何记录及读取日志信息
我们知道,sql server(WINDOWS平台上强大的数据库平台)是先记log的机制。Server Cache Memory中日志页总是先写于数据页
Log pages 在commit ,checkpoint,space needed 时写入硬盘。
Data pages 在checkpoint,space needed 时写入硬盘。
系统在recovery 时读每个database 的syslogs 表的信息,回退未完成的事务(transaction)(数据改变到事务前状态);完成已提交的事务(transaction)(数据改变为事务提交后的状态)。在Log中记下checkpoint点。这样保证整个数据库系统的一致性和完整性。
二、Transaction logs 和checkpoint 进程
checkpoint 命令的功能是强制所有“脏”页(自上次写入数据库设备后被更新过的页)写入数据库设备。自动的checkpoint 间隔是由sql server(WINDOWS平台上强大的数据库平台) 根据系统活动和系统表sysconfigures中的恢复间隔(recovery interval)值计算出的。通过指定系统恢复所需的时间总量,恢复间隔决定了checkpoint 的频率。
如果数据库开放trunc log on chkpt选项,则sql server(WINDOWS平台上强大的数据库平台)在数据库系统执行checkpoint时自动清除log。但用户自己写入执行的checkpoint命令并不清除log,即使trunc log on chkpt选项开放。只有在trunc log on chkpt选项开放时,sql server(WINDOWS平台上强大的数据库平台)自动执行checkpoint动作,才能自动清除log 。这个自动的checkpoint动作在sql server(WINDOWS平台上强大的数据库平台)中的进程叫做checkpoint进程。当trunc log on chkpt选项开放时,checkpoint进程每隔0秒左右清除log,而不考虑recovery interval设置时间的间隔。
三、Transaction log 的大小
没有一个十分严格的和确切的方法来确定一个数据库的log应该给多大空间。对一个新建的数据库来说,log大小为整个数据库大小的20%左右。因为log记录对数据库的修改,如果修改的动作频繁,则log的增长十分迅速。所以说log空间大小依赖于用户是如何使用数据库的。
例如:
- update,insert和delete 的频率
- 每个transaction 中数据的修改量
- sql server(WINDOWS平台上强大的数据库平台)系统参数recovery interval 值
- log是否存到介质上用于数据库恢复
还有其它因素影响log大小,我们应该根据操作估计log大小,并间隔一个周期就对log进行备份和清除。
四、检测log 的大小
若log 在自己的设备上,dbcc checktable (syslogs) 有如下信息:
例:***NOTICE:space used on the log segment is 12.87Mbytes,64.35%
***NOTICE:space free on the log segment is 7.13Mbytes,35.65%
根据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 命令。
五、log 设备
一般来说,应该将一个数据库的data和log存放在不同的数据库设备上。这样做的好处:
- 可以单独地备份(back up)transaction log
- 防止数据库溢满
- 可以看到log空间的使用情况。[dbcc checktable (syslogs)]
- 可以镜像log设备
六、log 的清除
数据库的log是不断增长的,必须在它占满空间之前清除。前面已经讨论过,清除log可以开放数据库选项trunc log on chkpt,使数据库系统每隔一段时间间隔自动清除log,还可以执行命令dump transaction 来清除log.trunc log on chkpt 选项同dump transaction with truncate_only 命令一样,只是清除log而不保留log到备份设备上。所以如果只想清除log而不做备份,可以使用trunc log on chkpt 选项及dump transaction with truncate_only,dump transaction with no_log 命令。若想备份,应做dump transaction database_name to dumpdevice。
七、管理大的transactions
有些操作是大批量地修改数据,log增长速度十分快,如:
- 大量数据修改
- 删除一个表的所有记录
- 基于子查询的数据插入
- 批量数据拷贝
下面讲述怎样使用这些transaction 使log 不至溢满:
大量数据修改
例 :
1>update large_tab set col_1=0
2>go
若这个表很大,则此update动作在未完成之前就可能使log满,引起1105错误(log full)而且执行这种大的transaction所产生的exclusive table loc,阻止其他用户在update期间修改这个表,这可能引起死锁。为避免这些情况,我们可以把这个大的transaction分成几个小的transactions,并执行dump transaction 动作。
上述例子可以分成两个或多个小transactions.
例如:
1>update large_tab set col1=0
2>where col2 3>go
1>dump transaction database_name with truncate_only
2>go
1>update large_tab set 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 new_tab select col1,col2 from large_tab
2>go
同样的方法,对这个大的transaction,我们应该处理为几个小的transactions。
1>Insert new_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 new_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 on chkpt 选项
1>use master
2>go
1>sp_dboption database_name,trunc,true
2>go
1>use database_name
2>go
1>checkpoint
2>gobcp... -b 100 (on unix)
bcp... /batch_size=100(on vms)
关闭trunc log on chkpt选项,并dump database。
在这个例子中,一个批执行100行拷贝。也可以将bcp输入文件分成两或多个分开的文件,在每个文件执行后做dump transaction 来避免log 满。
若bcp使用快速方式(无索引,无triggers),这样操作不记log,换句话说,log 只记载空间分配情况。在这种情况下,要先做dump database(为恢复数据库用)。若log太小,可置trunc log on chkpt 选项,这样在每次checkpoint后清除log。
八、Threshold 和transaction log 管理
sql server(WINDOWS平台上强大的数据库平台)提供阈值管理功能,它能帮助用户自动监视数据库log设备段的自由空间。这方面的详细讨论见NO.5技术支持杂志。log的管理是灵活而复杂的,我们应该在实践中摸索经验,针对每个数据库的不同情况,不同操作,做不同处理。
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接