Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative valueHow to pinpoint root cause of excessive log file growthLog File Management - Space on DiskWhy can't I get the available free space after shrinking?should we keep the log file or the data file on the fastest drive?Reclaim Transaction Log space of SQL ServerTruncation of transaction logsSQL Server 2016 out of disk spaceDatabase file …_log has a max file size set to x MB. If it runs out of space, the database will stop workingDoes splitting 1 tempdb file into 4 require a SQL Server restart?
How could our ancestors have domesticated a solitary predator?
Should we release the security issues we found in our product as CVE or we can just update those on weekly release notes?
Why doesn't the EU now just force the UK to choose between referendum and no-deal?
Is going from continuous data to categorical always wrong?
How could a female member of a species produce eggs unto death?
Why does Deadpool say "You're welcome, Canada," after shooting Ryan Reynolds in the end credits?
Is Gradient Descent central to every optimizer?
Word for a person who has no opinion about whether god exists
What wound would be of little consequence to a biped but terrible for a quadruped?
Am I not good enough for you?
Execute Apex on Record Load Trigger?
Nimitta - sutta references
Fourth person (in Slavey language)
How to deal with a cynical class?
Is King K. Rool's down throw to up-special a true combo?
Making a sword in the stone, in a medieval world without magic
What to do when during a meeting client people start to fight (even physically) with each others?
Is all copper pipe pretty much the same?
Can't remove a file with file mode bits a+rw
Is "history" a male-biased word ("his+story")?
Force user to remove USB token
Decoding assembly instructions in a Game Boy disassembler
Sword in the Stone story where the sword was held in place by electromagnets
Block storage rewrites
Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?
SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative valueHow to pinpoint root cause of excessive log file growthLog File Management - Space on DiskWhy can't I get the available free space after shrinking?should we keep the log file or the data file on the fastest drive?Reclaim Transaction Log space of SQL ServerTruncation of transaction logsSQL Server 2016 out of disk spaceDatabase file …_log has a max file size set to x MB. If it runs out of space, the database will stop workingDoes splitting 1 tempdb file into 4 require a SQL Server restart?
If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully?
Is it good practice to not leave any space on the drive available, i.e. allocate it all to the log files? (The drive is dedicated to the log files in this case, data and the OS live on their own partitions.)
sql-server sql-server-2008-r2 transaction-log disk-space logs
asked 1 hour ago
J.D.J.D.
477311
add a comment |
If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully?
Is it good practice to not leave any space on the drive available, i.e. allocate it all to the log files? (The drive is dedicated to the log files in this case, data and the OS live on their own partitions.)
sql-server sql-server-2008-r2 transaction-log disk-space logs
asked 1 hour ago
J.D.J.D.
477311
You should leave some space on drive where log file resides no matter how better you calculated the space requirement, there is always a chance of a query forcing it to grow more. Why take a risk
– Shanky
1 hour ago
add a comment |
If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully?
Is it good practice to not leave any space on the drive available, i.e. allocate it all to the log files? (The drive is dedicated to the log files in this case, data and the OS live on their own partitions.)
sql-server sql-server-2008-r2 transaction-log disk-space logs
asked 1 hour ago
J.D.J.D.
477311
If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully?
Is it good practice to not leave any space on the drive available, i.e. allocate it all to the log files? (The drive is dedicated to the log files in this case, data and the OS live on their own partitions.)
sql-server sql-server-2008-r2 transaction-log disk-space logs
sql-server sql-server-2008-r2 transaction-log disk-space logs
asked 1 hour ago
J.D.J.D.
477311
asked 1 hour ago
J.D.J.D.
477311
asked 1 hour ago
J.D.J.D.
477311
asked 1 hour ago
J.D.J.D.
477311
asked 1 hour ago
J.D.J.D.
477311
477311
You should leave some space on drive where log file resides no matter how better you calculated the space requirement, there is always a chance of a query forcing it to grow more. Why take a risk
– Shanky
1 hour ago
add a comment |
You should leave some space on drive where log file resides no matter how better you calculated the space requirement, there is always a chance of a query forcing it to grow more. Why take a risk
– Shanky
1 hour ago
You should leave some space on drive where log file resides no matter how better you calculated the space requirement, there is always a chance of a query forcing it to grow more. Why take a risk
– Shanky
1 hour ago
You should leave some space on drive where log file resides no matter how better you calculated the space requirement, there is always a chance of a query forcing it to grow more. Why take a risk
– Shanky
1 hour ago
add a comment |
2 Answers
2
active
oldest
votes
There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available VLF entries.
If the log runs out of space, you'll not be able to commit any transactions until you resolve the problem. If the entire drive is consumed by the log file, the only action you can take is to add a log file on a different drive to the database; however that can be problematic if you can't actually write anything to the existing log file. Adding a log file when you're already out of log space can be impossible since the mere fact of adding a log file modifies the primary data file, which necessitates writing to the log. It's kind of a chicken-and-egg thing. If you proactively manage your log file, and never run out of log space, there is no technical prohibition from doing what you're contemplating.
Having said that, I wouldn't recommend sizing your log file to consume all available drive space:
- Windows will complain about the disk being out of space, which can be annoying.
- If you do run out of log space, and you almost certainly will at some point, the database will not be accessible.
- How much does disk space cost? Almost nothing. Ask yourself why you wouldn't leave a little space free on the drive.
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
|
show 2 more comments
Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
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/3.0/"u003ecc by-sa 3.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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232048%2fdo-i-need-to-leave-some-extra-space-available-on-the-disk-which-my-database-log%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available VLF entries.
If the log runs out of space, you'll not be able to commit any transactions until you resolve the problem. If the entire drive is consumed by the log file, the only action you can take is to add a log file on a different drive to the database; however that can be problematic if you can't actually write anything to the existing log file. Adding a log file when you're already out of log space can be impossible since the mere fact of adding a log file modifies the primary data file, which necessitates writing to the log. It's kind of a chicken-and-egg thing. If you proactively manage your log file, and never run out of log space, there is no technical prohibition from doing what you're contemplating.
Having said that, I wouldn't recommend sizing your log file to consume all available drive space:
- Windows will complain about the disk being out of space, which can be annoying.
- If you do run out of log space, and you almost certainly will at some point, the database will not be accessible.
- How much does disk space cost? Almost nothing. Ask yourself why you wouldn't leave a little space free on the drive.
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
|
show 2 more comments
There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available VLF entries.
If the log runs out of space, you'll not be able to commit any transactions until you resolve the problem. If the entire drive is consumed by the log file, the only action you can take is to add a log file on a different drive to the database; however that can be problematic if you can't actually write anything to the existing log file. Adding a log file when you're already out of log space can be impossible since the mere fact of adding a log file modifies the primary data file, which necessitates writing to the log. It's kind of a chicken-and-egg thing. If you proactively manage your log file, and never run out of log space, there is no technical prohibition from doing what you're contemplating.
Having said that, I wouldn't recommend sizing your log file to consume all available drive space:
- Windows will complain about the disk being out of space, which can be annoying.
- If you do run out of log space, and you almost certainly will at some point, the database will not be accessible.
- How much does disk space cost? Almost nothing. Ask yourself why you wouldn't leave a little space free on the drive.
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
|
show 2 more comments
There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available VLF entries.
If the log runs out of space, you'll not be able to commit any transactions until you resolve the problem. If the entire drive is consumed by the log file, the only action you can take is to add a log file on a different drive to the database; however that can be problematic if you can't actually write anything to the existing log file. Adding a log file when you're already out of log space can be impossible since the mere fact of adding a log file modifies the primary data file, which necessitates writing to the log. It's kind of a chicken-and-egg thing. If you proactively manage your log file, and never run out of log space, there is no technical prohibition from doing what you're contemplating.
Having said that, I wouldn't recommend sizing your log file to consume all available drive space:
- Windows will complain about the disk being out of space, which can be annoying.
- If you do run out of log space, and you almost certainly will at some point, the database will not be accessible.
- How much does disk space cost? Almost nothing. Ask yourself why you wouldn't leave a little space free on the drive.
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available VLF entries.
If the log runs out of space, you'll not be able to commit any transactions until you resolve the problem. If the entire drive is consumed by the log file, the only action you can take is to add a log file on a different drive to the database; however that can be problematic if you can't actually write anything to the existing log file. Adding a log file when you're already out of log space can be impossible since the mere fact of adding a log file modifies the primary data file, which necessitates writing to the log. It's kind of a chicken-and-egg thing. If you proactively manage your log file, and never run out of log space, there is no technical prohibition from doing what you're contemplating.
Having said that, I wouldn't recommend sizing your log file to consume all available drive space:
- Windows will complain about the disk being out of space, which can be annoying.
- If you do run out of log space, and you almost certainly will at some point, the database will not be accessible.
- How much does disk space cost? Almost nothing. Ask yourself why you wouldn't leave a little space free on the drive.
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
answered 1 hour ago
Max VernonMax Vernon
51.6k13114228
51.6k13114228
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
|
show 2 more comments
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
So #2 and #3 confuse me a little bit (as does Shanky's comment on my question). I believe those points apply in regards to sizing the disk drive itself but not sizing the log files. If I reduced the log files sizes to leave more free space on the disk, and the same number of transactions occur to fill up my log files such that they grow to use all the free space on the disk, then the database will just as well be inaccessible and (it had to undergo grow operations).
– J.D.
22 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
I guess the only way I see #2 applies is for the fact that you might not know which log file will grow the most and doing an even split of the whole disk drive then potentially limits the growth of one log file while another log file is wasting space. But even in that case when you don't know which log file might grow the fastest, doesn't it make more sense to make the disk large enough to leave a good buffer room for all of them and then size the log files to split up all of the disk space, instead of taking a shot in the dark which will grow fastest? (Since I agree disk space is cheep. :)
– J.D.
20 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@J.D. The point is that if you leave enough space free, you only have to deal with the file that actually grows, and if you make your growth setting sensible enough, you'll be able to deal with it before it's too late. If you just fill up the entire drive, you won't get any warning, the very first growth event will shut you down. Again, with enough free space and sensible growth settings, it's possible that growth events can happen freely without ever causing you any pain. Filling the drive is like keeping your sink completely full and not expecting any submerged dish to cause an overflow.
– Aaron Bertrand♦
10 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, So the benefit of leaving the space available on the drive allows for the growth of the log files on the drive to be monitored and then more space can be added to the drive before it runs out? I think I follow that ideology, I guess I may have had a biased view when I asked my question because my database server's log drive has much more space available then I think the database will ever realistically use and the only times I've ran out of log file VLFs were due to unusual events such as the log backups not working and the transaction log filling up the whole drive.
– J.D.
3 mins ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
@AaronBertrand, Does it make sense to shrink my log files back down then after an unusual event like that, because currently they are using about 95% of the drive? I thought shrinking was typically looked down on and unnecessary.
– J.D.
1 min ago
|
show 2 more comments
Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
add a comment |
Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
add a comment |
Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
answered 1 hour ago
Aaron Bertrand♦Aaron Bertrand
152k18293490
152k18293490
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232048%2fdo-i-need-to-leave-some-extra-space-available-on-the-disk-which-my-database-log%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You should leave some space on drive where log file resides no matter how better you calculated the space requirement, there is always a chance of a query forcing it to grow more. Why take a risk
– Shanky
1 hour ago