限制SQL Server日志文件的增长率

I have a big database of 2.3 billion rows and size of 76gb.

我有一个23亿行,大小为76GB的大型数据库。

My problem is that I want to convert a column type to smalldatetime but during this operation the .ldf file grows so big that it takes my entire disk space (it got up to 350gb) and then query exits with error.

我的问题是我想将列类型转换为smalldatetime但是在此操作期间.ldf文件变得如此之大以至于占用了我的整个磁盘空间(它达到了350gb),然后查询退出时出错。

Is there any way to keep the .ldf small?

有什么方法可以保持.ldf小吗?

I shrinked my .ldf from options.

我从选项中收缩了我的.ldf。

Database recovery model is set to simple.

数据库恢复模型设置为简单。

2 个解决方案

#1

Add a new nullable column of type smalldatetime. Then slowly (that is, batches of 10-100k rows, for instance) populate that column by setting its value based on the old columns value. Once all rows have a value in the new column, drop the old one and rename the new one to the old ones name.

添加一个类型为smalldatetime的新可为空列。然后缓慢(例如,批量为10-100k行)通过基于旧列值设置其值来填充该列。一旦所有行在新列中都有值,请删除旧列并将新值重命名为旧列。

That'll ensure no transaction becomes big enough to severely impact your log file.

这将确保没有任何事务变得足以严重影响您的日志文件。

#2

here is the final code : I run it now so I will know if its 100% good tommorow , but it seems to work

这是最终的代码:我现在运行它所以我会知道它是否100%好tommorow,但它似乎工作

 WHILE (2 > 1) 
  BEGIN 
    BEGIN TRANSACTION 
    UPDATE TOP ( 10000 ) [ais].[dbo].[imis position report]
    SET    [time2] = convert(smalldatetime, left(date, 19)) 
     IF @@ROWCOUNT = 0 
      BEGIN 
        COMMIT TRANSACTION 
         BREAK 
      END 
    COMMIT TRANSACTION 
    -- 1 second delay
    WAITFOR DELAY '00:00:01'
  END -- WHILE
GO

#1