SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinking Transaction Log File "the right way"


Shrinking Transaction Log File "the right way"

Author
Message
distas
distas
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 202
Comments posted to this topic are about the item Shrinking Transaction Log File "the right way"
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91849 Visits: 17972
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

NOTRUNCATE
Moves 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" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)

Group: General Forum Members
Points: 369857 Visits: 46948
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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91849 Visits: 17972
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" ;-)
david.washington
david.washington
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 141
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)

Group: General Forum Members
Points: 369857 Visits: 46948
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


david.washington
david.washington
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 141
That's true. You don't need to truncate the log to defrag VLFs.
Paul Randal
Paul Randal
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12658 Visits: 1721
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
Daniel Fountain
Daniel Fountain
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1927 Visits: 890
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91849 Visits: 17972
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search