Sql 服务器 - 由于 ACTIVE_TRANSACTION，日志已满
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23820741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql server - log is full due to ACTIVE_TRANSACTION
提问by oabarca
I have a very large database (50+ GB). In order to free space in my hard drive, I tried deleting old records from one of the tables . I ran the command:
我有一个非常大的数据库（50+ GB）。为了释放硬盘驱动器中的空间，我尝试从其中一个表中删除旧记录。我运行了命令：
delete from Table1 where TheDate<'2004-01-01';
However, SQL Server 2012 said:
但是，SQL Server 2012 说：
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'MyDb' is full due to 'ACTIVE_TRANSACTION'.
and it did not delete a thing. What does that message mean? How can I delete the records?
它没有删除任何东西。那条消息是什么意思？如何删除记录？
回答by oabarca
Here is what I ended up doing to work around the error.
这是我最终为解决该错误所做的工作。
First, I set up the database recovery model as SIMPLE. More information here.
首先，我将数据库恢复模型设置为 SIMPLE。更多信息在这里。
Then, by deleting some old files I was able to make 5GB of free space which gave the log file more space to grow.
然后，通过删除一些旧文件，我能够腾出 5GB 的可用空间，从而为日志文件提供更多空间来增长。
I reran the DELETE statement sucessfully without any warning.
我在没有任何警告的情况下成功地重新运行了 DELETE 语句。
I thought that by running the DELETE statement the database would inmediately become smaller thus freeing space in my hard drive. But that was not true. The space freed after a DELETE statement is not returned to the operating system inmediatedly unless you run the following command:
我认为通过运行 DELETE 语句，数据库会立即变小，从而释放硬盘驱动器中的空间。但事实并非如此。除非您运行以下命令，否则 DELETE 语句后释放的空间不会以中介方式返回给操作系统：
DBCC SHRINKDATABASE (MyDb, 0);
GO
More information about that command here.
有关该命令的更多信息请点击此处。
回答by Ankit Bajpai
Restarting the SQL Server will clear up the log space used by your database. If this however is not an option, you can try the following:
重新启动 SQL Server 将清除数据库使用的日志空间。但是，如果这不是一个选项，您可以尝试以下操作：
* Issue a CHECKPOINT command to free up log space in the log file.
* Check the available log space with DBCC SQLPERF('logspace'). If only a small
percentage of your log file is actually been used, you can try a DBCC SHRINKFILE
command. This can however possibly introduce corruption in your database.
* If you have another drive with space available you can try to add a file there in
order to get enough space to attempt to resolve the issue.
Hope this will help you in finding your solution.
希望这能帮助您找到解决方案。
