插入具有默认值的not空列

Question

我有一个数据库，用于在web服务器上进行开发和测试。我想做的是在包含大约50,000条记录的表中添加一列(请注意，我不是数据库管理员)。我正在使用我们的提供者提供的凭据通过SSMS进行连接，并执行下一个查询。

ALTER TABLE MyTable
ADD MyCol BIT
CONSTRAINT MyConstraint DEFAULT 1 NOT NULL

我得到的错误是：

Msg 9002，17级，状态4，第2行 数据库'my_db‘的事务日志已满。若要了解日志中空间不能重用的原因，请参阅sys.databases中的sys.databases列。

如果我执行

SELECT log_reuse_wait_desc, * 
FROM sys.databases WHERE name ='my_db'
GO

在与Add MyCol一起的批处理中，log_reuse_wait_desc将是ACTIVE_TRANSACTION。如果我将它作为单独的命令执行，它将是检查点。

数据库的恢复模式设置为simple。我在互联网上搜索了一个解决方案，人们要么建议将恢复模式设置为简单(这已经是了)，要么备份一个日志文件，然后缩小它，一些专家认为这是非常糟糕的做法。

那么，我需要做什么才能将简单的非空列添加到有数据的表中呢？

编辑：是一个解决方案，如果有人不想阅读所有答案：

要获得事务日志的大小，我是这样做的：

sp_helpdb 'YouDatabaseName'

在结果窗口中，您将获得日志文件的大小(我的日志文件大小为8MB)。若要将其增加到更大的值，请使用以下查询：

ALTER DATABASE YourDatabaseName
MODIFY FILE(NAME = YourDatabaseLogName, SIZE=128MB);
GO

错误就会消失。

Answer 1

您可以指定更大的日志大小，这样这个消息就不会出现了。您可以将日志增加到自动增长(或两者兼而有之)，这样Server就可以获得更多处理事务的空间。

请参考此链接：sys.databases中的desc列

您可以使用以下命令来增大大小：

ALTER DATABASE YourDatabaseName

MODIFY FILE

(NAME = YourDatbase_log,

SIZE = 1500MB);

您可以看到日志文件的大小：

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBName'

Answer 2

对CHECKPOINT的描述可以是在MSDN上找到

自上次日志截断以来，没有出现检查点，或者日志的头还没有移动到虚拟日志文件(所有恢复模型)之外。这是延迟日志截断的一个常规原因。

除非手动更改检查点设置，否则这意味着日志文件太小。如果可能，允许日志文件增长，或者完全删除最大大小。

如果无法增加日志文件大小，可以尝试绕过它。解决办法之一是在较小的事务中拆分您的更改。例如：

• 将列添加为可空(添加可空列是一个小得多的事务)
• 以小块(例如，每次1000行)更新列。
• 将列修改为not null

Answer 3

这意味着您的日志文件太小，无法增长。

添加此列(在本例中，bit列并不总是这样)，需要对磁盘上的结构进行调整，以生成日志条目，以便回滚。备份日志文件并缩小它几乎从来都不是一个好的解决方案:它听起来好像已经在这里完成了，因此它太小了。我的意思是很小，因为5万行很小。

您可以使用SQL或GUI来增加空间和/或增长。这个答案描述了更多：Server:如何增加事务日志的大小？