Solved

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

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

696 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