Sql 服务器 - 由于 ACTIVE_TRANSACTION，日志已满 [英] Sql server - log is full due to ACTIVE_TRANSACTION

问题描述

我有一个非常大的数据库(50+ GB).为了释放硬盘驱动器中的空间，我尝试从其中一个表中删除旧记录.我运行了命令:

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:

delete from Table1 where TheDate<'2004-01-01';

但是，SQL Server 2012 说:

However, SQL Server 2012 said:

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?

推荐答案

这是我最终解决该错误的方法.

Here is what I ended up doing to work around the error.

首先，我将数据库恢复模型设置为 SIMPLE.更多信息请此处.

First, I set up the database recovery model as SIMPLE. More information here.

然后，通过删除一些旧文件，我能够腾出 5GB 的可用空间，从而为日志文件提供更多空间来增长.

Then, by deleting some old files I was able to make 5GB of free space which gave the log file more space to grow.

我在没有任何警告的情况下成功地重新运行了 DELETE 语句.

I reran the DELETE statement sucessfully without any warning.

我认为通过运行 DELETE 语句，数据库会立即变小，从而释放我的硬盘空间.但事实并非如此.除非您运行以下命令，否则 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:

DBCC SHRINKDATABASE (MyDb, 0);
GO

有关该命令的更多信息此处.

More information about that command here.

这篇关于Sql 服务器 - 由于 ACTIVE_TRANSACTION，日志已满的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！