如何在启用镜像的情况下缩小SQL Server日志文件？

I have several databases for my applications that use SQL Server 2005 mirroring to keep a nice copy of the data somewhere else. Works like a charm, however, the log file just seems to be growing and growing, one is at 15GB for a 3GB database.

我的应用程序有几个数据库,它们使用SQL Server 2005镜像来保存其他地方的数据的良好副本。就像魅力一样,但是,日志文件似乎正在增长和增长,一个是3GB数据库的15GB。

Normally, I can just shrink it - however an error pops up that this specifically cannot be done. But, it seems eventually if unchecked would just expand to use all the space on the drive.

通常情况下,我可以缩小它 - 但是会弹出一个错误,特别是无法完成。但是,似乎最终如果未经检查只会扩展以使用驱动器上的所有空间。

I see that I can set a maximum file size for the log file, is that the answer here? Will the log just roll when it hits the max, or will the DB just stop functioning?

我看到我可以为日志文件设置最大文件大小,这是答案吗?日志是否会在达到最大值时滚动,或者数据库是否会停止运行?

Thanks

10 个解决方案

#1

We ran into the same issue after switching from log shipping to mirroring. You have to create a job that regularly backs up the transaction log (every 15 or 30 minutes or so) to keep the log size from getting out of hand.

从日志传送切换到镜像后,我们遇到了同样的问题。您必须创建一个定期备份事务日志的作业(每15或30分钟左右)以防止日志大小失控。

If it is already out of hand, run BACKUP LOG TO DISK = 'Nul', then run a DBCC SHRINKFILE command. Then you can get your job set up.

如果已经失控,请运行BACKUP LOG TO DISK ='Nul',然后运行DBCC SHRINKFILE命令。然后你可以设置你的工作。

Note that The 'Nul' is not a misspelling, it is an old DOS trick that behaves as if you are writing a file, but really just dumps the information off into the ether so it doesn't take up space on the machine.

请注意,'Nul'不是拼写错误,它是一个旧的DOS技巧,其行为就像您正在编写文件一样,但实际上只是将信息转储到以太网中,因此它不会占用机器上的空间。

Also, your log will grow until you run out of space, then the whole thing stops working. Your app will receive an error that the transaction log is full.

此外,您的日志将增长,直到您的空间不足,然后整个过程停止工作。您的应用将收到事务日志已满的错误。

EDIT: David correctly pointed out that this action will break the log chain and reduce the ability to recover from failure. Be sure to use the backup log to 'nul' command as a last resort. If you have space on the drive, you should take a proper log backup and setup your log backup plan. Make sure you also include regular full backups and a cleanup task to remove old files.

编辑:大卫正确地指出,此操作将打破日志链并降低从故障中恢复的能力。请务必将备份日志用作'nul'命令作为最后的手段。如果驱动器上有空间,则应进行适当的日志备份并设置日志备份计划。确保还包括常规完整备份和清除任务以删除旧文件。

#2

I would look at why your logfile is growing, then you can define a strategy for solving your issue. It might be worth checking your mirroring state

我会看看你的日志文件增长的原因,然后你可以定义一个解决问题的策略。可能值得检查您的镜像状态

When a principal database is exposed, it is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the principal from the point the principal entered the exposed state. Also, the principal's transaction log cannot be truncated, so the log file will be growing indefinitely. ref http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

公开主体数据库时,它处于活动状态,具有用户连接和处理事务。但是,没有日志记录被发送到镜像数据库,并且如果主体应该失败,则镜像将不会从主体进入暴露状态的点开始具有任何来自主体的事务。此外,主体的事务日志不能被截断,因此日志文件将无限增长。参考http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

#3

Some good ideas can be found here: http://yukonspace ... transaction-log-growth.

可以在这里找到一些好的想法:http:// yukonspace ... transaction-log-growth。

From my experience with full-recovery mode databases (should behave in somewhat similar way) you need to make regular full backups at least, otherwise logs only grow.

根据我对完全恢复模式数据库的经验(应该以类似的方式运行),您需要至少进行定期完整备份,否则日志只会增长。

#4

http://support.microsoft.com/kb/937531

The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005

CAUSE
Database mirroring will change the physical file sizes only after a checkpoint.

原因数据库镜像将仅在检查点之后更改物理文件大小。

WORKAROUND
Method 1
To work around this problem, run the following statements to create a new stored procedure in the master database. Then, use this stored procedure to shrink the principal database instead of running the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement.
[stored procedure too long to post here]

回到顶端|提供反馈替代方法方法1若要解决此问题,运行以下语句以在master数据库中创建新的存储过程。然后,使用此存储过程收缩主体数据库,而不是运行DBCC SHRINKDATABASE语句或DBCC SHRINKFILE语句。 [存储过程太长,无法在此处发布]

Method 2
Issue a manual checkpoint after shrinking the files on the principal.

方法2收缩主体上的文件后发出手动检查点。

#5

I had the same problem on mirrored databases the log file grew 40GB where main Db file was around 700 Mb I found this solution In microsoft it works but only shrinked my Database about 4 percent

我在镜像数据库上遇到了同样的问题,日志文件增长了40GB,其中主Db文件大约为700 Mb我发现这个解决方案在微软它可以工作,但只收缩了我的数据库大约4%

1- just execute this to create a stored procedure

1-只需执行此操作即可创建存储过程

   use master
   go
   if object_id ('sp_shrink_mirrored_database', 'P') is not null 
     drop proc sp_shrink_mirrored_database 
   go
   create procedure sp_shrink_mirrored_database @dbname sysname, @target_percent int = null
   as
   begin
     declare @filename sysname
     declare @filesize int
     declare @sql nvarchar(4000)

     if @target_percent is null
       dbcc shrinkdatabase (@dbname)
     else 
       dbcc shrinkdatabase (@dbname, @target_percent)
     declare c cursor for 
     select [name], [size] from sys.master_files where type=0 and database_id = db_id (@dbname)
     open c
     fetch next from c into @filename, @filesize
     while @@fetch_status=0
     begin
       set @filesize=(@filesize+1)*8
       set @sql='alter database [' + @dbname + '] modify file ( name=' 
         + @filename + ', size=' + cast(@filesize as nvarchar) + 'kb )'
       execute sp_executesql @sql
       fetch next from c into @filename, @filesize
     end
     close c
     deallocate c
   end
   go

2- execute the stored procedure like this in a new query window For example, if you want to shrink the mydb database, run the following statement.

2-在新的查询窗口中执行这样的存储过程例如,如果要收缩mydb数据库,请运行以下语句。

  EXEC sp_shrink_mirrored_database 'mydb'

#6

Are you sure the Mirror did not fall behind? The principal server instance can't truncate the log until they have been successfully applied to the mirror. After a certain point I believe you may have to backup the transaction log and apply it to the mirror and restart mirroring.

你确定镜子没落后吗?主服务器实例在成功应用于镜像之前无法截断日志。在某一点之后,我认为您可能必须备份事务日志并将其应用于镜像并重新启动镜像。

#7

To shrink transaction file, backup must be performed as there are actives Virtual Log File : http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx

要收缩事务文件,必须执行备份,因为存在活动虚拟日志文件:http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499的.aspx

#8

You have to backup the log regularly otherwise your log file will grow indefinitely. If you can't write to the log (i.e. out of disk space or hit max size) the DB will fail

您必须定期备份日志,否则您的日志文件将无限增长。如果您无法写入日志(即磁盘空间不足或达到最大大小),则DB将失败

SQL Server 2005 has an issue where the shrink isn't reflected on the mirror side. The solution is here http://support.microsoft.com/kb/937531

SQL Server 2005存在一个问题,即镜像端没有反映缩小。解决方案在这里http://support.microsoft.com/kb/937531

This article has some good info on How Log Files work, you should also read Factors that can delay log truncation

本文提供了有关日志文件如何工作的一些很好的信息,您还应该阅读可以延迟日志截断的因素

#9

Simply you can shrink the log file by using the following command

您只需使用以下命令缩小日志文件即可

USE DBNAME
GO
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNAME_log, 1)
GO

#10

-1

Please make sure to perform a full backup after performing log backup with TRUNCATE_ONLY. This breaks the log backup chain.

使用TRUNCATE_ONLY执行日志备份后,请确保执行完整备份。这打破了日志备份链。

#1