Avatar of compdigit44
compdigit44
 asked on

SQL 2008 R2 Database Log Files are not Shrinking

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
Microsoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
compdigit44

8/22/2022 - Mon
jamesrh

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.
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
compdigit44

ASKER
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...
compdigit44

ASKER
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jamesrh

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.
compdigit44

ASKER
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
jamesrh

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jamesrh

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.
jamesrh

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.
compdigit44

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
jamesrh

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
compdigit44

ASKER
Yes I did... no matter what I do the my log files still reminain the "LOG_Backu" state.
Scott Pletcher

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
compdigit44

ASKER
I cannot change the recovery mode any other suggestion?
compdigit44

ASKER
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 Pletcher

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Pletcher

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.
jamesrh

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.
compdigit44

ASKER
thanks I believe the issue was my lake of experience.. A.K.A user error... ;-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.