Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can I Shrink my SQL Server 2008 R2 Transaction files without fragmentation?

Posted on 2012-04-12
9
Medium Priority
?
547 Views
Last Modified: 2012-04-16
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!!
0
Comment
Question by:dantheanswerman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:hafeezmca
ID: 37840048
Hi,

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(<TransactionLogName>, 1)
 BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
 DBCC SHRINKFILE(<TransactionLogName>, 1)
 GO

Read this article as well.

http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37840805
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.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 37840817
My SQL Server 2008 R2 log files are not too big yet, but they will be someday
You need to size your Transaction Log so that there is no need to shrink it.  It does not get more complicated than that.

You should only shrink your data files if it is an emergency.  Is that what you are asking?  If the answer is yes, than it is quite simple:
1.  Find a window when there is little or no activity and back up the Transaction Log.
2.  Shrink the Transaction Log to the appropriate size.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1000 total points
ID: 37841162
In your question you specifically say Transaction Log files.  Truncating Transaction log files do not fragment your database.  Only shrinking your Data files will fragment your database.  Log files are circular and you should only have one, so shrinking your t-log file will only release space back to the last open transaction, but it is not going to fragment your database.
As was mentioned, you should size your t-log file so that you are not growing it or shrinking it.   The most important thing about the t-log file is that you have less Virtual Log Files (DBCC LOGINFO) and that the file be as contiguous on the disk as possible because it is circular and benefits from sequential reads and writes.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37842489
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.
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 37842602
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37844096
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:
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.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37844317
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...
0
 
LVL 2

Author Closing Comment

by:dantheanswerman
ID: 37851210
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).
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question