I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who originally set things up are no longer available.
Server version: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) Jun 15 2019 07:56:34 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
One of the first things that came to my attention when I looked at the server was an SQL Server Agent job that makes a backup of the main database. The database is in Simple recovery mode. The job has three steps:
-- step 1 DBCC SHRINKFILE (N'DWH_Main_log' , 0) -- step 2 declare @disk varchar(100) = replace(N'G:\DB_Backup\DWH_Main_&d&.bak','&d&', convert(varchar, getdate(), 112)) --print @disk ; BACKUP DATABASE [DWH_Main] TO DISK = @disk WITH RETAINDAYS = 2 , NOFORMAT , NOINIT , NAME = N'DWH_Main-Full Database Backup' , SKIP , NOREWIND , NOUNLOAD , COMPRESSION , STATS = 10 GO -- step 3 WAITFOR DELAY '00:05' ; DBCC SHRINKFILE (N'DWH_Main_log' , 0)
The data file size is about 400GB, the log file size right now is about 50GB.
CREATE DATABASE [DWH_Main] CONTAINMENT = NONE ON PRIMARY ( NAME = N'DWH_Main', FILENAME = N'E:\Data\DWH_Main.mdf' , SIZE = 411114496KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'DWH_Main_log', FILENAME = N'F:\Log\DWH_Main_log.ldf' , SIZE = 53941184KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
I can see A LOT of Log File Auto Growth events, which is no surprise.
DBCC LOGINFO returns 858 rows, i.e. 858 fragments, almost each of them 64MB.
What should I say to my boss about this situation? I don't understand why anyone would want to shrink the transaction log file every day after a full backup, especially for a database in a simple recovery mode.
My first reaction was to simply remove the
DBCC SHRINKFILE commands from the script and then re-configure the initial size of the transaction log file. Grow it manually in 8GB chunks to ~64GB to reduce the fragmentation, but maybe I'm missing something?