SQL Server:如何增加事务日志的大小? [英] SQL Server: How do I increase the size of the transaction log?
问题描述
如何增加事务日志的大小?暂时还可以增加事务日志吗?
How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log?
假设我有以下情况.我的Delete操作对于当前事务日志来说太大了.我不想:
Let's say I have the following scenario. I have a Delete operation that's too big for the current transaction log. I wan't to:
- 增加事务日志(我可以检测当前大小吗，请问我需要多大的事务日志才能进行操作?)
- (执行我的操作)
- 备份事务日志
- 还原事务日志的大小.
推荐答案
简短答案:
- SQL 2k5/2k8 如何:增加数据库的大小(SQL服务器管理Studio)(也适用于登录)，如何:缩小数据库(SQL Server Management Studio)
- SQL 2K 如何增加数据库的大小(企业管理器)，如何缩小数据库(企业管理器)
- SQL 2k5/2k8 How to: Increase the Size of a Database (SQL Server Management Studio) (applies to log also), How to: Shrink a Database (SQL Server Management Studio)
- SQL 2K How to increase the size of a database (Enterprise Manager), How to shrink a database (Enterprise Manager)
长答案:您可以使用ALTER DATABASE ... MODIFY FILE更改数据库文件(包括LOG文件)的大小.您可以查找master_files/sysfiles(2k)或<dbname>.sys.database_files(2k5/2k8)以获取日志的逻辑名称.而且，您可以使用 DBCC SHRINKFILE 来缩小文件(如果可能) ).
Long answer: you can use ALTER DATABASE ... MODIFY FILE to change the size of database files, including LOG files. You can look up master_files/sysfiles (2k) or <dbname>.sys.database_files (2k5/2k8) to get the logical name of the log. And you can use DBCC SHRINKFILE to shrink a file (if possible).
我能告诉我我需要多少 交易日志适合我 操作吗?
can I tell how large I need the transaction log to be for my operation?
这取决于很多因素(此新数据吗?是更新吗?是删除吗?什么恢复模型?您是否在SQL 2k8上进行过压缩?等)，但通常比您预期的要大.我估计要执行的更新大小是2.5倍.
It depends on a lot of factors (is this new data? is it an update? is it a delete? what recovery model? Do you have compression on SQL 2k8? etc etc) but is usually bigger than you expect. I would estimate 2.5 times the size of the update you are about to perform.
更新:
想念您说的是删除.粗略估计是已删除数据(包括所有索引)大小的1.5倍.
Missed you say is an DELETE. A rough estimate is 1.5 times the size of the data deleted (including all indexes).
这篇关于SQL Server:如何增加事务日志的大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
