使用默认值插入非空列

I have a database that is used for development and testing purpose on a web server. What I would like to do is add one column to the table that contains approximately 50,000 records (please note that I am not a database administrator). I am connecting through SSMS with credentials provided by our provider and executing next query

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

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

The error I get is:

我得到的错误是：

Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'my_db' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

消息9002，级别17，状态4，行2数据库'my_db'的事务日志已满。要找出无法重用日志中的空间的原因，请参阅sys.databases中的log_reuse_wait_desc列。

If I execute

如果我执行

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

in a batch together with Add MyCol, log_reuse_wait_desc will be ACTIVE_TRANSACTION. If I execute it as a separate command, it will be CHECKPOINT

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

Recovery mode for database is set to simple. I have searched for a solution on internet, and people are either suggesting to set recovery mode to simple (which already is), or to backup a log file and then shrink it, which some experts consider very bad practice.

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

So what I need to do to add simple NOT NULL column to a table with data?

那么我需要做些什么才能将简单的NOT NULL列添加到带有数据的表中？

Edit: Here is the solution, if someone doesn't want to read through all answers:

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

to get the size of the Transaction log, this is how I did it:

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

sp_helpdb 'YouDatabaseName'

In result window you will get the size of the log file (mine was 8MB). To increase it to larger value, use this query:

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

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

And the error will be gone.

错误将消失。

4 个解决方案

#1

You can specify the size of log bigger, that this message won´t come. You can increase the log to autogrowth (or both) that SQL Server can get more space doing the transactions.

您可以指定更大的日志大小，此消息不会出现。您可以将日志增加到自动增长（或两者），SQL Server可以在事务中获得更多空间。

Please use this link for reference: The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

请使用此链接作为参考：数据库的事务日志已满。要找出无法重用日志中的空间的原因，请参阅sys.databases中的log_reuse_wait_desc列。

You can Grow the size using the following command:

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

ALTER DATABASE YourDatabaseName

MODIFY FILE

(NAME = YourDatbase_log,

SIZE = 1500MB);

You can see the size of Log file:

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

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'

#2

A description for CHECKPOINT can be found on MSDN:

可以在MSDN上找到CHECKPOINT的说明：

No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). This is a routine reason for delaying log truncation.

自上次日志截断以来没有发生检查点，或者日志的头部尚未超出虚拟日志文件（所有恢复模型）。这是延迟日志截断的常规原因。

Unless you manually changed the checkpoint settings, this means your log file is too small. If possible, allow the log file to grow, or remove the maximum size altogether.

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

If you can't increase the log file size, you can try to work around it. One workaround is to split your change in smaller transactions. For example:

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

• Add the column as nullable (Adding a nullable column is a much smaller transaction)
• 将列添加为可空（添加可空列是一个小得多的事务）
• Update the column in small chunks (say, 1000 rows at a time.)
• 以小块更新列（例如，一次更新1000行。）
• Modify the column to be not null
• 将列修改为非null

#3

This means your log file is both too small and can not grow.

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

Adding this column (in this case, not always for bit columns) requires the on-disk structure to be shuffled around which generates log entries so it can be rolled back. Backing up the log file and shrinking it is almost always never a good solution: it sounds like it has already been done here, hence it is too small. And I mean really small because 50,000 rows is small.

添加此列（在这种情况下，并非始终用于位列）需要对磁盘上的结构进行混洗，从而生成日志条目以便可以回滚。备份日志文件并缩小它几乎总是不是一个好的解决方案：听起来它已经在这里完成，因此它太小了。我的意思是非常小，因为50,000行很小。

You can increase the space and/or growth using SQL or the GUI. This answer describes more: SQL Server: How do I increase the size of the transaction log?

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

#4

Try to remove the restriction on the size of the log-file or increase log file:

尝试删除对日志文件大小的限制或增加日志文件：