SQL SERVER 事务日志已满
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4132821/
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 Transaction log full
提问by Dhiva
How to free Transaction log space in SQL SERVER 2005:
如何在 SQL SERVER 2005 中释放事务日志空间：
I have Transaction log size = 70 GB and there four transaction logs 1,2,3,4 in different drives. Through DBCC SQLPERF(LOGSPACE) I found that the transaction log is Full (uses 100 %) and I want to free up the space in transaction log and I don't want T-log backup. I don't have space to backup the transaction log. And my DB is in Replication state.
我有事务日志大小 = 70 GB，并且在不同的驱动器中有四个事务日志 1、2、3、4。通过 DBCC SQLPERF(LOGSPACE) 我发现事务日志已满（使用 100%），我想释放事务日志中的空间，我不想 T-log 备份。我没有空间来备份事务日志。我的数据库处于复制状态。
- How can I free up the Transaction log or
- Instead of 3 transaction log files, can I have only one log file or
- If shrink method what to do with Replication?
- 如何释放事务日志或
- 我可以只有一个日志文件，而不是 3 个事务日志文件吗？
- 如果收缩方法与复制做什么？
采纳答案by TomTom
Check the following article from Microsoft.
查看来自 Microsoft 的以下文章。
http://msdn.microsoft.com/en-us/library/ms175495.aspx
http://msdn.microsoft.com/en-us/library/ms175495.aspx
and i want to free up the space in Transaction log and I dont want T-log backup
我想释放事务日志中的空间，我不想要 T-log 备份
MS does not support this.
微软不支持这个。
Transaction log backup IS NEEDED HERE. Get a drive, do it.
这里需要事务日志备份。得到驱动器，做它。
If you don't do it, you can not free the log.
如果不这样做，则无法释放日志。
Without freeing the log you can not shrink the files. Ergo, you need to make a backup, whether you want to or not.
如果不释放日志，则无法缩小文件。因此，无论您是否愿意，您都需要进行备份。
If you don't care about the transaction logs the databases should have been created in simple recovery mode to start with.
如果您不关心事务日志，则应该以简单恢复模式开始创建数据库。
回答by subhash
Perform the following sequence of statements:
执行以下语句序列：
BACKUP LOG <db_name> WITH TRUNCATE_ONLY
--or save to log to other drives on disk if required
GO
CHECKPOINT
GO
--replace 2 with your actual log file number.
DBCC SHRINKFILE (2, 100)
If the log does not shrink then check the reason of log file growth. More information about this can be found here:
如果日志没有缩小，则检查日志文件增长的原因。可以在此处找到有关此的更多信息：
http://sqlreality.com/blog/ms-sql-server-2008/troubleshooting-the-full-transaction-log-problem/
http://sqlreality.com/blog/ms-sql-server-2008/troubleshooting-the-full-transaction-log-problem/
回答by DataWriter
Will shrinking the files individually accomplish what you want?
单独缩小文件会达到您想要的效果吗？
DBCC SHRINKFILE (N'LogFile', 1)
回答by yrushka
It was already discussed about the recovery model switch on replicated databases here. and you can read some documentation as well on MSDN.
它已经讨论过关于复制数据库的恢复模式开关位置。您也可以在 MSDN 上阅读一些文档。
If you don't want to make T-log backups because of space restraints you can put the replicated database in Simple recovery model and try the:
如果由于空间限制您不想进行 T-log 备份，您可以将复制的数据库置于简单恢复模式并尝试：
Perform a CHECKPOINT. The database in the SIMPLE recovery mode clears THE LOG out on each checkpoint.
执行检查点。处于 SIMPLE 恢复模式的数据库会在每个检查点上清除 THE LOG out。
DBCC SHRINKFILE (N'LogFileName', 1) -- shrink the log file to 1 MB
After that change it back to Full. In order to remove some of your DB's log files you first have to empty the ones meant for removal and then remove them. Details here.
之后将其更改回完整。为了删除某些数据库的日志文件，您首先必须清空要删除的日志文件，然后再删除它们。详情请看这里。
