DB CM_log.ldf with large size

Lazher YAAKOUBI 381

Hi,

I have sccm in the Always On availability group, the CM database size = 5GB but the log file CM_log.ldf = 50GB,

how can i reduce the size?

thanks.

Erland Sommarskog 101K Reputation points MVP

2021-11-04T22:14:42.517+00:00
Are you taking log backups? Is the secondary healthy?

What is the output of:

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'yourdb'

And what does the line for this particular database say when you run_

DBCC SQLPERF('logspace')

AmeliaGu-MSFT 13,961 Microsoft Vendor

Hi LazherYAAKOUBI-7887,
In addition, please make sure that transaction log backups are taken made regularly in the primary replica, which ensures that it is regularly truncated.
You can use the following query to get the backup history:

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
When 'I' THEN 'Differential database'
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE database_name='dbname'
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc

And you can go to Alwayson dashboard by expanding the Always On High Availability node, right-clicking the Availability Groups node, and then clicking Show Dashboard to check if the replicas are in Sync. Otherwise, the backup of the transaction log will not truncate the log.

Best Regards,
Amelia

Lazher YAAKOUBI 381 Reputation points

2021-11-05T08:25:12.063+00:00

hi,

Are you taking log backups? = No
Is the secondary healthy? = Yes

Accepted answer

AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor

2021-11-05T09:20:14.947+00:00
Hi LazherYAAKOUBI-7887,
If the database is in full recovery mode, the virtual log file cannot be reused if it has not been backed up. This may cause the log_reuse_wait_desc column to report the LOG_BACKUP value.
Please take log backup of the database in the primary replica. And you must have created at least one full backup before you can create any log backups.
Please refer to Create a Full Database Backup and Back Up a Transaction Log which might be helpful.
Once log backup completes, you can consider to shrink the log file to reduce size:

DBCC SHRINKFILE(FILENAME,SIZE(MB))

Please check this doc.
Best Regards,
Amelia

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".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Lazher YAAKOUBI 381 Reputation points

2021-11-05T11:13:53.66+00:00

Hi,

Backup the database and log is ok, but shrink has no effect, and the state of the database is:

Erland Sommarskog 101K Reputation points MVP

2021-11-05T22:20:19.613+00:00

This is confusing. You first talked about the CM database, but now it is the ReportServer database. Did you backup the transaction log on all databases?

You can try running the CHECKPOINT command in the ReportServer database and is if that changes the value of log_reuse_wait_desc.

By the way, keep in mind that you don't only back up the databases to keep down the transaction log in size. The prime reason you back up the transaction is so that you can recover with minimal data loss in case of a disaster.

AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor

2021-11-08T09:33:27.043+00:00

Hi LazherYAAKOUBI-7887,

How are things going?
Have you shrunk the CM database successfully?
About the value of log_reuse_wait_desc for the reportserver database, does erland’s reply work for you?
If you have any question, please free feel to let us know.

Best Regards,
Amelia

Lazher YAAKOUBI 381 Reputation points

2021-11-08T18:08:08.583+00:00

Issue resolved.
Thanks.
Sign in to comment

DB CM_log.ldf with large size

0 additional answers