Is It normal to keep log file larger than data file? [duplicate]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
ST_Intersects vs ST_Within
Telling potential employer that I might stay a short period
Can a public school in the USA force a 14yr old to create a Twitter account for a passing grade?
As of 2019, why do mountaineering courses still teach how to use a paper maps?
How is 懐く read in this case?
This new puzzle type needs a name
Eating Titan's oceans
Pass on your radiation
Is Two-Weapon Fighting the only way for a Horizon Walker ranger to use the Distant Strike feature to attack two different creatures?
What bit should I use to drill a two inch hole in a solid concrete wall?
Can salted butter be used to make lemon curd?
Can the Magic Keyboard be used with the USB-C to Lightning cable?
Is self-awareness or consciousness actually a evolutionary disadvantage?
70s/80s scifi movie: astronauts return to future earth
The algorithm of the new quantum factoring record 1,099,551,473,989
The default C drive is too small. How to make Windows boot from D drive
How to do a privileges escalation with ping?
How can I get 2 characters to bond while standing alternate watches?
How to prevent humanity from using alien spaceships and technology exclusively?
I filled the crucial gap; co-authors did the rest–who should be first author?
Toxic Culture - I'm putting more resources to help project move faster, but people are slowing down
Allowing comments in interactive zsh commands
If my train is cancelled and replaced, must I take the indicated replacement train when I have a Sparpreis ticket?
Why are the 4th and 7th scale degrees removed from the major scale to make the Pentatonic scale?
Is It normal to keep log file larger than data file? [duplicate]
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;
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
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
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
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
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
Oct 2 at 5:01
1
@mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
– Laurenz Albe
Oct 2 at 6:28
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
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
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.
This question already has answers here:
Shrink Tempdb log file when reaching a specific size [closed]
(2 answers)
This question already has answers here:
This question already has answers here:
This question already has answers here:
Shrink Tempdb log file when reaching a specific size [closed]
(2 answers)
sql-server sql-server-2008-r2
sql-server sql-server-2008-r2
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
edited Oct 1 at 16:09
dezso
24.5k12 gold badges65 silver badges105 bronze badges
24.5k12 gold badges65 silver badges105 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
asked Oct 1 at 15:11
Ayman FaroukAyman Farouk
1014 bronze badges
1014 bronze badges
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
Oct 2 at 5:01
1
@mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
– Laurenz Albe
Oct 2 at 6:28
add a comment
|
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
Oct 2 at 5:01
1
@mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
– Laurenz Albe
Oct 2 at 6:28
1
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
Oct 2 at 5:01
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
Oct 2 at 5:01
1
1
@mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
– Laurenz Albe
Oct 2 at 6:28
@mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
– Laurenz Albe
Oct 2 at 6:28
add a comment
|
2 Answers
2
active
oldest
votes
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.
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
1
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
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.
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 bronze badges
add a comment
|
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
1
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
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.
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
1
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
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.
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
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.
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
answered Oct 1 at 15:30
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,0781 gold badge9 silver badges19 bronze badges
1,0781 gold badge9 silver badges19 bronze badges
1
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
add a comment
|
1
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
1
1
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
– RonJohn
Oct 2 at 7:00
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.
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 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.
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 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.
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 bronze badges
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.
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 bronze badges
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 bronze badges
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 bronze badges
answered Oct 1 at 17:09
David SpillettDavid Spillett
24.7k3 gold badges34 silver badges71 bronze badges
24.7k3 gold badges34 silver badges71 bronze badges
add a comment
|
add a comment
|
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
Oct 2 at 5:01
1
@mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
– Laurenz Albe
Oct 2 at 6:28