SQL 2008 R2 Database Log Files are not Shrinking

compdigit44
compdigit44 used Ask the Experts™
on
I have a maintenance plan that runs that does a full backup on ALL DB's and LOG files nightly. I'm running out of space on my servers and tried to run a DBCC shrink command which I found online.. "I'm not a DBA. I work for a small company and where many hats"

Anyway, I have tried all of the suggestions on how to shrick large log files ans nothing is workings.

R
The recovery mode for my DB's is Full and I have over 65+ of them to truncate / shrink why isn't my maintenace plan shrinking my log files
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Shink command doesn't actually create freespace it only shrinks the physical file if it contains freespace.  You must use the backup process to backup the log files in order to create the actual freespace.

What you should do is change your maintenance plan to replace the shink operations with  transaction log backups after the full backups each night.  You don't really want to shrink the logs unless one of them gets abnormally large, since it will have to grow again to accommodate the day's transactions.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You can't truncate logs in SQL 2008 or later.

But once the log is backed up, you can shrink the underlying file, like so:

USE <dbname>
DBCC SHRINKFILE (2)
ALTER DATABASE <dbname> MODIFY FILE ( NAME = <log_logical_file_name>, SIZE = <size_needed> )


Sometimes things can prevent log space from being re-used.  You can tell check that using this query:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM master.sys.databases

You want to see "NOTHING" in the log_reuse_wait_desc column, otherwise what's stated there is preventing the log from shrinking / reusing space.

Author

Commented:
Thanks for the reply's just for clarification I'm running a full backup on my Databases and log files nightly and not running a DB shrink...
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I ran the command:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM master.sys.databases

And it shows some of my DB's listed as LOG_Backup?

Commented:
You are running a full backup, but are you running a separate transaction log backup for each of the DBs?  The full backup does include transaction log info, but doesn't purge the committed transactions from the log which is what keeps them from growing/allows you to shrink them.  Also be aware that the transaction log backup doesn't shrink the physical file, but it will generally keep your log files at a consistent size as the transactions purged each night make space in the file for the transactions added the next day.

Author

Commented:
I am running a seperate transaction log backup but I need to shrink the log files becuase they are TO BIG!! but some of my log files are held open wit hthe LOG_BACKUP. I have tried to reboot and it doesn help

Commented:
For the DBs listed with LOG_Backup as the log_reuse_wait_desc it is indicating that you must do a log backup before you can reclaim space.  Per MS documentation you may need to do 2 log backup backups before you actually able to shrink the file.  This has to do with position of the head of log and which VLF (Virtual Log File) is active.

Commented:
For all of the DBs with LOG_Backup as the log_reuse_wait_desc you will need to do another off-schedule log backup before you will be able to shrink.

Commented:
If this problem recurs, you may need to add a transaction log backup before and after the full backup.  Usually though it is an issue of the TL getting very large due to unusual level of activity or some hiccup in the backup process.

Author

Commented:
I just ran another transaction log only backup and then the DB shrick command listed above in query analyzer.

The command completes successfully but when I check the size of the log file it still over 10GB

Commented:
Did you rerun the SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM master.sys.databases query.  Until it shows NOTHING for the DB in question, you will not be able to reclaim any space

Author

Commented:
Yes I did... no matter what I do the my log files still reminain the "LOG_Backu" state.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
IF it's acceptable in your business environment, you could temporarily set the db to "SIMPLE" recovery model rather than "FULL".  Then wait ~30 minutes, and explicitly run a CHECKPOINT in that db (just to make sure).  That should reduce the log size (eventually).  HOWEVER, you will be in danger of some data loss if the db is damaged/lost before the next full db backup.

Author

Commented:
I cannot change the recovery mode any other suggestion?

Author

Commented:
I was able to resolve this issue. Using the steps outlined here: http://stackoverflow.com/questions/646845/sql-server-2008-log-will-not-truncate-driving-me-crazy


I know this post was answered (more than) two years ago but after banging my head against the wall, I finally found what seems to be a simple answer. I thought best to post it here in case someone came across this post while having the same problem.

In my situation, I had a 650MB database with a 370GB log file in SQL Server 2008. No matter what I tried, I could not get it to shrink down. I tried everything listed as answers here but still, nothing worked.

Finally, I found a very short comment somewhere else that did work. It is to run this:
BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO

 This caused the log file to shrink from 37GB down to 1MB. Whew!


THe thing that I do not understand is why a maintenace plan that does s full DB and log file backup daily does not shrink the log files as well.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
LOL.  Yeah, I posted a shrinkfile command for you to use way back at: ID:38004618

I thought that hadn't worked for some reason.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Thanks!

Btw, you should use this command:

ALTER DATABASE <dbname> MODIFY FILE ( NAME = <log_logical_file_name>, SIZE = <size_needed> )

to increase the log size, in one go, to something reasonable, say 50-80MB.

You should also increase the growth amount to something higher, say 8-10MB.

Two reasons:
    1) Autogrow of log files pauses ALL other activity on the db until its complete
    2) Small autogrow really fragments the log file on disk and causes to many VLFs to be created, hurting performance.

Commented:
I was also under the impression that the shrinkfile command was not working for you.  As I explained in an earlier post there are 2 separate operations.  1) transaction log backup which releases the committed transactions allowing the space to be resused, but does not change the physical size of the file; 2) shrink operation which reduces that size of the file, but requires there to already be free space in the log as a result of the transaction log backup.  

Normally you don't need and actually shouldn't shrink the transaction log file if you are doing regular transaction log backups, because the new transactions will just fill the reusable space you reclaimed by doing the previous backup.  Generally, with regular transaction log backups you will hit a "normal" size for a given log and it will just stay at that size indefinitely.   If you shrink the file, it just has to regrow to accommodate the next set of transaction data.  This shrink/regrow process is very inefficient.  However, if the transaction log backup has not run for some period of time or if you have significantly higher than normal activity so that the log is bigger than "normal" the shrink operation is useful.

Author

Commented:
thanks I believe the issue was my lake of experience.. A.K.A user error... ;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial