Solved

SQL 2008 R2 Log Files Very Large

Posted on 2015-02-18
31
82 Views
Last Modified: 2015-06-29
Good day. I just noticed that there are a few DBs that have transaciton log files that are very large. Let me detailed my scenario. We have about 30 DBs. Only about 8 DBs have log files that are very large. All DBs are set to Full recovery model. We use Backup Exec 2012 to backup the DBs. We backup full on Friday, Backup Differential once a day (at 1 AM), and backup Incremental three times during the day.

We also have a maintenance plan in SQL that runs once a week a checks DB Integrity, Rebuilds Indexes,  and a Maintenance Cleanup (deleting file older than 2 months).

Autogrowth is also set on most all of these DBs (for both the DB and the log file).

I think I got all the info. Let me know if there is any further information that would be needed to provide some assistance.

Thank you
0
Comment
Question by:mig1980
  • 11
  • 6
  • 6
  • +2
31 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 40617810
Full, Differential and Incremental backups only refer to the datafiles, not the logfiles. In Full Recovery mode the transaction log needs to get backed up separately. You should see if and when that happens in the DB properties, so best to check there first.
0
 

Author Comment

by:mig1980
ID: 40617847
I checked in each of the DBs' properties and it does state (as of today) last database backup 2/14/2015 and last database log backup 2/18/2015 @ 12 PM.
0
 

Author Comment

by:mig1980
ID: 40617916
I just noticed a KB article that Backup Exec 2012 backing up the logs and truncating only increases free space but does not release the space.

What is best practices for my scenario. I ran a script that pulled all the auto growth events for the last month and it looks like there is only one DB that is throwing auto-growth events multiple times a day almost everyday.

Two issues here:

1) Better management practices for my DB and log files
2) How to decrease the file size of some logs
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 168 total points
ID: 40617957
Here's an article I wrote on fixing this situation.  As Qlemo states, you can backup full and incremental all you like but unless you do log backups your transaction log will never be able to be reduced.

Have a read of the article and get back with any questions:

http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

For getting a shrink done, probably run the log backup twice just to make sure in flight transactions are cleared.

If your log is huge you could also:

Switch to Simple Recovery mode
Shrink Log File
Put back into Full Recovery mode.
Take an IMMEDIATE full backup since your recovery chain is broken.
0
 

Author Comment

by:mig1980
ID: 40617985
This makes sense on steps in need to do for immediate resolution but the bigger problem I have is management of this going forward. Are their any beat practices or processes I should be following to manage my DB environment?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40617987
Regular transaction log backups after you get this fixed will be all you need to stop the transaction log from growing uncontrollably again.

Rather than an incremental 3 times a day, consider transaction log backups several times a day.

For example, I have one database I do a full on Friday night, incrementals Saturday night through Thursday night and half hour transaction log backups.
0
 

Author Comment

by:mig1980
ID: 40618022
But the incremental is backing up the logs. That's what I was trying to explain.  Please take a look at my comments on post this at is four posts up.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40618043
You don't state how big your log file got.

Terminology is important - you said in your question that you did a full and incremental 3 times a day - you don't mention logs.

You also don't state your transactional volume.

More frequent log file backups decrease the amount of time that the database has to write information to the logfile.

If you are moving 50GB of data every few hours (for example), then if you're only performing log backups a couple of times a day it's not unreasonable for your logfile to be 40-50 GB

Perhaps increasing the frequency of your log backups will help in stopping your log file from growing out of control too.
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 166 total points
ID: 40618596
That single DB increasing on log size all the time, examine it again for log backup.
Perform a manual one, if in doubt, and then review free log space (it should be almost no allocated log space anymore). Review after an hour, and note the difference. Then adapt your log backup strategy to that growth for that specific DB.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40618889
We use Backup Exec 2012 to backup the DBs. We backup full on Friday, Backup Differential once a day (at 1 AM), and backup Incremental three times during the day.
Looks it's a terminology issue here. SQL Server doesn't have Incremental Backups and Backup Exec doesn't have Transaction Log backups, at least by the name we are used to know. So, I'll bet that the Incremental Backup is really the SQL Server native Transaction Log backup.
If your tlogs files are growing that much, means that 3 times by day isn't enough. Usually every hour or every 2 hours would be a more standard backup plan, so it will truncate the transaction log more often.
0
 

Author Comment

by:mig1980
ID: 40619479
As I have read here and else where, I think my strategy will be to reduce the log file sizes (by shrinking them) since backing up the log files through Backup Exec 2012 only increases the free space on the file but does not release it.

Once I get the log files to adequate sizes, I will review how often auto-growth is occurring and manage that appropriately.

One thing I wanted to ask, I notice that some of my databases show auto-growth and shrink events but some do not. It looks like it has to do with the Auto-Shrink option being enabled on some databases and not on others.

Wouldn't having auto-shrink enabled cause fragmentation on the db? Is it good practice to have this enabled? Is there a way to just shrink the log file or what is the best practice to follow for this?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40619656
Auto-Shrink is bad practice, exactly because of the high fragmentation it causes. Shrink should be used rarely.
0
 

Author Comment

by:mig1980
ID: 40620157
That is what I had read. What would be good practice in order to manage this? Looking for a process I can replicate in my environment to manage this.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40620490
I don't understand what you're asking.

Databases grow - that is the nature of the beast.  Turn off auto shrink for those that have it.

Then you can let your data files automatically grow or you can manually grow them by monitoring free space.

Datafile growth is an expensive operation.  If you have the ability to manually grow a file during a low period of activity, that may be better for you and your users (depends on your system load I suppose).
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:mig1980
ID: 40621504
Thank you for the information. What I was asking is how best to manage the log files as they are not releasing the free space once backed up. For instance, I have one log file for one DB that is at 12 GB and shows only .5% in use. I know I can run a DBCC SHRINKFILE but how can I monitor this so that the log files don't get out of hand again (also any maintenance plans that would help with this).

I asking for best practices to stay on top of this.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40625222
For instance, I have one log file for one DB that is at 12 GB and shows only .5% in use. I know I can run a DBCC SHRINKFILE (...)
Yes you can and you should. Shrinking transaction log files doesn't have the same impact as shrinking data files. But if you reaching that big size is because you aren't backup often the transaction log.

(...) how can I monitor this so that the log files don't get out of hand again (also any maintenance plans that would help with this).
I already answered that: "If your tlogs files are growing that much, means that 3 times by day isn't enough. Usually every hour or every 2 hours would be a more standard backup plan, so it will truncate the transaction log more often."
0
 

Author Comment

by:mig1980
ID: 40625965
I imagine that the tlog grew a few months ago when we reopened the fiscal year for transactions. There were weeks worth of transactions in queue and I imagine that's when the few DBs that grew, grew to those sizes.

We do not process that much data in a day. We use Backup Exec to backup the DBs (as mentioned above) and I was told that even though Backup Exec backups the logs and frees space in the files, it doesn't release the space. Is that correct?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40625976
Yes, no backup will release the space that the transaction log file occupies in the disk. It only mark the space with zeros so the engine knows that can be reutilized. Shrinking is the only option to reduce the file size.
0
 

Author Comment

by:mig1980
ID: 40625985
Would it be advisable to perform a shrink of the transaction log files as part of my maintenance plan?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40625996
I wouldn't advise as part of maintenance plan. You can do it now since you're facing space issues.
If you need to shrink so often it can mean that you need more disk space (add/buy more disk) or that you're having a not so good backup plan (review your backup plan by backing up the transaction log more often).
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40626012
Since you were asking about "Best Practice" - best practice is to size your transaction log according to your transaction load.

Auto growing a Transaction Log file is an expensive operation performance wise - also growing a TLog file in small chunks can lead to you having an excessive number of Virtual Log Files inside your Transaction Log.

Shrink it now.  Then manually grow it in a controlled manner (4 to 8GB chunks, depending upon the size of the logfile)

At the bottom of the article I posted way back towards the top, there are several links to articles about how to grow your logfile in a controlled manner so you can avoid the problems that may come with an excessive number of VLFs inside your log file.
0
 

Author Comment

by:mig1980
ID: 40626013
Thank you. Would you be able to point me to a strategy I could use for a maintenance plan? I currently only Checking DB integrity (on a few production DBs), Rebuilding Indexes on the same DBs, and running a maintenance plan.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40627688
You are looking for something more than a backup plan.
I highly recommend to use Ola Hallengren's script. It's very complete and the default values should fits in 90% of the cases.
0
 

Author Comment

by:mig1980
ID: 40649665
Vitor, this looks like a great resource. It looks like the only things I would be leveraging are the Index optimization and database integrity scripts as I am not looking for anything to backup my DBs.

Are those two the only things I need to worry about when managing the database instance?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40653350
In terms of maintenance plans, yes. Those scripts are much better than the packages provided by Microsoft. And Ola's solution is used in many companies. Some large companies as well.
0
 
LVL 34

Expert Comment

by:Seth Simmons
ID: 40854572
I've requested that this question be closed as follows:

Accepted answer: 500 points for Qlemo's comment #a40617810

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40853648
While Qlemo's original post starts the answer here, there are many pieces to this answer that should be considered:

The original answer
My answer http:#a40617957 addresses how to shrink the logs
Qlemo's followup: http:#a40618596 and Vitor's followup: http:#a40618889 address further steps needed

There are several experts here who should probably be recognized in the closure of this question.

Hate to interfere with the cleanup process, but I thought it was worth mentioning :)
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40854573
Objecting to allow for a split.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40854574
Properly closing is difficult here, since there are a lot of important posts here. We've got a terminology issue (Backup Exec backup modes different from MSSQL's), a oversized transaction log, some best practice statements, and a digression into maintenance plans.

I recommend an equal split between
  http:#a40617810 or http:#a40618596 (Qlemo)
  http:#a40617957 (Steve Wales)
  http:#a40618889 (Vitor Montalvão)
because exact answers cannot be pointed out.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

VM backup deduplication is a method of reducing the amount of storage space needed to save VM backups. In most organizations, VMs contain many duplicate copies of data, such as VMs deployed from the same template, VMs with the same OS, or VMs that h…
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now