How to shrink a log file where 89.5% is unused

Question

I currently have a log file at 27.6GB where 89.5 is unused. 10.5% is used. What I don't know is what value to use after the DataFile1 example below. Any help or recommendation's is welcomed.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

You may not want to do this: https://stackoverflow.com/a/7193676/4934937 — maio290, Jan 10 '20 at 16:30
I understand. Unfortunately the client is insisting and I don't want to swing at it blindly. Hence why I posted it here. Thanks! — Tbrand17, Jan 10 '20 at 16:52

score 1 · Answer 1 · answered Jan 10 '20 at 16:56

Use the name of the log file you want to shrink, which you can discover like this:

select file_id, type_desc, name, physical_name
from sys.database_files

A database will typically have only one log file. eg

file_id     type_desc                                                    name
----------- ------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------
1           ROWS                                                         AdventureWorksDW2017
2           LOG                                                          AdventureWorksDW2017_log

(2 rows affected)

Then

DBCC SHRINKFILE (N'AdventureWorksDW2017_log' , 0, TRUNCATEONLY)

will attempt to shrink the log file to its initial creation size. If there are used log segments near the end of the file, or the total used space is greater, it won't shrink that much.

You shouldn't do this regularly, though, because you don't want the log file to grow while while the database is active. Log file growth is expensive, as the file must be zeroed, and all sessions that need to commit transactions will have to wait for the the operation to complete.

score 0 · Answer 2 · answered Jan 10 '20 at 22:15

Here is the script I use to get the log file size.

IF OBJECT_ID('tempdb..#tmplogspace') IS NOT NULL DROP TABLE #tmplogspace

CREATE table #TmpLOGSPACE  
(
DatabaseName varchar(100),
LOGSIZE_MB decimal(18, 9),
LOGSPACE_USED decimal(18, 9),
LOGSTATUS decimal(18, 9)
) 

INSERT INTO #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) 
EXEC('DBCC SQLPERF(LOGSPACE);')

SELECT * FROM #TmpLOGSPACE
ORDER BY LOGSIZE_MB DESC

Look at the size and percentage for the DB in question. Then run this:

USE My_Database
GO

DBCC SHRINKFILE (2,20000)  -- 2 is for log file, last number is 20G, which should be a safe start.  Change as desired MB size

How to shrink a log file where 89.5% is unused

2 Answers2