Solved

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

Posted on 2014-04-07
3
238 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

729 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