SQL 2008 R2 Database Log Files are not Shrinking

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.

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

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?

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.

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

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.

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.

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.

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

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

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

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.

I cannot change the recovery mode any other suggestion?

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.

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.

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.

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.

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