Sql Server中的大事务,有什么问题吗?
我有一个程序,可以在一个事务中对 SQL Server 2005 或 2008 数据库执行许多批量操作(删除和创建索引、创建列、全表更新等)。
是否会出现任何问题?
- 我知道即使在简单恢复模式下事务日志也会扩展。
- 该程序在系统正常运行期间不会执行,因此锁定和并发不是问题。
是否还有其他原因将事务拆分为更小的步骤?
I have a program which does many bulk operations on an SQL Server 2005 or 2008 database (drops and creates indexes, creates columns, full table updates etc), all in one transaction.
Are there any problems to be expected?
- I know that the transaction log expands even in Simple recovery mode.
- This program is not executed during normal operation of the system, so locking and concurrency is not an issue.
Are there other reasons to split the transaction into smaller steps?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
简而言之,
考虑一下,如果在事务开始和完成之间的任何时间点,您的服务器遇到故障,为了使数据库联机,SQL Server 必须执行崩溃恢复过程,这将涉及从日志中回滚所有未提交的事务。
假设您开发了一种数据处理解决方案,该解决方案足够智能,可以从中断的地方继续。通过使用单个交易,这对您来说不是一个可用的选项,因为您需要再次从乞讨开始该过程。
In short,
Consider that if at any point between the time the transaction started and finished, your server experienced a failure, in order to be bring the database online SQL Server would have to perform the crash recovery process which would involve rolling back all uncommitted transactions from the log.
Supposing you developed a data processing solution that is intelligent enough to pick up from where it left off. By using a single transaction this would not be an option available to you because you would need to start the process from the begging once again.
如果事务导致太多数据库日志条目(更新),则日志可能会达到所谓的“高水位线”。这是日志达到(大约)其绝对最大大小一半的点,然后必须开始回滚所有更新(这将消耗与执行更新大约相同数量的磁盘。
此时不回滚点将意味着冒着最终达到最大日志大小但仍未完成事务或点击回滚命令的风险,此时数据库会因为没有足够的日志空间可回滚而陷入困境。
If the transaction causes too many database log entries (updates) the log can hit what is known as the "high water mark". It's the point at which the log reaches (about) half of its absolute maximum size, when it must then commence rolling back all updates (which will consume about the same amount of disk as it took to do the updates.
Not rolling back at this point would mean risking eventually reaching the maximum log size and still not finishing the transaction or hitting a rollback command, at which point the database is screwed because there's not enough log space to rollback.
在磁盘空间耗尽之前,这并不是真正的问题,但您会发现回滚将需要很长时间。当然,我并不是说要为失败做好计划。
但是,请考虑该进程而不是事务日志。我会考虑将以下内容分离:
如果出现问题,我希望您有回滚脚本和/或备份。
真的需要原子地完成所有事情吗?
It isn't really a problem until you run out of disk space, but you'll find that rollback will take a long time. I'm not saying to plan for failure of course.
However, consider the process not the transaction log as such. I'd consider separating:
If something goes wrong I'd hope that you have rollback scripts and/or a backup.
Is there really a need to do everything atomically?
根据更新语句的复杂性,我建议仅在小型表(例如 100 行)上执行此操作。特别是当您只有少量可用主内存时。否则,例如,大表上的更新可能需要很长时间,甚至看起来会挂起。然后很难弄清楚进程(spid)正在做什么以及可能需要多长时间。
无论如何,我不确定“删除索引”是否是事务记录操作。请参阅这个问题< /a> 位于 stackoverflow.com。
Depending on the complexity of your update statements, I'd recommend to do this only on small tables of, say, a few 100 rows. Especially if you have only a small amount of main memory available. Otherwise, for instance, updates on big tables can take a very long time and even appear to hang. Then it's difficult to figure out what the process (spid) is doing and how long it might take.
I'm not sure whether "Drop index" is transaction-logged operation anyway. See this question here on stackoverflow.com.