October 15, 2012 at 11:43 pm
Comments posted to this topic are about the item Shrinking Transaction Log File "the right way"
October 16, 2012 at 4:21 am
Your article mentions
distas
Here is important point in regards to DBCC SHRINKFILE not to be misinterpreted: DBCC SHRINKFILE (<Transaction_LogFile_LogicalName>, TRUNCATEONLY) does truncate a transaction log file, i.e. breaks the LSN - Log Sequence Number. This will result in inability to restore database up to the point of failure, if the full or differential database backup is not taken right after and database ...oops... fails after this transaction log file truncation operation.
But I think you have misinterpreted, this command (which is no longer available in SQL Server) will truncate the log and break LSNs
BACKUP LOG ... WITH NO_LOG or TRUNCATE_ONLY
The options TRUNCATEONLY and NOTRUNCATE for DBCC SHRINKFILE do not apply to transaction log files, they are applicable to data files only.
This link details the following
Books Online
NOTRUNCATEMoves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.
NOTRUNCATE is applicable only to data files. The log files are not affected.
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 16, 2012 at 4:33 am
Here is important point in regards to DBCC SHRINKFILE not to be misinterpreted: DBCC SHRINKFILE (<Transaction_LogFile_LogicalName>, TRUNCATEONLY) does truncate a transaction log file, i.e. breaks the LSN - Log Sequence Number.
No , it does not. Shrinking never breaks the log chain. That 'truncateonly' means to only release free space at the end of the file not to shuffle data pages around (emphasis data pages, it's ignored when shrinking a log).
The only things that break the log chain are:
- Switch to simple recovery
- Backup log with truncate only (SQL 2005)
- Reverting from a database snapshot
- Rebuilding the log
What you have stated there is a prevalent and very irritating myth. If you'd tested it, you'd have seen that the shrink does not break the log chain.
Instead, backup the transaction log file: (SQL Server 2008 Standard Edition, Enterprise Edition / SQL Server 2008R2 any addition)
BACKUP LOG <DatabaseName> TO DISK = N'<drive>:\<backup_folder>\<database_name>LogBackup.trn' WITH NOFORMAT, INIT, NAME = N'<DatabaseName> - Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, COPY_ONLY, STATS = 1;
A copy only log backup won't free any space in the log, so I don't known what the point of running this is.
Also might like to mention that statement is Enterprise only, because of the compression.
then execute
DBCC SHRINKFILE(<Transaction Log File Name (logical)>, 1)which will shrink the T-Log to to its last used extent.
Log files don't have extents. It'll shrink by removing any inactive VLFs that are at the end of the file. If the last VLF in the file is part of the active portion of the log, it won't shrink at all.
Shrinking as small as possible is a horrid thing to do, unless you intend to immediately grow the log file again. If the idea is just to shrink the log after unusual growth, then it should just be shrunk to normal size. See Managing Transaction Logs[/url]
Warning: Backing up the transaction log file prior to its shrinkage is required because in the total majority of situations, the T-Log file won't shrink because of active segments either at its tail or towards it. Those ones are "freed-up" exactly by running transaction log file backup
Except that you said to run the log backup with copy_only, which won't free anything. Even a normal log backup won't free VLFs that are active and at the end of the file if they're needed for something else (transaction, replication, mirroring, etc). Shrink cannot move log records around the log.
In this kind of situation, when you're getting 9002 errors and database is in "Suspect" state, there is really no time to wait for transaction log file backup completion before you can proceed with its shrinking,
A full log will never send the database suspect (if it ever does, you've hit a critical bug). A full log makes the DB read only, that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2012 at 6:04 am
i think the user was primarily confusing NOTRUNCATE and TRUNCATEONLY on DBCC SHRINKFILE with the BACKUP LOG scenario, i could of course be wrong but i'm sure this is the case 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 16, 2012 at 7:30 am
Great article. However, I think I would have pointed out one more point under your bulleted list
AVOID TRUNCATING TRANSACTION LOG FILE ON PRODUCTION ENVIRONMENT BY ALL MEANS BECAUSE IT BREAKS LSN, unless:
o You have a very high VLF count which is an indicator that your Transaction Log is fragmented. This fragmentation will cause performance issues.
You should also mention something about growing your transaction log in chunks once you have it truncated so that it doesn't fragment again.
Reference Kimberly Tripp's article at http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
October 16, 2012 at 7:48 am
No need to truncate the log and break the log chain to fix fragmentation. Pick a time the DB is not heavily used (not used at all is better) and the active log is at the beginning of the file. Take a log backup, shrink the log to 0, grow it back to the desired size in however many chunks you calculate for the VLFs you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2012 at 7:56 am
That's true. You don't need to truncate the log to defrag VLFs.
October 16, 2012 at 9:06 am
My goodness - what a load of utter nonsense in this article.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 16, 2012 at 9:25 am
Apart from the other stuff that has been said i would also suggest that from what i have seen the number one cause of the log file becoming big is that someone has left SQL on default settings and they dont know what there doing.
So they should either
A) Put it in simple - if that is what is required by the business\system. 99 times out of 100 i have seen systems that they want backed up nightly, and that is sufficient loss - but the database is in full mode.
B) If on the rare occasion they do want more then nightly (sometimes you get "oh can you do that") then they need to be backing up the logs and maybe doing diffs. This is rare as people who normally want this backup regime know enough to actually know they need to implement it.
Most times its a pop it in simple and the LSN`s are irrelevent to the backup regime they have.
Dan
October 16, 2012 at 11:58 am
david.washington (10/16/2012)
Great article.
just out of interest which article were you reading?
Paul Randal (10/16/2012)
My goodness - what a load of utter nonsense in this article.
I actually didn't want to be that brutal, it does look like their first article. What was disappointing to me was that the authour linked in 3 separate authors (one of them yourself) and still managed to wander off base.
What i would want the author to take away from this is to always fully research your article and if you're unsure then have someone peer review it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 16, 2012 at 12:13 pm
Perry Whittle (10/16/2012)
What i would want the author to take away from this is to always fully research your article and if you're unsure then have someone peer review it
I'll go as far as to say always have the article peer-reviewed, especially if you're sure it's right (cause that's when you make the biggest mistakes). Every article I've had published here in the last 3 or so years has been reviewed by a couple people, and every time they found mistakes. Usually minor mistakes or missing explanations, but still stuff that needed fixing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2012 at 2:34 pm
I might be missing something so I'm hoping someone can clear it up.
I have used TRUNCATEONLY on log files in SQL 2008R2 before to remove VLF fragmentation. I double checked it against SQL 2012 and it seems to work still.
DBCC SHRINKFILE(database_log, TRUNCATEONLY);
I'm wondering if this is a BOL mistake, deprecated feature, or if it was never supposed to work.
October 16, 2012 at 3:07 pm
Books online states that if no target size is specified, a shrink-file operation removes only the inactive virtual log files beyond the last active virtual log file in the file. Depending on where the last active VLF is it could shrink a little or a lot, this is what you are seeing.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 16, 2012 at 3:11 pm
No matter what size or options you specify, shrinking a log file can only ever remove inactive VLFs, and only from the end of the file.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 16, 2012 at 3:17 pm
JeremyE (10/16/2012)
I might be missing something so I'm hoping someone can clear it up.I have used TRUNCATEONLY on log files in SQL 2008R2 before to remove VLF fragmentation. I double checked it against SQL 2012 and it seems to work still.
DBCC SHRINKFILE(database_log, TRUNCATEONLY);I'm wondering if this is a BOL mistake, deprecated feature, or if it was never supposed to work.
Sure, it works. What books online says is that the truncateonly option only applies to data files, so for a log file that's equivalent to
DBCC SHRINKFILE(database_log);
as the truncateonly option is completely ignored
That means shrink the file as small as possible (given the current active VLFs) to the minimum of the size it was when it was created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply