如何减小sql服务器日志文件的大小?

So I have been neglecting to do any backups of my fogbugz database, and now the fogbugz ldf file is over 2 and half gigs. Thats been built up over the six months we've been using fogbugz.

因此，我一直忽略了对fogbugz数据库进行任何备份，现在fogbugz ldf文件已经超过了2.5个gigs。这是在我们使用fogbugz的六个月里建立起来的。

I backed up the database, then I backed up, and truncated the transaction log, yet the transaction log is still 2 and a half gigs. I did a shrink on the log file and its still 2 and a half gigs. Nothing I do seems to shrink the file in size.

我备份了数据库，然后备份了事务日志，并截断了事务日志，但是事务日志仍然是2.5个gigs。我在日志文件上做了一个收缩，仍然是2。5。我所做的一切似乎都不能缩小文件的大小。

Is there anyway to fix the problem? Or is the only way back at this point to detach the database, delete the log file and then reattach with a new one?

有办法解决这个问题吗?或者，回到这一步的唯一方法是分离数据库，删除日志文件，然后重新附加一个新的文件?

4 个解决方案

#1

Welcome to the fickle world of SQL Server log management.

欢迎来到多变的SQL Server日志管理世界。

SOMETHING is wrong, though I don't think anyone will be able to tell you more than that without some additional information. For example, has this database ever been used for Transactional SQL Server replication? This can cause issues like this if a transaction hasn't been replicated to a subscriber.

有些事情是错误的，尽管我认为没有人能够告诉你更多的信息。例如，这个数据库曾经被用于事务SQL Server复制吗?如果没有将事务复制到订阅服务器，则会导致类似的问题。

In the interim, this should at least allow you to kill the log file:

在此期间，至少应该允许您终止日志文件:

Perform a full backup of your database. Don't skip this. Really.
执行数据库的完整备份。不要跳过。真的。
Change the backup method of your database to "Simple"
将数据库的备份方法更改为“Simple”
Open a query window and enter "checkpoint" and execute
打开查询窗口并输入“检查点”并执行
Perform another backup of the database
执行另一个数据库备份
Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple)
将数据库的备份方法更改为“Full”(或其他方法，如果不是很简单的话)
Perform a final full backup of the database.
执行数据库的最后完全备份。

You should now be able to shrink the files (if performing the backup didn't do that for you).

现在，您应该能够收缩文件(如果执行备份时没有为您这样做)。

Good luck!

好运！

#2

Perform a full backup of your database. Don't skip this. Really.
执行数据库的完整备份。不要跳过。真的。
Change the backup method of your database to "Simple"
将数据库的备份方法更改为“Simple”
Open a query window and enter "checkpoint" and execute
打开查询窗口并输入“检查点”并执行
Perform another backup of the database
执行另一个数据库备份
Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple)
将数据库的备份方法更改为“Full”(或其他方法，如果不是很简单的话)
Perform a final full backup of the database.
执行数据库的最后完全备份。
Run below queries one by one
1. USE Database_Name
2. 使用Database_Name
3. select name,recovery_model_desc from sys.databases
4. 选择的名字,从sys.databases recovery_model_desc
5. ALTER DATABASE Database_Name SET RECOVERY simple
6. 简单修改数据库Database_Name设置恢复
7. DBCC SHRINKFILE (Database_Name_log , 1)
8. DBCC SHRINKFILE (Database_Name_log, 1)
在下面逐一运行查询，使用Database_Name选择name,recovery_model_desc从sys。数据库改变数据库Database_Name集恢复简单的DBCC SHRINKFILE (Database_Name_log, 1)

#3

Ensure the database's backup mode is set to Simple (see here for an overview of the different modes). This will avoid SQL Server waiting for a transaction log backup before reusing space.

确保数据库的备份模式被设置为简单的(请参阅这里对不同模式的概述)。这将避免SQL服务器在重新使用空间之前等待事务日志备份。
Use dbcc shrinkfile or Management Studio to shrink the log files.

使用dbcc shrinkfile或Management Studio缩小日志文件。

Step #2 will do nothing until the backup mode is set.

第2步在设置备份模式之前不会做任何事情。

#4

You have to shrink & backup the log a several times to get the log file to reduce in size, this is because the the log file pages cannot be re-organized as data files pages can be, only truncated. For a more detailed explanation check this out.

您必须多次收缩和备份日志a才能使日志文件减小大小，这是因为日志文件页不能重新组织，因为数据文件页可以被截断。想要得到更详细的解释，请查看以下内容。

detaching the db & deleting the log file is dangerous! don't do this unless you'd like data loss

删除数据库和删除日志文件是危险的!除非你喜欢数据丢失，否则不要这样做。

#1