Solved

How to shrink SQL 2005 transaction log of a database that is Primary DB in Log Shipping set up

Posted on 2009-05-12
9
828 Views
Last Modified: 2012-06-21
According to BOL, when using Log Shipping, backing up the transaction log independantly of the log ship process will disrupt thel restore process on the secondary server. (which makes sense).

As a transaction log back up followed by a file shrink - is the method used to shrink bloated transaction files. How does one shrink the tranaction log  goal if log shipping process is running?

Thanks
0
Comment
Question by:DavidHannen
[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
  • 5
  • 3
9 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24363787
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24363791
You can get your answer in this thread where I have given the commannds as to how to shrink it.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24398614.html

Hope this helps.
0
 

Author Comment

by:DavidHannen
ID: 24363929
Thanks both for the posts:

My question is NOT 'How to shrink the transaction log' I have that covered. My issue is that I may need to shrink the log of a database that is primary DB in a Log Ship Set up. The documentation says Do Not  back up the log in this instance as it will interupt the chain of logs in the restore on the secondary server.

How do I get around this scenario.

Thanks
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 6

Expert Comment

by:bull_rider
ID: 24364045
You can set your secondary database's Recovery Model to SIMPLE which does not break\effect the log chain, at least I haven't seen a side effect of this setting for the secondary database. (I assume you do not back up your transaction logs on the secondary database, because you can not do this when
your database's RM is SIMPLE.)

I hope this helps. Let me know if you have any more questions.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24364058
And can you reduce the time for the log shipping so that it happens frequently and does not over grow the log file? This is a preventive measure. :)
0
 

Author Comment

by:DavidHannen
ID: 24364193
Bull Rider,

Am I right in saying that when a transaction log is backed up (in full recovery model) , the log is automatically truncated to the point of the last record being sucessfully applied to the database file.

If I am right with this, does that mean that shrinkfile could be used straight after the transaction log ship as there will be space in the file. Am I anywhere near correct here?

Thanks
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24364268
A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.

Shrinkfile might interrupt the chain of logs as you said and I have seen many instances of that and it really creates a havoc. Please follow what I said in the previous comment.
0
 
LVL 6

Accepted Solution

by:
bull_rider earned 500 total points
ID: 24367736
Did it solve the issue?
0
 

Author Closing Comment

by:DavidHannen
ID: 31580498
Thanks very much for your input - much appreciated. I'm still learning and trying to assimilate best practice in all these different scenarios.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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