Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrining log file not releasing the unused space Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 10:56 PM
Points: 35, Visits: 277
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
Posted Monday, October 22, 2012 10:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 384, Visits: 1,270
-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 ...
Post #1375566
Posted Monday, October 22, 2012 11:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:54 AM
Points: 115, Visits: 398
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
Posted Monday, October 22, 2012 12:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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
Posted Monday, October 22, 2012 12:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,358, Visits: 13,688
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"
Post #1375663
Posted Monday, October 22, 2012 6:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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
Posted Tuesday, October 23, 2012 1:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:10 PM
Points: 81, Visits: 918
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
Posted Tuesday, October 23, 2012 8:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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 »

Add to briefcase

Permissions Expand / Collapse