SQL Server：如何增加事务日志的大小？

How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log?

如何增加事务日志的大小?是否也可以暂时增加事务日志?

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:

假设我有以下情况。我有一个对当前事务日志来说太大的Delete操作。我想要:

Increase the transaction log (can I detect the current size?, can I tell how large I need the transaction log to be for my operation?)

增加事务日志(我可以检测当前大小吗?,我可以告诉我需要多大的事务日志用于我的操作吗?)

(Perform my operation)

(执行我的操作)

Backup the transaction log

备份事务日志

Restore the size of the transaction log.

恢复事务日志的大小。

3 个解决方案

#1

Short answer:

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 2k5 / 2k8如何:增加数据库的大小(SQL Server Management Studio)(也适用于日志),如何:收缩数据库(SQL Server Management Studio)

SQL 2K How to increase the size of a database (Enterprise Manager), How to shrink a database (Enterprise Manager)

SQL 2K如何增加数据库的大小(企业管理器),如何收缩数据库(企业管理器)

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).

答案很长:您可以使用ALTER DATABASE ... MODIFY FILE来更改数据库文件的大小,包括LOG文件。您可以查找master_files / sysfiles(2k)或 .sys.database_files(2k5 / 2k8)以获取日志的逻辑名称。您可以使用DBCC SHRINKFILE收缩文件(如果可能)。

can I tell how large I need the transaction log to be for my operation?

我可以告诉我需要多大的事务日志才能进行操作吗?

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.

这取决于很多因素(这是新数据吗?它是一个更新吗?它是一个删除吗?什么是恢复模型?你有SQL 2k8上的压缩等等)但通常比你预期的要大。我估计你将要执行的更新大小是2.5倍。

Update:

Missed you say is an DELETE. A rough estimate is 1.5 times the size of the data deleted (including all indexes).

错过了你说是DELETE。粗略估计是删除数据(包括所有索引)大小的1.5倍。

#2

The transaction log can be configured to expand as needed. You set the option to grow automatically. However when the transaction log gets too big (running out of disk space) or making sql server unusable.

可以将事务日志配置为根据需要进行扩展。您将选项设置为自动增长。但是,当事务日志太大(磁盘空间不足)或使sql server无法使用时。

Back up transaction log. SQL will auto truncate inactive transactions

备份事务日志。 SQL将自动截断非活动事务

When you restore the transaction log will be reduced

还原事务日志时会减少

To configure autogrow:

配置自动增长:

Right click on the database in management studio.

右键单击management studio中的数据库。

Select Properties
Update Autogrowth value

更新自动增长值

#3

The most important part is the last line of your scenario: "Restore the size of the transaction log." You mean shrink the log back to its original size.

最重要的部分是场景的最后一行:“恢复事务日志的大小。”您的意思是将日志缩小回原始大小。

This is really dangerous for a lot of reasons, and we've covered them in a couple of stories over at SQLServerPedia:

由于很多原因,这非常危险,我们已经在SQLServerPedia的几个故事中介绍了它们:

#1