Database logs file size, is normal to have a data log file bigger in size than the data files.

Question

Database logs file size, is normal to have a data log file bigger in size than the data files.

jg_cordero

SSC Veteran

Points: 268
More actions
August 9, 2018 at 4:51 am

#407695

I am starting with the database support to a SQL Server database, this database has two data files and one data log file.
My question is why and how to deal, the fact that in this database the data log file size is bigger than the combined size of the data files?
To be more specific the size of data files combined (are two) is about 60 GB and the data log file size is about 90 GB ?
This database is getting data constantly, is related to get monitoring data from SCADA system.
This database has a week FULL backup, if I understand after a FULL backup the space in the data log file could be reusable and the log file size do not need to be increased.
How could manage correctly this topic about the data log file size? What would we the procedure to fix that? Could I SHRINK this data log file? What would be the best moment to do it?
I appreciate all suggestions about this topic in order to improve the management of the data log size of this database?
Best regards and thanks in advance.
Jose Cordero

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

A full backup doesn't mark the space in the log file as reusable. Have a read through this, and let us know if you have any questions.

John

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 2

jg_cordero - Thursday, August 9, 2018 4:51 AM
I am starting with the database support to a SQL Server database, this database has two data files and one data log file.
My question is why and how to deal, the fact that in this database the data log file size is bigger than the combined size of the data files?
To be more specific the size of data files combined (are two) is about 60 GB and the data log file size is about 90 GB ?
This database is getting data constantly, is related to get monitoring data from SCADA system.
This database has a week FULL backup, if I understand after a FULL backup the space in the data log file could be reusable and the log file size do not need to be increased.
How could manage correctly this topic about the data log file size? What would we the procedure to fix that? Could I SHRINK this data log file? What would be the best moment to do it?
I appreciate all suggestions about this topic in order to improve the management of the data log size of this database?
Best regards and thanks in advance.
Jose Cordero

Do you ship transaction logs? If not and you only take a full weekly backup you could put the database in Simple Recovery mode and then shrink the log file. It should be quick to do and can be done whilst online.

jg_cordero SSC Veteran Points: 268 More actions · Answer 3

John Mitchell-245523 - Thursday, August 9, 2018 4:58 AM
A full backup doesn't mark the space in the log file as reusable. Have a read through this, and let us know if you have any questions.
John

Thanks for your help, let me read the suggested information.

I supposed by your answer if a Log backup database is executed so the space in the log file is marked as reusable?

Thanks a lot for your help and the suggested article.

Regards,

Jose Cordero

jg_cordero SSC Veteran Points: 268 More actions · Answer 4

Jonathan AC Roberts - Thursday, August 9, 2018 5:38 AM
jg_cordero - Thursday, August 9, 2018 4:51 AM
I am starting with the database support to a SQL Server database, this database has two data files and one data log file.
My question is why and how to deal, the fact that in this database the data log file size is bigger than the combined size of the data files?
To be more specific the size of data files combined (are two) is about 60 GB and the data log file size is about 90 GB ?
This database is getting data constantly, is related to get monitoring data from SCADA system.
This database has a week FULL backup, if I understand after a FULL backup the space in the data log file could be reusable and the log file size do not need to be increased.
How could manage correctly this topic about the data log file size? What would we the procedure to fix that? Could I SHRINK this data log file? What would be the best moment to do it?
I appreciate all suggestions about this topic in order to improve the management of the data log size of this database?
Best regards and thanks in advance.
Jose Cordero
Do you ship transaction logs? If not and you only take a full weekly backup you could put the database in Simple Recovery mode and then shrink the log file. It should be quick to do and can be done whilst online.

No the database is not configured to ship transaction log file, but I need to review first how to manage the backup strategy in the best way.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

jg_cordero - Thursday, August 9, 2018 8:08 AM
I supposed by your answer if a Log backup database is executed so the space in the log file is marked as reusable?

Jose

Yes, that's right, although there may be certain things that stop some or all of the log being reused, such as replication or large VLFs. It's certainly true to say that if you don't back up the log, you can't reuse the space. (Unless you cheat and switch the database to Simple recovery, but I don't recommend that!)

John

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 6

jg_cordero - Thursday, August 9, 2018 8:10 AM
Jonathan AC Roberts - Thursday, August 9, 2018 5:38 AM
jg_cordero - Thursday, August 9, 2018 4:51 AM
I am starting with the database support to a SQL Server database, this database has two data files and one data log file.
My question is why and how to deal, the fact that in this database the data log file size is bigger than the combined size of the data files?
To be more specific the size of data files combined (are two) is about 60 GB and the data log file size is about 90 GB ?
This database is getting data constantly, is related to get monitoring data from SCADA system.
This database has a week FULL backup, if I understand after a FULL backup the space in the data log file could be reusable and the log file size do not need to be increased.
How could manage correctly this topic about the data log file size? What would we the procedure to fix that? Could I SHRINK this data log file? What would be the best moment to do it?
I appreciate all suggestions about this topic in order to improve the management of the data log size of this database?
Best regards and thanks in advance.
Jose Cordero
Do you ship transaction logs? If not and you only take a full weekly backup you could put the database in Simple Recovery mode and then shrink the log file. It should be quick to do and can be done whilst online.
No the database is not configured to ship transaction log file, but I need to review first how to manage the backup strategy in the best way.

In the meantime you won't be able to shrink the log file until you change the recovery mode to Simple. If you right click the database in SSMS Object Explorer, go to Options. Change the Recovery model to "Simple". After that you'll be able to shrink the log file.

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 7

jg_cordero - Thursday, August 9, 2018 8:10 AM
Jonathan AC Roberts - Thursday, August 9, 2018 5:38 AM
jg_cordero - Thursday, August 9, 2018 4:51 AM
I am starting with the database support to a SQL Server database, this database has two data files and one data log file.
My question is why and how to deal, the fact that in this database the data log file size is bigger than the combined size of the data files?
To be more specific the size of data files combined (are two) is about 60 GB and the data log file size is about 90 GB ?
This database is getting data constantly, is related to get monitoring data from SCADA system.
This database has a week FULL backup, if I understand after a FULL backup the space in the data log file could be reusable and the log file size do not need to be increased.
How could manage correctly this topic about the data log file size? What would we the procedure to fix that? Could I SHRINK this data log file? What would be the best moment to do it?
I appreciate all suggestions about this topic in order to improve the management of the data log size of this database?
Best regards and thanks in advance.
Jose Cordero
Do you ship transaction logs? If not and you only take a full weekly backup you could put the database in Simple Recovery mode and then shrink the log file. It should be quick to do and can be done whilst online.
No the database is not configured to ship transaction log file, but I need to review first how to manage the backup strategy in the best way.

I guess you manage backups and log backups based on the SLA. Also look at the auto growth setting for the log file because 10% can create many VLFs depending on current size which I guess decreases performance so change the autogrowth setting to 20 gb or 30 gb depending on how large your log file gets, helps.

jg_cordero SSC Veteran Points: 268 More actions · Answer 8

John Mitchell-245523 - Thursday, August 9, 2018 8:18 AM
jg_cordero - Thursday, August 9, 2018 8:08 AM
I supposed by your answer if a Log backup database is executed so the space in the log file is marked as reusable?
Jose
Yes, that's right, although there may be certain things that stop some or all of the log being reused, such as replication or large VLFs. It's certainly true to say that if you don't back up the log, you can't reuse the space. (Unless you cheat and switch the database to Simple recovery, but I don't recommend that!)
John

John

I agree totally with you.

Let me ask some additional questions?
The last six months there is only a weekly full backup database and none logs backup database.
The size of the logs backup database file could be different if it executed in a short period of time after executed a Full backup database ? or it does not matter ?
This question is because the actual size of the log file is about 90 GB and I do not know if there is some kind of proportion between the size of the log file and
the size of the backup file ?

Thanks a lot John.

Jose

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Jose

Yes, the more often you back up your log, the smaller the log file needs to be and the smaller your log backups will be. But there comes a point at which you cease to make savings. Your log file needs to be at least the size of your largest transaction. If you perform index maintenance, for example, you will need enough space in your transaction log for the rebuild of your largest index. However, since the full backup doesn't have any impact on the transaction log, it doesn't make any difference how you schedule your log backups relative to the full backup. The log backup frequency will most often be dictated by business requirements - if the RPO (recovery point objective or the maximum amount of data in minutes you can afford to lose in the event of a disaster) is 15 minutes, then you'd better make sure you're making log backups at least every 15 minutes.

If you have a 90GB log file, and that file is full, then yes, that will mean that the log backup will be about 90GB, provided that all sections of the log can be marked as reusable. (That doesn't take into account backup compression, which will usually give a significant reduction in the size of the backup.)

John

jg_cordero SSC Veteran Points: 268 More actions · Answer 10

John Mitchell-245523 - Friday, August 10, 2018 2:05 AM
Jose
Yes, the more often you back up your log, the smaller the log file needs to be and the smaller your log backups will be. But there comes a point at which you cease to make savings. Your log file needs to be at least the size of your largest transaction. If you perform index maintenance, for example, you will need enough space in your transaction log for the rebuild of your largest index. However, since the full backup doesn't have any impact on the transaction log, it doesn't make any difference how you schedule your log backups relative to the full backup. The log backup frequency will most often be dictated by business requirements - if the RPO (recovery point objective or the maximum amount of data in minutes you can afford to lose in the event of a disaster) is 15 minutes, then you'd better make sure you're making log backups at least every 15 minutes.
If you have a 90GB log file, and that file is full, then yes, that will mean that the log backup will be about 90GB, provided that all sections of the log can be marked as reusable. (That doesn't take into account backup compression, which will usually give a significant reduction in the size of the backup.)
John

Thanks a lot for your support John.

Best regards,

Jose Cordero