question

LazherYAAKOUBI-7887 avatar image
0 Votes"
LazherYAAKOUBI-7887 asked LazherYAAKOUBI-7887 commented

DB CM_log.ldf with large size

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.

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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')



0 Votes 0 ·

hi,

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

146727-image.png


146729-image.png


0 Votes 0 ·
image.png (31.7 KiB)
image.png (90.7 KiB)

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




0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered LazherYAAKOUBI-7887 commented

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.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

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


146806-image.png


0 Votes 0 ·
image.png (17.1 KiB)

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.

0 Votes 0 ·

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

0 Votes 0 ·

Issue resolved.
Thanks.

0 Votes 0 ·