mig1980
asked on
SQL 2008 R2 Log Files Very Large
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
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
Qlemo
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.
ASKER
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
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.
ASKER
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Auto-Shrink is bad practice, exactly because of the high fragmentation it causes. Shrink should be used rarely.
ASKER
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.
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).
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).
ASKER
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.
I asking for best practices to stay on top of this.
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."
ASKER
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?
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?
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.
ASKER
Would it be advisable to perform a shrink of the transaction log files as part of my maintenance plan?
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).
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).
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.
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.
ASKER
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.
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.
I highly recommend to use Ola Hallengren's script. It's very complete and the default values should fits in 90% of the cases.
ASKER
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?
Are those two the only things I need to worry about when managing the database instance?
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.
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.
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.
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 :)
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 :)
Objecting to allow for a split.
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.
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.