Mdthbs

Is sleeping on the ground in cold weather better than on an air mattress?

Can I use I2C over 2m cables?

Is It normal to keep log file larger than data file?

What determines the top speed in ice skating?

Can I color text by using an image, so that the color isn't flat?

Meaning of A-infinity relations

Rat proofing compost bin but allowing worms in

A sentient carnivorous species trying to preserve life. How could they find a new food source?

Why didn't Kes send Voyager home?

Mishna Berura Ruling on Tying Tekhelet

Translate "iconoclast" to classical Latin

How can I adjust the sequential numbering scheme when exporting Photos?

Can you pitch an outline?

Water Bottle Rocket Thrust - two calculation methods not matching

Are the names of guilds in Ravnica copyrighted?

How can we check whether the user input equal to one elements of an array?

How to find an internship in OR/Optimization?

Are there any privately owned large commercial airports?

Shuffling multiple iterators in order

Why does unique_ptr<Derived> implicitly cast to unique_ptr<Base>?

how do you value what your leisure time is worth?

Why do previous versions of Debian packages vanish in the package repositories? (highly relevant for version-controlled system configuration)

Was Wayne Brady considered a guest star on "Whose Line Is It Anyway?"

What does IKEA-like mean?

Is It normal to keep log file larger than data file?

Shrink Tempdb log file when reaching a specific sizeSQL Server Transaction Log Initial Data LoadHow best to maintain SQL log file sizesHow to pinpoint root cause of excessive log file growthTransaction log backup file larger than expected?Should I shrink the Log FileAlways On log files bigger than database filesIs it bad to have index space larger than data space?Why is my log file so massive? 22gb. I am running log backups

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{
margin-bottom:0;
}

3

1

Is It normal to keep log file larger than data file?

I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be large.. as i'm using log shipping, I normally take log backups every 10 minutes.

What I'm asking is "Is it normal to see Log file larger than data file as my data file is about 7,216 GB and my log file is about 9,930 GB?" I'm afraid there is a standard ratio between log and data file? I don't want to shrink my log file because I have enough space on my hard disk.

sql-server sql-server-2008-r2

share|improve this question

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

New contributor

Ayman Farouk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Possible duplicate of Shrink Tempdb log file when reaching a specific size
  
  – mustaccio
  14 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The only downside of large log file is that it can affect your RTO, both restore and recovery (assuming large numbers of VLFs as well). But 10 GB is probably nothing to worry about.
  
  – dean
  1 hour ago
  

  
  
  
  

add a comment
 | 

3

1

Is It normal to keep log file larger than data file?

I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be large.. as i'm using log shipping, I normally take log backups every 10 minutes.

What I'm asking is "Is it normal to see Log file larger than data file as my data file is about 7,216 GB and my log file is about 9,930 GB?" I'm afraid there is a standard ratio between log and data file? I don't want to shrink my log file because I have enough space on my hard disk.

sql-server sql-server-2008-r2

share|improve this question

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

New contributor

Ayman Farouk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Possible duplicate of Shrink Tempdb log file when reaching a specific size
  
  – mustaccio
  14 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The only downside of large log file is that it can affect your RTO, both restore and recovery (assuming large numbers of VLFs as well). But 10 GB is probably nothing to worry about.
  
  – dean
  1 hour ago
  

  
  
  
  

add a comment
 | 

Is It normal to keep log file larger than data file?

I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be large.. as i'm using log shipping, I normally take log backups every 10 minutes.

What I'm asking is "Is it normal to see Log file larger than data file as my data file is about 7,216 GB and my log file is about 9,930 GB?" I'm afraid there is a standard ratio between log and data file? I don't want to shrink my log file because I have enough space on my hard disk.

sql-server sql-server-2008-r2

share|improve this question

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

New contributor

Ayman Farouk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

New contributor

Ayman Farouk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

New contributor

Ayman Farouk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

edited 13 hours ago

dezso

23.5k1212 gold badges6363 silver badges103103 bronze badges

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

New contributor

Ayman Farouk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 14 hours ago

Ayman FaroukAyman Farouk

1611 bronze badge

2 Answers
2

active

oldest

votes

6

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.


You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage.

I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.

share|improve this answer

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

add a comment
 | 

3

It may be unusual to see log files much larger than the data in a properly configured server with well behaved applications, but it isn't wrong. SQL Server assumes that because at one time the log file needed to be grown that long that it will need that much space again so keeps it that long unless told to do otherwise.

I'm afraid there is a standard ratio between log and data file?

There is not. There are circumstances where you would expect the log file to be relatively large even without one-off large operations like the one you mention (for instance: any small database configured for full recovery, that sees a great many insert/update/delete operations over that small set of data between each log backup).

I don't want to shrink my log file because I have enough space on my hard disk.

If the operation that caused the log file to balloon is genuinely a one-off or otherwise a rare event then there would be no harm in truncating it (just truncate, not rearrange, and down to a size that still leaves plenty free for expected growth) to free filesystem space, but if you don't need the filesystem space to be freed then I wouldn't bother as the space being allocated for possible future use is not causing issues.

share|improve this answer

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges

add a comment
 | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Ayman Farouk is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f250016%2fis-it-normal-to-keep-log-file-larger-than-data-file%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

6

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.


You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage.

I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.

share|improve this answer

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

add a comment
 | 

6

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.


You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage.

I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.

share|improve this answer

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

add a comment
 | 

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.


You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage.

I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.

share|improve this answer

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

share|improve this answer

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

answered 14 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

68611 gold badge77 silver badges1717 bronze badges

3

It may be unusual to see log files much larger than the data in a properly configured server with well behaved applications, but it isn't wrong. SQL Server assumes that because at one time the log file needed to be grown that long that it will need that much space again so keeps it that long unless told to do otherwise.

I'm afraid there is a standard ratio between log and data file?

There is not. There are circumstances where you would expect the log file to be relatively large even without one-off large operations like the one you mention (for instance: any small database configured for full recovery, that sees a great many insert/update/delete operations over that small set of data between each log backup).

I don't want to shrink my log file because I have enough space on my hard disk.

If the operation that caused the log file to balloon is genuinely a one-off or otherwise a rare event then there would be no harm in truncating it (just truncate, not rearrange, and down to a size that still leaves plenty free for expected growth) to free filesystem space, but if you don't need the filesystem space to be freed then I wouldn't bother as the space being allocated for possible future use is not causing issues.

share|improve this answer

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges

add a comment
 | 

3

It may be unusual to see log files much larger than the data in a properly configured server with well behaved applications, but it isn't wrong. SQL Server assumes that because at one time the log file needed to be grown that long that it will need that much space again so keeps it that long unless told to do otherwise.

I'm afraid there is a standard ratio between log and data file?

There is not. There are circumstances where you would expect the log file to be relatively large even without one-off large operations like the one you mention (for instance: any small database configured for full recovery, that sees a great many insert/update/delete operations over that small set of data between each log backup).

I don't want to shrink my log file because I have enough space on my hard disk.

If the operation that caused the log file to balloon is genuinely a one-off or otherwise a rare event then there would be no harm in truncating it (just truncate, not rearrange, and down to a size that still leaves plenty free for expected growth) to free filesystem space, but if you don't need the filesystem space to be freed then I wouldn't bother as the space being allocated for possible future use is not causing issues.

share|improve this answer

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges

add a comment
 | 

It may be unusual to see log files much larger than the data in a properly configured server with well behaved applications, but it isn't wrong. SQL Server assumes that because at one time the log file needed to be grown that long that it will need that much space again so keeps it that long unless told to do otherwise.

I'm afraid there is a standard ratio between log and data file?

There is not. There are circumstances where you would expect the log file to be relatively large even without one-off large operations like the one you mention (for instance: any small database configured for full recovery, that sees a great many insert/update/delete operations over that small set of data between each log backup).

I don't want to shrink my log file because I have enough space on my hard disk.

If the operation that caused the log file to balloon is genuinely a one-off or otherwise a rare event then there would be no harm in truncating it (just truncate, not rearrange, and down to a size that still leaves plenty free for expected growth) to free filesystem space, but if you don't need the filesystem space to be freed then I wouldn't bother as the space being allocated for possible future use is not causing issues.

share|improve this answer

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges

share|improve this answer

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges

answered 12 hours ago

David SpillettDavid Spillett

24.2k33 gold badges3434 silver badges7171 bronze badges