May 12, 2023 at 8:15 am
deubel_m wrote:Of course it will work. Put your database in simple mode, shrink your log file and go on. That's my daily work, works always and that's the way to shrink a log file. This is the way.
If this is your daily work - you are doing something wrong and potentially putting your organization at risk. If you ever have a problem with the current full backup and have to resort to using a prior full backup to recover you are going to find out the hard way that you are not able to recover the system to a current point in time.
And their are people out there who misunderstand postings on purpose.
The appropriate method to shrink a log file for a database in bulk/full recovery model is:
1. put the database in simple mode, full backup
2. shrink the log file,
3. put the database back in full/bulk mode, full backup
May 12, 2023 at 8:17 am
@jeffrey williams
417 VLFs showing
File Sizes between some KB and 2-7 GB
Autogrowth / Maxsize: By 10 percent, Unlimited
You should change that immediatly.
After you shrunk the logfile set an appropriate initial size for the log. If it's sized properly, it shouldn't resize at all.
May 12, 2023 at 7:17 pm
Jeffrey Williams wrote:deubel_m wrote:Of course it will work. Put your database in simple mode, shrink your log file and go on. That's my daily work, works always and that's the way to shrink a log file. This is the way.
If this is your daily work - you are doing something wrong and potentially putting your organization at risk. If you ever have a problem with the current full backup and have to resort to using a prior full backup to recover you are going to find out the hard way that you are not able to recover the system to a current point in time.
And their are people out there who misunderstand postings on purpose.
The appropriate method to shrink a log file for a database in bulk/full recovery model is: 1. put the database in simple mode, full backup 2. shrink the log file, 3. put the database back in full/bulk mode, full backup
This is not only wrong - but bad advice and isn't necessary to shrink a long file. Switching recovery mode to simple breaks the log chain - and as I stated before you cannot recover past that break in the log chain from a prior known good backup and the transaction log backups.
For example, you have a known good backup on Monday (no corruption). Monday afternoon you have a storage issue and the database is corrupted. Tuesday backup is corrupted and Tuesday afternoon you switch recovery model to simple, perform a full (as you stated), shrink the log, switch back to full and take another full backup.
Wednesday morning - before the next full backup occurs you find out the database is corrupted. You cannot restore from the Tuesday backup (corruption was backed up), nor can you recover using any of the full backups taken after that (they all contain the corruption).
How do you recover to a point in time? You can't - you have to go back to Monday's backup and restore every transaction log backup from that point forward, but as soon as you hit that break in the log chain you cannot go any further.
And as I stated - it isn't necessary to shrink a log file. Perform a shrink and the log file will shrink to the latest VLF that is in use - perform a log backup to free up the latest VLF - shrink the file again. Repeat the process until the file is the appropriate size. No need to switch recovery models.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2023 at 7:28 pm
@deubel_m log file is still growing after every shrink... so it doesnt work
Let's deal with the first issue - the autogrowth setting is incorrect. You have it set to grow 10% - which forces each growth to be larger than the previous growth and will almost certainly always be more than 1/8 the size of the file. This means each growth is going to add more VLF's than necessary - and each growth will take a lot longer than necessary.
Change the size to a fixed MB - then shrink the file to as small as you can to allow it to grow out again in that defined size. You can probably set that to 512MB.
Now, as far as the log file growing after the shrink - that is normal. It will do that until it reaches the largest size it needs to be to support the amount of transactions occurring between log backups. To reduce that you increase the frequency of log backups so the VLF's are marked as reusable sooner.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2023 at 7:57 pm
The appropriate method to shrink a log file for a database in bulk/full recovery model is:
1. put the database in simple mode, full backup
2. shrink the log file,
3. put the database back in full/bulk mode, full backup
I have to agree with Jeffrey Williams. The order in Step 1 is incorrect. Take the full backup before you change the Recovery Model.
And, as he stated (and I forgot to), it should only be used as a last resort. There are other methods that can work just find even without getting out of the FULL Recovery Model.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2023 at 8:33 pm
datkop wrote:@deubel_m log file is still growing after every shrink... so it doesnt work
Change the size to a fixed MB - then shrink the file to as small as you can to allow it to grow out again in that defined size. You can probably set that to 512MB.
Now, as far as the log file growing after the shrink - that is normal.
Really terrible approach, and therefore not normal at all. That's because growing the log file is a very expensive operation because log space must first be pre-formatted (not to mention that it locks the db while it happens).
Shrink the log file to the size you expect it needs to be. For example, say you want to allow 3GB for the (relatively small) log file. Then when you shrink the log file, use:
DBCC SHRINKFILE(2, 3072)
NOT:
DBCC SHRINKFILE(2) --WRONG!!--
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 12, 2023 at 10:09 pm
Jeffrey Williams wrote:datkop wrote:@deubel_m log file is still growing after every shrink... so it doesnt work
Change the size to a fixed MB - then shrink the file to as small as you can to allow it to grow out again in that defined size. You can probably set that to 512MB.
Now, as far as the log file growing after the shrink - that is normal.
Really terrible approach, and therefore not normal at all. That's because growing the log file is a very expensive operation because log space must first be pre-formatted (not to mention that it locks the db while it happens).
Shrink the log file to the size you expect it needs to be. For example, say you want to allow 3GB for the (relatively small) log file. Then when you shrink the log file, use:
DBCC SHRINKFILE(2, 3072)
NOT:
DBCC SHRINKFILE(2) --WRONG!!--
In this case - the OP needs to shrink the file as far as it can be shrunk to then grow back out in appropriately sized segments. If they only shrink it to a pre-defined sized then all of the very small VLF's at the beginning of the log file will still exist.
So yeah, if the log had previously been set to grow using a fixed size - and this was just to shrink the file back to a normal size after an unexpected event, I would agree. But that is not the situation here.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 13, 2023 at 2:10 am
ScottPletcher wrote:Jeffrey Williams wrote:datkop wrote:@deubel_m log file is still growing after every shrink... so it doesnt work
Change the size to a fixed MB - then shrink the file to as small as you can to allow it to grow out again in that defined size. You can probably set that to 512MB.
Now, as far as the log file growing after the shrink - that is normal.
Really terrible approach, and therefore not normal at all. That's because growing the log file is a very expensive operation because log space must first be pre-formatted (not to mention that it locks the db while it happens).
Shrink the log file to the size you expect it needs to be. For example, say you want to allow 3GB for the (relatively small) log file. Then when you shrink the log file, use:
DBCC SHRINKFILE(2, 3072)
NOT:
DBCC SHRINKFILE(2) --WRONG!!--
In this case - the OP needs to shrink the file as far as it can be shrunk to then grow back out in appropriately sized segments. If they only shrink it to a pre-defined sized then all of the very small VLF's at the beginning of the log file will still exist.
So yeah, if the log had previously been set to grow using a fixed size - and this was just to shrink the file back to a normal size after an unexpected event, I would agree. But that is not the situation here.
But you said to just "let it (auto)grow." And, again, that is a terrible approach.
Yes, if you need to shrink it all the way, then allocate more space yourself, do not wait for autogrowth to do it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2023 at 2:49 pm
More specifically, if you need to fully shrink a log, do something like this:
--assume you want log to be 3GB after shrink
USE database_name;
DBCC SHRINKFILE(2);
--explicitly increase the log to the size you want; you can do it in however many steps you want, for example:
--(Option 1)
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 1GB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 2GB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 3GB );
--OR:(Option 2)
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 1536MB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 3172MB );
--OR:(Option 3; fewest VLFs but each will be rather large)
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 3GB );
--OR:(Option 4; NOTE: this will create the most VLFs, but each will be smaller)
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 512MB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 1024MB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 1536MB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 2048MB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 2560MB );
ALTER DATABASE database_name MODIFY FILE ( NAME = [database_name_log], SIZE = 3172MB );
Log space must be preformatted. If your disk subsystem is rather slow, Options 2 and 3 may take too long on your system.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply