Maintenance Plan in MS SQL 2014 to shrink only log file

Hi

I am assisting a customer to manage their SQL database. So far there are no issues except that the SQL Log file is growing at a fast rate even thought I have a mainteinace plan to backup the Transaction Log file every week.

I can help to shrink the log file manually. However, the customer would not like me to access their server regualrly just to do this task. I was thinking of using a shrink database maintainance plan but it will shrink their database file which is not what I want.

Is there a way to change the script of a maintenance plan in MS SQL 2014, I would like to change it so that log file is shrank not the database file? If not, is there a method to schedule a procedure to run on a weekly basis?  

Please do not suggest setting the database server to Autoshrink the log file which has affected their operations. I am also not allowed to write a batch file to run the with the Windows scheduler as it is not an approved process.

Thank you,
NaviWorldSGAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I have a mainteinace plan to backup the Transaction Log file every week.
Every week? Every week should be at least for a Full Backup. A TLog backup should occur several times by day. If you do that then there will be no need for shrink the transaction log.
You should review the backup plan. If I would be your client I wouldn't be happy at all.
Brian CroweDatabase AdministratorCommented:
I agree with Vitor.  If you are only doing a transaction log backup weekly then why bother with FULL recovery model?  A typical backup plan for a production environment in my experience is a weekly full backup, daily differential backups, and transaction log backups every 15 - 60 minutes.

Growing the log file is one of its most expensive actions which means that every time you shrink it you are forcing it to regrow to its needed size each cycle.  Generally you should not have to shrink a log file as it will reach an equilibrium of its own based on how often you back it up.

https://msdn.microsoft.com/en-us/library/ms365418.aspx
David ToddSenior Database AdministratorCommented:
Hi

And of course the repeated grow/shrink on the log file(s) creates a huge number of vlf's. Too many vlf's can be a performance issues. The cure is to firstly pick a reasonable size for the log, then shrink it to as small as possible. Then alter the size to the above agreed upon figure.

If your system is so big that you can't afford the time or whatever for daily backups, then you'll need to do this out of hours ... [insert standard dba disclaimers here ...]

For small to medium databases, I start with daily full backup and hourly transaction log backups, and use Ola Hallengren's free script. http://ola.hallengren.com/

HTH
  David

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NaviWorldSGAuthor Commented:
Hi

>>Every week should be at least for a Full Backup
The customer is doing their own backup with their backup software.

>>You should review the backup plan
The backup solution is not in my hands. I asking about about how to keep the size of the log file at a reasonable size. I am having a weekly backup of the Transaction log file to manage the size of the transaction log but it does not appear to be managing the transaction log size.

 >>If your system is so big that you can't afford the time or whatever for daily backups
I will ask the customer if they can live with more frequent transaction log backups.

Thank you,
Vitor MontalvãoMSSQL Senior EngineerCommented:
The customer is doing their own backup with their backup software.
That's not an excuse to do not perform a good backup plan. All backup softwares accept any plan but they are doing with we tell them to do.

The backup solution is not in my hands. I asking about about how to keep the size of the log file at a reasonable size. I am having a weekly backup of the Transaction log file to manage the size of the transaction log but it does not appear to be managing the transaction log size.
But you're assisting them or not? If you are you should prevent them for this situation.You have two solutions:
1 - Change the backup plan to perform several transactional log backups by day so it will truncate the TLOG more often avoiding it to grow.
2 - Change the Recovery Model of the database to SIMPLE so the transaction log will not be used for all operations reducing the possibility to grow. The bad side of this option is that they wouldn't be able to restore to a point in time if they need it.
David ToddSenior Database AdministratorCommented:
Hi,

 >>If your system is so big that you can't afford the time or whatever for daily backups
> I will ask the customer if they can live with more frequent transaction log backups.

Cart before the horse I'm afraid. Get them doing more frequent transaction log backups and daily full backups, and see if you can then reduce the size of the transaction log.

Backups can be to lower tier disk systems.

Ask them how much data can they afford to loose. At the moment they can easily loose a weeks data. That might be enough to close their shop.

So, its not IF they CAN afford more frequent backups, I suggested that they CAN NOT afford the awful backup schedule they currently have.

HTH
  David
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.