Shrining log file not releasing the unused space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Shrining log file not releasing the unused...

Shrining log file not releasing the unused space

Rate Topic

Display Mode

Topic Options

Author

Message

viduvs

Posted Monday, October 22, 2012 9:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, October 29, 2012 4:10 AM
Points: 34, Visits: 262

I have a database with almost 116 GB of log file size.
Earlier it was showing the available free space to 2%.
I took the full DB backup followed by log file backup (DB is in full recovery mode).
Then I shrink the log file using SQL Server Management Studio (Tasks -> Shrink -> Files and selected log file) and selected shrink action as "Reorganize pages before releasing unused space - Shrink file to 10240 MB (i.e. 10GB)".
It is still showing currently allocated space to 106 GB and available free space to 99%.
It has not released the unused space to OS at all.
Can anybody please tell me why it is not reducing the log file size?
Even the "Release unused space" shrink action is not releasing the unused space.
As per my understanding, the unused space is released to the OS after shrinking the log file. (at least after taking the log file backup)
Can anyone please answer this?

Thanks & Regards,
Vidula

Post #1375492

sql-lover

Posted Monday, October 22, 2012 10:00 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:37 PM
Points: 228, Visits: 725

-Check if you have open transactions. If that's the case, that space cannot be released.
-Try using T-SQL, instead of GUI. I do not know why, sometimes MS-SQL acts strange

*** EDIT ***

Use DBCC OPENTRAN to check for any open transaction. Not sure if you knew it already ... Wink

Post #1375566

pooyan_pdm

Posted Monday, October 22, 2012 11:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 108, Visits: 361

query the sys.databases "log_reuse_wait_desc" column.It could be for several reasons.

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008

Post #1375644

GilaMonster

Posted Monday, October 22, 2012 12:08 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:23 PM
Points: 37,723, Visits: 29,978

Because the free space is not at the end of the file. Nothing can move log records around within a log file, so if you have 20GB of empty space then 1 GB of active log then 5GB of empty space, at most shrink will reduce the file by 5GB.

You'll have to wait for the active log to be at the beginning of the file and then shrink. The 'reorganise pages' is solely for data files, not log files.

Also, don't shrink too far. You want to shrink to the usual size that the log needs to be for the transactions and log backup frequency, please read through this - Managing Transaction Logs

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1375655

Perry Whittle

Posted Monday, October 22, 2012 12:12 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 5,202, Visits: 11,155

Run a couple of log backups in succession and then try shrinking again

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1375663

Mac1986

Posted Monday, October 22, 2012 6:16 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180, Visits: 512

It happened to be few times. I use the below script and it worked for me at all Times.

Execute SP_ReplicationDbOption Adventureworks2012,Publish,true,1
GO
DBCC ShrinkFile(Adventureworks2012_log,1, Truncate only)
GO
Execute SP_ReplicationDbOption Adventureworks2012,Publish,false,1
GO

Post #1375792

mahesh.dasoni

Posted Tuesday, October 23, 2012 1:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 81, Visits: 860

1st run a log backup then run Dbcc Shrinkfile(2),
After that again run log backup then run Dbcc shrinkfile(2) again this will shrink log file.There might be active transaction which needs to be backed up before shrinking.

Post #1375861

colin.Leversuch-Roberts

Posted Tuesday, October 23, 2012 8:27 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

I never understand why there's this issue of shrinking logs - chances are that you'll need that space at some time, probably an index rebuild. The art of managing databases is to make sure you don't get autogrowths ( except under your control )
Regular log backups will help clear the content from the log. If you don't want log backups put the database into simple recovery.
As a tip you'll find the log will shrink better if you put the database into simple recovery first - but of course this will destroy your recovery but I'm assuming this probably isn't a production database.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #1376089

« Prev Topic | Next Topic »

Permissions