In this “troubleshooting” tutorial, we will see how to reduce the transaction file (ldf) of a SQL Server database.
Normally this file is truncated during server backups (Veeam, Altaro …) or by the maintenance plan.
In certain situations with SQL Express, the “truncation” is not done and if the size of the file is badly configured, the situation can become problematic and lead to a blocking of the database. (example: known problem on VMWare with the vCenter installed on Windows).
To illustrate this tutorial, here is the size of the lsd 1 file from the wsus database.
Open SQL Server Management Studio in order to access the administration of the instance.
Connect to the SQL Server instance.
Click on the New request 1 button.
In the input area 1 enter the query below, adapting the DB database.
Use DB; ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKDATABASE (DB, 1, TRUNCATEONLY) ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT
Click on the Run 1 button.
Once the query has been executed, the result is displayed in the lower part 1. Close MSSMS.
Go to the location of the files, we can see that the transaction file 1 has been reduced.
