I have a problem with my database log file size

Question

I have a database where the MDF size is 10GB and the LDF size is 47GB.

I changed recovery model to simple and full but it didn't change.

I have a job for full backup (once per day) and log backup (every 15 mins).

How do I decrease the size of the log file LDF file?

After you changed the Recovery Model to Simple, did you try to shrink the database? What else have you tried? — Attie Wagner, Aug 28 '19 at 06:35
Did you try to shrink the files separately, i.e. shrink the database file, then shrink the log file? Note that the Recovery Model should be on Simple... — Attie Wagner, Aug 28 '19 at 06:38
Yes, but did you try to shrink the Log File (LDF)? Right click on the Database, Shrink, then Shrink Files. Click on the drop down field, then click on Log and then click OK? — Attie Wagner, Aug 28 '19 at 06:44
shrinkfile with wizard mode,change recovery model to simple and full — Shayan Soltany, Aug 28 '19 at 06:51
Make sure that the log file you are shrinking is actually the one that you are checking on your hard drive. Sometimes files paths are messy. Also check that you don't have any current big transaction holding your log file's space. — EzLo, Aug 28 '19 at 09:15

score 1 · Answer 1 · answered Aug 28 '19 at 09:19

1

You should be able to use DBCC SHRINKFILE to shrink the transaction log file. Let's say the following query:

select name from MyUserDatabase.sys.database_files;

Returns:

name
--------------------------------
MyUserDatabase
MyUserDatabase_log

You can shrink the transaction log file to its smallest size (which includes whatever data is still residing in it) with:

dbcc shrinkfile (MyUserDatabase_log, 1);

answered Aug 28 '19 at 09:19

AlwaysLearning

shrinkfile is not solution for first way, – Shayan Soltany Aug 31 '19 at 04:29
find query that create big log is better than – Shayan Soltany Aug 31 '19 at 04:30

score 0 · Answer 2 · answered Sep 03 '19 at 06:36

0

my soloution was that : EXEC sys.sp_cdc_disable_db

because in " SELECT * FROM SYS.DATABASES " COLUMN cdc was enable and that maked log_reuse_wait_desc to REPLICATION and increased log file

answered Sep 03 '19 at 06:36

Shayan Soltany

2 Answers2