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?
Add a comment
|
4 Answers
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.
-
I tried that several times with no result. The results tab shows the following: Current Size: 402465 Minimum Size: 402465 I don't know if it is necessary, I find the performance poor but I don't know if the size of de LDF has impact on the performance.– FlorianNov 19, 2020 at 13:23
-
The size of your LDF file should not have any impact on the performance. Try the
TRUNCATEONLYvia T-SQL, not the GUI. If you can't truncate the log, that indicates that you may have an open transaction; runDBCC OPENTRANto find those.– alrocNov 19, 2020 at 13:51
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
answered Nov 19, 2020 at 21:22
Maybe the DB itself can tell you why it's not trimming the log (see Factors that can delay log truncation).
answered Nov 22, 2020 at 11:37
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.
- Does the log file disks have appropriate RAID set up and/or disk options?
- Do you have appropriate file size and auto growth settings matching with your transaction load profile and transaction count?
- Do you have a log back up plan suited to your recovery model and disaster recovery scenario?
- Is there any long running transaction occupying transaction logs?
- How many VLF file exist in transaction log file?
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.
