Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again.
(对于遇到意外增长（您不希望再次发生这种情况）的情况，应该真正减少日志文件的大小。)
If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. (如果日志文件将再次增长到相同的大小，则暂时缩小将不会有太多效果。)
Now, depending on the recovery goals of your database, these are the actions you should take. (现在，根据数据库的恢复目标，这些是您应该执行的操作。)
First, take a full backup (首先，进行完整备份)
Never make any changes to your database without ensuring you can restore it should something go wrong.
(如果发生问题，请务必对数据库进行任何更改，否则请确保无法还原数据库。)
If you care about point-in-time recovery (如果您关心时间点恢复)
(And by point-in-time recovery, I mean you care about being able to restore to anything other than a full or differential backup.)
(（通过时间点恢复，我的意思是您关心的是能够还原到除完整备份或差异备份以外的任何内容。）)
Presumably your database is in FULL recovery mode.
(大概您的数据库处于FULL恢复模式。)
If not, then make sure it is: (如果没有，请确保它是：)
ALTER DATABASE testdb SET RECOVERY FULL;
Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup - this is for your protection, not to needlessly eat away at your disk space.
(即使您进行常规的完整备份，日志文件也会不断增长，直到执行日志备份为止-这是为了保护您，而不是不必要地吞噬了磁盘空间。)
You should be performing these log backups quite frequently, according to your recovery objectives. (根据恢复目标，您应该经常执行这些日志备份。)
For example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. (例如，如果您有一条业务规则规定在发生灾难时可以承受不超过15分钟的数据丢失损失，那么您应该有一份每15分钟备份一次日志的作业。)
Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don't choose any of the shrink options in maintenance plans, they're awful). (这是一个脚本，它将基于当前时间生成带有时间戳的文件名（但是您也可以使用维护计划等来执行此操作，只是不要在维护计划中选择任何收缩选项，它们太糟糕了）。)
DECLARE @path NVARCHAR(255) = N'\backup_sharelogestdb_'
+ CONVERT(CHAR(8), GETDATE(), 112) + '_'
+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
+ '.trn';
BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;
Note that \\backup_share\ should be on a different machine that represents a different underlying storage device.
(请注意， \\backup_share\应该位于代表不同基础存储设备的另一台计算机上。)
Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that's on the same physical host) does not really help you, since if the machine blows up, you've lost your database and its backups. (将这些备份到同一台计算机（或使用同一基础磁盘的另一台计算机，或同一物理主机上的另一台VM）并不能真正帮助您，因为如果计算机崩溃，您将丢失数据库和它的备份。)
Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; (根据您的网络基础架构，在本地备份然后将其转移到幕后的其他位置可能更有意义。)
in either case, you want to get them off the primary database machine as quickly as possible. (无论哪种情况，您都希望尽快将它们从主数据库计算机中删除。)
Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it's blown up to now.
(现在，一旦运行了常规的日志备份，就应该将日志文件压缩到比现在炸毁的文件更合理的大小。)
This does not mean running SHRINKFILE over and over again until the log file is 1 MB - even if you are backing up the log frequently, it still needs to accommodate the sum of any concurrent transactions that can occur. (这并不意味着运行SHRINKFILE一遍又一遍，直到日志文件为1 MB -即使你经常备份日志，它仍然需要适应可能出现的任何并发事务的总和。)
Log file autogrow events are expensive, since SQL Server has to zero out the files (unlike data files when instant file initialization is enabled), and user transactions have to wait while this happens. (日志文件自动增长事件非常昂贵，因为SQL Server必须将文件归零（与启用即时文件初始化后的数据文件不同），并且用户事务必须等待这种情况发生。)
You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don't want to make your users pay for it. (您希望尽可能少地执行此增长-收缩-增长-收缩例程，并且您当然不想让您的用户为此付费。)
Note that you may need to back up the log twice before a shrink is possible (thanks Robert).
(请注意，您可能需要先备份两次日志，然后才能进行收缩（感谢Robert）。)
So, you need to come up with a practical size for your log file.
(因此，您需要为日志文件提出一个实际的大小。)
Nobody here can tell you what that is without knowing a lot more about your system, but if you've been frequently shrinking the log file and it has been growing again, a good watermark is probably 10-50% higher than the largest it's been. (没有人可以在不了解系统的情况下告诉您那是什么，但是如果您经常缩小日志文件并且又在增长，那么一个很好的水印可能比最大的水印高10-50％。 。)
Let's say that comes to 200 MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way: (假设这是200 MB，并且您希望任何后续的自动增长事件为50 MB，那么您可以通过以下方式调整日志文件的大小：)
USE [master];
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO
Note that if the log file is currently > 200 MB, you may need to run this first:
(请注意，如果日志文件当前> 200 MB，则可能需要先运行此文件：)
USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO
If you don't care about point-in-time recovery (如果您不关心时间点恢复)
If this is a test database, and you don't care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode.
(如果这是一个测试数据库，并且您不关心时间点恢复，则应确保数据库处于SIMPLE恢复模式。)
ALTER DATABASE testdb SET RECOVERY SIMPLE;
Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file (essentially phasing out inactive transactions) instead of growing to keep a record of all transactions (like FULL recovery does until you back up the log).
(将数据库置于“ SIMPLE恢复模式将确保SQL Server重用日志文件的某些部分（实质上淘汰不活动的事务），而不是为了保留所有事务的记录而进行扩展（就像“ FULL恢复”一样，直到备份日志为止） 。)
CHECKPOINT events will help control the log and make sure that it doesn't need to grow unless you generate a lot of t-log activity between CHECKPOINT s. (CHECKPOINT事件将有助于控制日志，并确保它不需要增长，除非您在CHECKPOINT之间生成大量t-log活动。)
Next, you should make absolute sure that this log growth was truly due to an abnormal event (say, an annual spring cleaning or rebuilding your biggest indexes), and not due to normal, everyday usage.
(接下来，您应绝对确保此日志增长确实是由于异常事件（例如，每年一次的春季大扫除或重建您的最大指标），而不是由于正常的日常使用。)
If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain? (如果将日志文件缩小到一个可笑的小尺寸，而SQL Server只需要再次增大它以适应您的正常活动，那么您获得了什么？)
Were you able to make use of that disk space you freed up only temporarily? (您是否能够利用您只是暂时释放的磁盘空间？)
If you need an immediate fix, then you can run the following: (如果需要立即修复，则可以运行以下命令：)
USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO
Otherwise, set an appropriate size and growth rate.
(否则，请设置适当的大小和增长率。)
As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters. (根据时间点恢复情况下的示例，您可以使用相同的代码和逻辑来确定合适的文件大小并设置合理的自动增长参数。)
Some things you don't want to do (一些你不想做的事)
Back up the log with TRUNCATE_ONLY option and then SHRINKFILE .
(使用TRUNCATE_ONLY选项和SHRINKFILE备份日志 。)
For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. (首先，此TRUNCATE_ONLY选项已被弃用，并且在当前版本的SQL Server中不再可用。)
Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup. (其次，如果您处于FULL恢复模式，这将破坏您的日志链，并需要新的完整备份。)
Detach the database, delete the log file, and re-attach .
(分离数据库，删除日志文件，然后重新附加 。)
I can't emphasize how dangerous this can be. (我不能强调这有多危险。)
Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc. (您的数据库可能无法备份，可能会被怀疑备份，可能必须还原到备份（如果有备份），等等。)
Use the "shrink database" option .
(使用“缩小数据库”选项 。)
DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. (这样做的DBCC SHRINKDATABASE和维护计划选项是个坏主意，尤其是在您真的只需要解决日志问题的情况下。)
Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE ... MODIFY FILE (examples above). (定位您要调整的文件，并使用DBCC SHRINKFILE或ALTER DATABASE ... MODIFY FILE （上面的示例）分别进行调整。)
Shrink the log file to 1 MB .
(将日志文件缩小到1 MB 。)
This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! (这看起来很诱人，因为，在某些情况下，SQL Server将允许我执行此操作，并查看它释放的所有空间！)
Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE ), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. (除非您的数据库是只读的（并且应该使用ALTER DATABASE对其进行标记），否则绝对会导致许多不必要的增长事件，因为无论恢复模式如何，日志都必须容纳当前事务。)
What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully? (暂时释放该空间的目的是什么，以便SQL Server可以缓慢而痛苦地收回该空间？)
Create a second log file .
(创建第二个日志文件 。)
This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. (这将暂时缓解已满磁盘的驱动器，但这就像试图用创可贴修复被刺破的肺部一样。)
You should deal with the problematic log file directly instead of just adding another potential problem. (您应该直接处理有问题的日志文件，而不是仅添加另一个潜在的问题。)
Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. (除了将某些事务日志活动重定向到其他驱动器之外，第二个日志文件确实对您没有任何作用（与第二个数据文件不同），因为一次只能使用其中一个文件。)
Paul Randal also explains why multiple log files can bite you later . (<a href="h
