I have a client with a SQL2016 DB with a 100Gb MDF and a 4Gb LDF. The DB is set to Simple. I can't get it done to shrink the LDF to a minimum. How can I shrink the LDF to a normal level?
Does this setup and file sizes effects performance?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Before attempting to shrink the logfile, you need to understand if it's a problem or not. It probably isn't.
The log is that size most likely because it needed to grow to that size, meaning that if you shrink it, it will likely grow back to that size in the future - and that log growth will momentarily slow whatever transaction is causing it to grow.
With it only being 4GB and in
SIMPLE recovery, I would recommend simply leaving the log as it is, and perhaps adjust the auto growth to fixed 1GB chunks.
If you simply must shrink the log, you can do so with DBCC SHRINKFILE, using the
TRUNCATEONLY option. Example:
DBCC SHRINKFILE (Database_log_file_name, 1);
This will truncate the file all the way down to the last open transaction which, if the database is idle or mostly idle, will be the whole thing. But first make sure you've set your auto-growth as described above, and you might even want to re-size the log to what you think you need.
But again...4GB on a 100GB database is not a big deal. I'd just let it stay there.
This is a piece of code that I use when the Log file gets too big:
-- check the size of the files. SELECT size / 128.0 as sizeMB, name FROM sys.database_files; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE My_DB_Name SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (My_DB_Name_log, 1); GO -- Reset the database recovery model. ALTER DATABASE My_DB_Name SET RECOVERY FULL; GO -- Be sure to do a full backup, then kick off transaction log backups -- check the size of the files. SELECT size / 128.0 as sizeMB, name FROM sys.database_files;
You have to change the name of
My_DB_Name with the name of your database
Maybe the DB itself can tell you why it's not trimming the log (see Factors that can delay log truncation).
From transaction log file performance perspective file size is not the only parameters affecting performance. To determine the performance degradation caused by transaction log files, below questions must be answered properly.
This list open for extra items depending on your scenario and infrastructure. Base points are; being familiar with the infrastructure and having enough knowledge about transaction log internals and recovery options.