Question: How to Shrink All the Log Files for SQL Server?
Answer: This question was asked recently after reading my latest blog post here: SQL SERVER – Small Backup for Large Database. Before you continue reading this blog post, let me stress on a couple of details. First of all – I am no way encouraging you to shrink your database and particularly data files. Shrinking the database can be very very bad for your SQL Server’s Performance and it can be extremely costly to you eventually.
The primary reason, one should consider shrinking the log file is right before the backup so whenever we restore the database, SQL Server does not have to recreate the large log file which actually takes up additional (unnecessary) space as well as increases the time to restore the database.
Now that we have clarified why one should shrink only the log file, let us see the script to shrink all the log files on the server.
DECLARE @ScriptToExecute VARCHAR(MAX);
SET @ScriptToExecute = '';
SELECT
@ScriptToExecute = @ScriptToExecute +
'USE ['+ d.name +']; CHECKPOINT; 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'
SELECT @ScriptToExecute ScriptToExecute
EXEC (@ScriptToExecute) If you want to take shrink the log file of any particular database, you can uncomment the line which says NameofDB and can use the script to shrink only that one database. Let me know if you use any such script in a comment and I will be happy to post that on the blog with due credit to you.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi,
Thanks for this.
I had issues with the filenames as well as the database names and offline databases. This worked.
DECLARE @ScriptToExecute VARCHAR(MAX);
SET @ScriptToExecute = ”;
SELECT
@ScriptToExecute = @ScriptToExecute +
‘USE ‘+ QUOTENAME(d.name) + ‘; CHECKPOINT; DBCC SHRINKFILE (‘+ QUOTENAME(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
— only try this for databases online
and d.state = 0
— AND d.name = ‘NameofDB’
SELECT @ScriptToExecute ScriptToExecute
EXEC (@ScriptToExecute)
Thanks for this! Two questions:
1. Just to confirm, this will shrink ONLY log files, not databases?
2. To what size will it shrink the log files?
Hi ,
Thank you for the script.
Is there any way that before shrinking logfile ,to check db is in use or not.
I mean, If I am scheduling the job ,it should ignore the databases which is having transaction currently ..
HOW to pass multiple databases here
Will put comma separated by correct?
Thanks , it’s working