如何减小 sql server 日志文件的大小？

How do I decrease the size of my sql server log file?

提问by Jonathan Beerhalter

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个半演出。这是我们在使用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 个半演出。我对日志文件做了一个缩小，它仍然是 2 个半的演出。我所做的一切似乎都没有缩小文件的大小。

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?

有没有办法解决这个问题？或者是此时分离数据库，删除日志文件然后重新附加一个新的唯一方法？

采纳答案by Adam Robinson

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:

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

1. Perform a full backup of your database. Don't skip this. Really.
2. Change the backup method of your database to "Simple"
3. Open a query window and enter "checkpoint" and execute
4. Perform another backup of the database
5. Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple)
6. Perform a final full backup of the database.

1. 执行数据库的完整备份。不要跳过这个。真的。
2. 将数据库的备份方法更改为“简单”
3. 打开一个查询窗口，输入“checkpoint”并执行
4. 执行数据库的另一个备份
5. 将数据库的备份方法改回“完整”（或其他任何方法，如果它还不是简单的话）
6. 执行数据库的最终完整备份。

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

您现在应该能够缩小文件（如果执行备份不适合您）。

Good luck!

祝你好运！

回答by banny

• Perform a full backup of your database. Don't skip this. Really.
• Change the backup method of your database to "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)
• Perform a final full backup of the database.
• Run below queries one by one
  1. USE Database_Name
  2. select name,recovery_model_desc from sys.databases
  3. ALTER DATABASE Database_Name SET RECOVERY simple
  4. DBCC SHRINKFILE (Database_Name_log , 1)

• 执行数据库的完整备份。不要跳过这个。真的。
• 将数据库的备份方法更改为“简单”
• 打开一个查询窗口，输入“checkpoint”并执行
• 执行数据库的另一个备份
• 将数据库的备份方法改回“完整”（或其他任何方法，如果它还不是简单的话）
• 执行数据库的最终完整备份。
• 一一运行以下查询
  1. USE Database_Name
  2. select name,recovery_model_desc from sys.databases
  3. ALTER DATABASE Database_Name SET RECOVERY simple
  4. DBCC SHRINKFILE (Database_Name_log , 1)

回答by Nick Kavadias

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.

您必须多次收缩和备份日志才能减小日志文件的大小，这是因为日志文件页面不能像数据文件页面那样重新组织，只能被截断。有关更详细的解释，请查看此内容。

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

分离数据库并删除日志文件是危险的！除非您希望数据丢失，否则不要这样做

回答by Richard

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

2. Use dbcc shrinkfileor Management Studioto shrink the log files.

1. 确保数据库的备份模式设置为简单（有关不同模式的概述，请参见此处）。这将避免 SQL Server 在重用空间之前等待事务日志备份。

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

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

在设置备份模式之前，步骤 #2 将不执行任何操作。

回答by user704187

I had the same problem, my database log file size was about 39 gigabyte, and after shrinking (both database and files) it reduced to 37 gigabyte that was not enough, so I did this solution: (I did not need the ldf file (log file) anymore)

我遇到了同样的问题，我的数据库日志文件大小约为 39 GB，在缩小（数据库和文件）后，它减少到 37 GB 不够用，所以我做了这个解决方案：（我不需要 ldf 文件（日志文件）了）

(**Important) : Get a full backup of your database before the process.

（**重要）：在此过程之前获取数据库的完整备份。

1. Run "checkpoint" on that database.

2. Detach that database (right click on the database and chose tasks >> Detach...) {if you see an error, do the steps in the end of this text}

3. Move MyDatabase.ldf to another folder, you can find it in your hard disk in the same folder as your database (Just in case you need it in the future for some reason such as what user did some task).

4. Attach the database (right click on Databases and chose Attach...)

5. On attach dialog remove the .ldf file (which shows 'file not found' comment) and click Ok. (don`t worry the ldf file will be created after the attachment process.)

6. After that, a new log file create with a size of 504 KB!!!.

1. 在该数据库上运行“检查点”。

2. 分离该数据库（右键单击该数据库并选择任务 >> 分离...）{如果您看到错误，请执行本文末尾的步骤}

3. 将 MyDatabase.ldf 移动到另一个文件夹，您可以在与数据库相同的文件夹中的硬盘中找到它（以防万一您将来由于某些原因需要它，例如用户执行了某些任务）。

4. 附加数据库（右键单击数据库并选择附加...）

5. 在附加对话框中删除 .ldf 文件（显示“找不到文件”注释）并单击“确定”。（不要担心在附件过程后会创建 ldf 文件。）

6. 之后，会创建一个大小为 504 KB 的新日志文件！！！。

In step 2, if you faced an error that database is used by another user, you can:

在步骤 2 中，如果您遇到数据库被其他用户使用的错误，您可以：

1.run this command on master database "sp_who2" and see what process using your database.

1.在主数据库“sp_who2”上运行这个命令，看看是什么进程在使用你的数据库。

2.read the process number, for example it is 52 and type "kill 52", now your database is free and ready to detach.

2.读取进程号，例如它是52并输入“kill 52”，现在你的数据库是空闲的，可以分离了。

If the number of processes using your database is too much:

如果使用数据库的进程数量过多：

1.Open services (type services in windows start) find SQL Server ... process and reset it (right click and chose reset).

1.打开服务（在windows start中输入services）找到SQL Server ...进程并重置它（右键单击并选择重置）。

1. Immediately click Ok button in the detach dialog box (that showed the detach error previously).

2. 立即单击分离对话框中的确定按钮（之前显示分离错误）。