[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL transaction log growth during maintenance plan

Posted on 2011-09-06
8
Medium Priority
?
584 Views
Last Modified: 2012-05-12
I have a reasonably large database (25GB) with a backup plan:

Full database backup daily at 10pm
Transaction log backup every 30minutes between 8am - 9.30pm

Additionally there is a maintenance plan which runs at midnight.

When the maintenance plan runs the transaction log grows to about 6GB.
Why is this? Could it/should it be avoided?

When the transaction log backups start in the morning, I "think" the log is cleared down (when I checked at 9am it was 98% free space) but after several hours (a few more transaction log backups) it eventually shrinks back to 10MB (and will remain roughly this size throughout the day).
Why is this? Could it/should it be avoided?

Thanks

Lee
0
Comment
Question by:tickett
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36487862
What your maintenance plan does? - What steps are included in it?

There is SHRINK step - it decreases LOG SIZE.
0
 
LVL 11

Author Comment

by:tickett
ID: 36487906
Check Database Integrity
Reorganize Indexes
Update Statistics
Shrink Database
Clean Up History

And the option is set to "Return freed space to the operating system".
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36488376
As you backup your transaction log every half an hour, it's probably at very low size all over the day, as it get shrinked after the backup.

However, when you run your maintenance plan, lots of operations get made and are pushed to the log.

I would try to run the maintenance plan operations separately seeing the growth of the transaction log between each one of them. If there is any of the operations specially hard with operations on transaction log I would try to make a log backup after that operation.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 11

Author Comment

by:tickett
ID: 36488932
I think I'm more looking for a best practice solution? It almost makes sense why it is happening (although it definitely doesn't seem right- surely SQL is designed better than that!?). This would be a common issue and there must be a common solution/best practice approach?

L
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36489145
Sincerely I don't know, I'm not a dba, only have some knowledge of it.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 36489677
You might rethink the reorg indices nightly.  They might be happy with a weekly index maintenance depending on the database volatility.

And regardless, don't worry about shrinking the database each night.  You need sufficient disk space for the max size each day anyway, so you might as well let it stabilize at the larger size.
0
 
LVL 11

Author Comment

by:tickett
ID: 36501956
Thanks Bardobrave

mastoo: can you state a source? i think what you're saying is probably right but was hoping for more reasoning/documented practice.

I have found scripts in the past which identify certain indexes based on the number of pages and fragmentation- so it might be that i need to drop the maintenance plan and actually run my own custom script to only process the tables/indexes which need to be improved.

L
0
 
LVL 21

Accepted Solution

by:
mastoo earned 2000 total points
ID: 36502037
A source as to frequency of index maint?  If we can count microsoft mvp discussions as authoritative, this discusses it and has a few good links itself:

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/aecc91b8-d0b8-4567-9c01-155c5c28306c/

and yes, tailoring your index maint can reduce the impact but is just slightly more work
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

650 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