question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked CarrinWu-MSFT commented

Transactional logfile is growing in size

I have a database with datafile 40GB and Logfile 47GB and it is in full recovery mode and transactional logs are backed up every 2 hrs and auto truncates. What steps should I follow so that the database does not have huge transactional logfile since it will be used for data warehouse. Transactional logfile Autogrowth is set to 256 MB and Mx fie Size 2 TB.
SELECT name, log_reuse_wait_desc FROM sys.databases order by name; log_reuse_wait_desc = Log_Backup

DBCC SQLPERF(logspace) 47879.99 MB Log Size and Log Space Used 2.172195 %

SELECT DB_NAME(database_id) AS DatabaseName ,
database_id ,
CAST(( total_log_size_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS TotalLogSizeMB ,
CAST(( used_log_space_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS LogSpaceUsedMB ,
CAST(used_log_space_in_percent AS DECIMAL(10, 1)) AS LogSpaceUsedPercent
FROM sys.dm_db_log_space_usage;

DatabaseName database_id TotalLogSizeMB LogSpaceUsedMB LogSpaceUsedPercent
DB 43 47880.0 1040.0 2.2

sql-server-general
· 1
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 @SahaSaha-5270, we have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @SahaSaha-5270,

There have 2 ways to reduce log files size:
1.Log truncation: does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log.
2.Log shrinking: removes enough inactive virtual logs to reduce the log file to the requested size.

In addition, shrink log files may cause fragmentation. If you choose this way, please refer to this link to get more information about pros and cons of shrink log files. Or you could backup database more frequently.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
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.

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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Back up the log more frequently?

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.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

Switch recovery model to simple - data warehouse systems generally don't need full recovery model. You could utilize differential backups several times a day - instead of transaction log backups which would provide recovery up to the point of the latest differential.

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.