Can I Shrink my SQL Server 2008 R2 Transaction files without fragmentation?
Experts - a preemptive strike here (hopefully)
My SQL Server 2008 R2 log files are not too big yet, but they will be someday... someday sooner than I care to admit. Our "Recovery Model" is "Bulk Logged" (instead of "Simple" or "FULL").
Will his option work on a "Bulk Logged" DB?
Please First Read this post before you give me your best answer... it was a painful revelation to me (who is a big fan of the truncate) .. shrinks everything so well... but fragments your DB almost as much as possible.
I need a better way... any ideas!!
My SQL Server 2008 R2 log files are not too big yet, but they will be someday... someday sooner than I care to admit. Our "Recovery Model" is "Bulk Logged" (instead of "Simple" or "FULL").
Will his option work on a "Bulk Logged" DB?
Please First Read this post before you give me your best answer... it was a painful revelation to me (who is a big fan of the truncate) .. shrinks everything so well... but fragments your DB almost as much as possible.
I need a better way... any ideas!!
hafeezmca,
Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
There are two problems with this:
1. It is a very bad idea.
2. The author is using SQL Server 2008R2 and thankfully TRUNCATE_ONLY is no longer supported. If you had actually read the article you posted you would have seen that.
Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
There are two problems with this:
1. It is a very bad idea.
2. The author is using SQL Server 2008R2 and thankfully TRUNCATE_ONLY is no longer supported. If you had actually read the article you posted you would have seen that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Truncating Transaction log files do not fragment your database.
Absolutely. However truncating the Transaction Log file has the potential to be far more dangerous, in that you have rendered your restore plan based on the Transaction Log useless.
Absolutely. However truncating the Transaction Log file has the potential to be far more dangerous, in that you have rendered your restore plan based on the Transaction Log useless.
acperkins:
Do you mean if you run BACKUP LOG WITH TRUNCATE_ONLY? Because if you use DBCC SHRINKFILE on the T-Log you are not rendering the restore plan useless as it only releases the unused space. But you are right, TRUNCATE_ONLY will ruin the last log recovery for restores. But TRUNCATE_ONLY is deprecated and does not exist in 2005 or 2008 +.
I hope I read your answer right.
Do you mean if you run BACKUP LOG WITH TRUNCATE_ONLY? Because if you use DBCC SHRINKFILE on the T-Log you are not rendering the restore plan useless as it only releases the unused space. But you are right, TRUNCATE_ONLY will ruin the last log recovery for restores. But TRUNCATE_ONLY is deprecated and does not exist in 2005 or 2008 +.
I hope I read your answer right.
Do you mean if you run BACKUP LOG WITH TRUNCATE_ONLY?
Yes. You break the backup chain, so that unless you do an immediate Full backup you will not be able to do a point-in-time restore.
Because if you use DBCC SHRINKFILE on the T-Log you are not rendering the restore plan useless as it only releases the unused space.
Correct.
But TRUNCATE_ONLY is deprecated and does not exist in 2005 or 2008 +.
Actually it did exist in SQL Server 2005 and could be used, although it was deprecated. SQL Server 2005 BOL had the following note:
It is no longer supported as of SQL Server 2008. Thankfully.
Yes. You break the backup chain, so that unless you do an immediate Full backup you will not be able to do a point-in-time restore.
Because if you use DBCC SHRINKFILE on the T-Log you are not rendering the restore plan useless as it only releases the unused space.
Correct.
But TRUNCATE_ONLY is deprecated and does not exist in 2005 or 2008 +.
Actually it did exist in SQL Server 2005 and could be used, although it was deprecated. SQL Server 2005 BOL had the following note:
The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see View or Change the Recovery Model of a Database (SQL Server).
It is no longer supported as of SQL Server 2008. Thankfully.
Can I reframe the question?
What do I need to look at to keep my log files from getting out of hand?
Applications that are not taking frequent enough commits.
Back to back index rebuilds without intervening transaction log backups
(and I prefer a method that only rebuilds indexes exceeding a certain level of fragmentation. Run it more frequently to rebuild fewer indexes at a time and space out the transaction log activity.)
Replication activity with long lag times. (leaving transaction log records non-deletable)
Keep an eye on the above and the logs take better care of themselves...
What do I need to look at to keep my log files from getting out of hand?
Applications that are not taking frequent enough commits.
Back to back index rebuilds without intervening transaction log backups
(and I prefer a method that only rebuilds indexes exceeding a certain level of fragmentation. Run it more frequently to rebuild fewer indexes at a time and space out the transaction log activity.)
Replication activity with long lag times. (leaving transaction log records non-deletable)
Keep an eye on the above and the logs take better care of themselves...
ASKER
Thanks both of you for your excellent, informative comments... I am really taking a good hard look at these DB's and determining the best plan. Knowing a little more, my real question (which I will ask in a separate question - and give credit you guys for your hard work) ..
New question -
What is a good plan for DR (Disaster Recovery) for a MAS_500 7.4 DB?
I want to keep the transaction log and data files a reasonable size and be able to recover if needed. We are using the bulk-logged method (which I inherited).
New question -
What is a good plan for DR (Disaster Recovery) for a MAS_500 7.4 DB?
I want to keep the transaction log and data files a reasonable size and be able to recover if needed. We are using the bulk-logged method (which I inherited).
Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLog
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLog
GO
Read this article as well.
http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/