This article describes,
How to shrink log file in SQL server? or Shrink SQL log file
SQL servers have two database file 1. MDF (Data File) and 2. LDF (Transaction Log file).
LDF is SQL database transaction log file and when data operation happen data getting inserted into SQL server log file (LDF). Log files have number of internal files called VLF and it get used to store data and it also get reuse as well.
Amount of data getting stored in log file is dependent on Log file auto growth and limit in SQL server configuration. Also Log file truncation happen in SQL server automatically if we have log backup scheduled or recovery mode of database is simple.
But some time we do not schedule SQL Server Transaction log backup mostly on DEV and Test Server also when new database gets created it is in full recovery mode. In this scenario, when data started inserting into database log file of database get full and also claim all disk space.
SQL log file too big and won’t shrink.
In such scenario, we need to check database log file usage using below DBCC command and verify log file percent usage.
DBCC SQLPERF(Logspace)If SQL Server transaction log file usage is full (100%) then you need to either put database in simple recovery if possible or may need to take SQL server Transaction log backup manually. Once usage of log file reduced then we can shrink log file of database.
Many time we have multiple databases having huge SQL server Transaction log file and we need to shrink log file manually.
How to shrink log file in SQL server?
Below query can help you to shrink all log files.
DECLARE @ScriptToExecute VARCHAR(MAX);
SET @ScriptToExecute = '';
SELECT
@ScriptToExecute = @ScriptToExecute +
'USE ['+ d.name +']; DBCC SHRINKFILE ('+f.name+');'
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
WHERE f.type = 1 AND d.database_id > 4
-- AND d.name = 'NameofDB' -- Add database name here if you want to shrink specific database SQL server log file.
SELECT @ScriptToExecute ScriptToExecute
This scrip will generate database log file shrunk script for all databases. You can open new query window in SSMS and run this script to shrink multiple database log files in one go.
1 thought on “How to shrink log file in SQL server? | Shrink SQL log file | SQL log file too big and won’t shrink”