How to Shrink All the Log Files for SQL Server? – Interview Question of the Week #203

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)

Shrinking Database, SQL Backup, SQL DMV, SQL Scripts, SQL Server
Previous Post
How to Start Database in a Single User Mode? – Interview Question of the Week #202
Next Post
How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? – Interview Question of the Week #204

Related Posts

2 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)

    Reply
  • Sakkie Jamneck
    July 29, 2019 3:31 pm

    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?

    Reply

Leave a Reply

Menu
Exit mobile version