Shrinking Transaction Log Files and TRUNCATEONLY

Question

Shrinking Transaction Log Files and TRUNCATEONLY

hoo-t

SSChampion

Points: 12713
More actions
November 2, 2004 at 3:08 pm

#85710

Lately, each time I have seen a thread (here and on another forum) regarding shrinking log files, the advice is nearly ALWAYS to use the "WITH TRUNCATEONLY" option. I don't understand this! If someone has their database in full recovery mode, and is backing up their transaction logs, why on earth would they want to use TRUNCATEONLY???? Unless I'm seriously mistaken, all TRUNCATEONLY does is tell SQL "I don't want to keep these transactions, just throw them away!"
If it were me, I'd take a transaction log backup, which truncates the log while saving the transactions in the backup, THEN shrink the log WITHOUT using the TRUNCATEONLY option. This will prevent breaking the chain, keeping point in time recoverability intact.
Am I delusional? Is there some secret something going on with TRUNCATEONLY that causes it to do a better job of truncating the log? Or are people getting less than optimal advice when told to use TRUNCATEONLY?
Confused and/or Delusional in USA
aka. Steve

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

IMHO, I would say the latter in the case

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

_alexnep SSC Enthusiast Points: 188 More actions · Answer 2

Is there some secret something going on with TRUNCATEONLY that causes it to do a better job of truncating the log?

NO, there is no secrets here, at all. I've seen DBAs, which use 'FULL' recovery model on all their databases, but never create databases backup (even full backups ... not to mention transaction log backups). The only method to truncate log for them - run BACKUP LOF WITH NO_LOG (or WITH TRUNCATE_ONLY, which are synonyms)

P.S. visit our TechRepublic's SQL Server forum at http://myitforum.techtarget.com/forums/tt.asp?appid=73

Alexzander N. Nepomnjashiy

Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"

Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"

stuartb SSC Veteran Points: 268 More actions · Answer 3

You're talking about two different things here to me - shrinking the log file(s) is quite different to truncating the log.

Obviously if you truncate the log and use a recovery model that relies on the log files for recovery then you're dead if you don't backup the Db after doing a truncate log operation.

If what you are doing is shrinking the log file then in some cases you need to "rotate" the virtual logs inside the log file to allow DBCC SHRINKFILE to actaully shrink the log file. When I've had to do this I always get an application outage, lock out other users, backup the db (full) then perform the log shrink, then do another full db BU, check things out, then make the db multi-user again - sue me I'm an anal DBA - data protection is paramount to me.

I hate doing log shrinks so I generally try to design or encourage the developers to design the db and db code and operations such that the log doesn't grow to such an extent - not always easy I admit.

Regards,

Stu

_alexnep SSC Enthusiast Points: 188 More actions · Answer 4

Hi Stu,

You're talking about two different things here to me - shrinking the log file(s) is quite different to truncating the log.

yes, you are right, but originally we discuss trunkating tran. log

Obviously if you truncate the log and use a recovery model that relies on the log files for recovery then you're dead if you don't backup the Db after doing a truncate log operation.

yes!!!

I hate doing log shrinks so I generally try to design or encourage the developers to design the db and db code and operations such that the log doesn't grow to such an extent - not always easy I admit.

agree with you - shrinking the transaction log, in general indicates bad planning ...

Alexzander N. Nepomnjashiy

Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"

Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Sometimes shrinking the database and/or log doesn't happen fast enough. When you absolutely need to shrink it now, truncateonly is the best choice. But I always advocate doing a full backup, truncating, do a full backup. Even when doing log backups, this will clear the empty space from the log faster than just shrinking it.

And when your log size is 100GB, and only 2 GB is used, you may need to shrink it quicker than the shrinkfile command allows. I know I've been there after a DBREINDEX of a 180+GB database.

-SQLBill

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 6

I use the TRUNCATE option after the weekly maintenance tasks are run, because I don't care about the 2 GB of log entries that record the reindexing etc. This happens on Sundays, when nobody is using the DB, so there is no user activity to be concerned about. Immediately after that operation, I perform a full DB backup.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

hoo-t SSChampion Points: 12713 More actions · Answer 7

Frank,

I always thought that shark was just an avatar. Now I realize its really you!!!

Stu,

It IS two different things, but unfortunately related, as TRUNCATEONLY is an option within DBCC SHRINKFILE/SHRINKDATABASE. And the issue is that people are being told to use it with no regard/concern about recoverability.

Alexzander,

I think perhaps you have hit the nail on the head. Perhaps those who are suggesting using TRUNCATEONLY don't understand the difference between log truncation and shrinking.

Stu and Alexzander,

I too, agree that in general shrinking is not a good thing. But unfortunately, it is sometimes necessary, especially for the less experienced DBAs out there. I think its important to give proper advice to those who are asking.

SQLBILL,

I've never experienced the scenario that you are describing, but I'll trust your judgement that it is true. I think the key here is that you do a backup before, and again after shrinking the file. If only those people who recommend TRUNCATEONLY would explain that part of it!

Russell,

Another good point. However, you must realize that if that full backup that you take afterwards is lost/corrupt/otherwise unusable, you're stuck. If you didn't use the TRUNCATEONLY option, you would be able to use a previous full backup along with all transaction logs right past that corrupt full. I don't know if you're a Star Trek fan, but I'm kind of like Miles O'Brien, I like "secondary redundant backup systems".

Thanks to all who have responded so far. Any additonal insight? As of this post, there have been 151 views, and 6 responses. Anyone else?

Steve

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

That's not true. I'm a

However, you will see a great confusion and misunderstanding among the SQL Server community when it comes to Backup, T-Log and things like this. Unfortunately this misunderstanding is often posted as some kind of clever advise.

Now you can think of me as

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

steve block SSCertifiable Points: 7698 More actions · Answer 9

I hate doing log shrinks so I generally try to design or encourage the developers to design the db and db code and operations such that the log doesn't grow to such an extent - not always easy I admit.

Sorry for jumping in so late on this topic. What are some of the tips for designing that keep a log from growing out of control.

Thanks

bill_twomey SSCrazy Points: 2302 More actions · Answer 10

Sometimes you can not avoid expanding the transaction log. Suppose during a short maintenance window, you purge years of old data. You will expand the transaction log, even with 5 minute backup intervals for Log Shipping.

I have been wondering if shrinkdb is log-shipping friendly.

I experimented and found that Log shipping continues happily after shrinkfile under SQL 2005.

Edit: I did another experiment.

It continues to work under SQL 2000 as well.

Is it documented anywhere that dbcc shrinkfile(..., truncateonly) is log shipping friendly?

I cannot find this answer. Is it documented anywhere that shrinkfile is a logged operation?

Update, answer found:

In BOL under "Transaction Log Logical Architecture", it states that:

Many types of operations are recorded in the transaction log. These operations include:

-The start and end of each transaction.

-Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

-Every extent and page allocation or deallocation.

-Creating or dropping a table or index.

I assumed as much, but there is nothing like seeing it in the documentation.

Bill