Shrink Microsoft SQL Server Transaction Log Safely and Effectively

Resumo: Learn how to shrink Microsoft SQL Server transaction logs using SSMS or TSQL. Fix log file bloat, avoid disk issues, and maintain optimal database performance.

Este artigo aplica-se a Este artigo não se aplica a Este artigo não está vinculado a nenhum produto específico. Nem todas as versões do produto estão identificadas neste artigo.
Confira outros recursos

Sintomas

Microsoft SQL Server Database log files grow to large file sizes. SQL Server database goes offline and is unavailable if the Transaction Log drive location runs out of space.

Causa

The most common cause of Microsoft SQL Server Transaction Logs to grow continuously is when the Database Recovery Model is set to Full or Bulk Logged. When using the Full or Bulk-Logged recovery model and the log file is not backed up, it continues to grow. File corruption may also occur resulting in the Transaction Log growing to sizes larger than expected relative to database transaction activity.

Resolução

When the SQL Server Transaction log has file sizes threatening to take up all available disk space, the file should be reduced with a shrink log operation. The steps below can be used to shrink the size of SQL Server Transaction Logs.

Use SQL Server Studio Management.

  1. Right-click the database with Transaction Log that must shrink. Select Tasks > Shrink > Files.

Click to shrink files

  1. The interface that comes up gives options for the file shrink operation. Make the selection options below:

File type - In the drop-down field, pick Log.

Release unused space - Select the radio button.

Click OK to complete the file shrink.

Make selections to shrink file

Using TSQL to shrink Transaction Log file.
  1. Run the TSQL statement below to shrink the designated database log file, replace the database log name with the name of the database log being shrunk:

Run the TSQL statement

  1. If the Transaction Log does not shrink successfully with the command above, the step below should allow the shrinking of the log. Replace database name and log file names where necessary.

Secondary shrink command


NOTE: A common reason the SQL Server Transaction log does not shrink is that there is not enough space in the log to allow the shrink operation.

FAQs:
  • How to increase the size of log file in SQL Server?

Follow the Microsoft article Increase the size of a database This hyperlink is taking you to a website outside of Dell Technologies.(this process requires ALTER permission on the database)

  • What are the disadvantages of shrinking database in SQL Server?

While shrinking a SQL log file can free up space, it can have negative consequences on database performance and fragmentation. It is best to address the root cause of excessive database growth and avoid frequent shrinking operations. 

See Microsoft article Manage file space for databases in Azure SQL Managed Instance for more information about SQL log file shrinking.

Mais informações

Shirking Your Microsoft SQL Server Transaction Log File

Duration: 00:07:55 (hh:mm:ss)
When available, closed caption (subtitles) language settings can be chosen using the CC icon on this video player.

Produtos afetados

Dell Quickstart Data Warehouse Appliance, Microsoft Windows Server 2016, Microsoft Windows Server 2019, Microsoft Windows Server 2022, Microsoft Windows 2012 Server R2, Prosupport for Microsoft Software
Propriedades do artigo
Número do artigo: 000196817
Tipo de artigo: Solution
Último modificado: 12 jun. 2025
Versão:  9
Encontre as respostas de outros usuários da Dell para suas perguntas.
Serviços de suporte
Verifique se o dispositivo está coberto pelos serviços de suporte.