Solved

Index Maintanance Plan - with DR site log shipping ( Off site )

Posted on 2014-04-07
3
239 Views
Last Modified: 2014-09-07
Hello ,

I have set up a Index Maintenance plan that runs every Sunday that is using Ola Hallengren's  scripts. This works brilliantly except that it is increasing the LDf file ( which it does normally from Rebuilding index's where needed ) and for me to log ship this file would be 50 GB which if i had to do would take probably around  14 hours. I have also tried to use the "Sort In Tempdb" which i thought would use that instead but not still growing the LDF of database that is been indexed.

Can anyone please advise how i can keep log shipping going for this maintenance schedule, as i have to set up a agent job for DBCC SQLPERF (LOGSPACE) and   DBCC SHRINKDATABASE then copy the database after a backup to DR site.

Kind regards
D
0
Comment
Question by:deanmachine333
[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
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39985800
I have set up a Index Maintenance plan that runs every Sunday that is using Ola Hallengren's  scripts
Why are you using the Index Maintenance Plans to run Ola Hallengren's Indexing scripts.  Ola's scripts are meant as a replacement of the Maintenance plans.

This works brilliantly except that it is increasing the LDf file
That would be because you are using the default Full Recovery Model and not backing up your Transaction Log.  You need to setup a job to do frequent Transaction Log backups using something like Ola's backup scripts.

Once you have that in place we can talk about doing a one-time only shrink of your Transaction Log.
0
 

Accepted Solution

by:
deanmachine333 earned 0 total points
ID: 40300495
I have set up log shipping - so have the Transaction logs backing up while this is happening but it still bloats up the log files and then instead of the bak file being huge its the transaction log.

i have just bit the bullet and sending bak file across to DR site which takes a while but still works out smaller and quicker than using the trn files while the maintenance is happening.

thanks
0
 

Author Closing Comment

by:deanmachine333
ID: 40308390
I have set up log shipping - so have the Transaction logs backing up while this is happening but it still bloats up the log files and then instead of the bak file being huge its the transaction log.

i have just bit the bullet and sending bak file across to DR site which takes a while but still works out smaller and quicker than using the trn files while the maintenance is happening.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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