Shrink Logs for a Database in Single Recovery mode

Question

Dear All :

i have an instance containing many databases, 3 databases have a big transaction log (12 GB, 9 GB, 4 GB) and consuming space from the partition. the 3 databases are simple recovery way. is there a way to shrink the log for these databases to gain space on the partition?

i know that if the database is in full recovery mode you can backup the database, after that backup the transaction log and finally shrink the log. in the simple recovery mode i can't backup the transaction log to do that

Answer 1

Hi @Tony Mourad ,

In simple recovery mode, you can't backup the transaction log to truncate it and reclaim space. Instead, the transaction log will automatically truncate itself when the database checkpoints.

To force the transaction log to truncate immediately, you can issue a manual checkpoint for the databases in question. This can be done by running the CHECKPOINT; command for each database.

After running the checkpoint command, you can try to shrink the transaction log file to reduce its size.

Keep in mind that shrinking the transaction log file can have performance implications, and it is generally not recommended to do it regularly. If the transaction log file grows too large, it may be an indication that you need to adjust the database's backup and maintenance routines to better manage log growth.

You should schedule full backups on a regular basis to minimize log growth. You can use SQL Server Agent to schedule backups, or you can create maintenance plans to automate the process.

it's still important to monitor its size regularly. If you notice the transaction log growing too quickly, you may need to adjust your backup frequency or increase the size of the log file.

In addition, regularly performing index maintenance can help improve performance and reduce the amount of data that is logged during index operations.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

In simple recov mode it's should work to shrink the log file, but there could be some causes that prevents that, so check first the log reuse state with

select name, log_reuse_wait_desc
from sys.databases

Answer 3

You can use the script below. It will only shrink the end of the log file that is free - so if there are active transactions at the end of the file, it won't shrink. You'll need to wait and try again.

Checkpoint;
DBCC SHRINKFILE(logicalLogFileName,SizeInMBToShrinkTo);
DBCC SHRINKFILE(MyLogFile,10);

--This comaand will show you the active area of the log file - status = 2 = active
dbcc loginfo;

Answer 4

Before you start to shrink files, you need to have an understanding which size of the log file the workload requires. If you shrink below that limit, they will only grow again. And growing log files takes resources, as the newly allocated spaces needs to be zeroed out.

I would not say that 12 GB is particularly big, but obviously this needs to be put in relation to the size of the data file()s).

Answer 5

Hi Seeya and sorry for the late reply. I have monitor the log file of the database for couple of days and saw that the size never grow, still 12 GB. I have an idea, can i set the database back to full recovery mode, backup the log, shrink the log and set again to simple recovery mode? Can i consider that a solution for my case? As the log will never grow in simple recovery mode, is not an issue for me, the issue is the partition where the database are located is running out of space and can't expand the space as also there is no space on the LUN. So if i can shrink this log to gain at least 10 GB of free space it will be great. Thanks for your help. NB: my background is not a DBA so i am trying to find the easiest way to do it.