Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrining log file not releasing the unused space


Shrining log file not releasing the unused space

Author
Message
viduvs
viduvs
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 284
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
sql-lover
sql-lover
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 1930
-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 ... ;-)
pooyan_pdm
pooyan_pdm
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 422
query the sys.databases "log_reuse_wait_desc" column.It could be for several reasons.

Pooyan
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44389
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, MVP, M.Sc (Comp Sci)
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


Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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" ;-)
Mac1986
Mac1986
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 787
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
mahesh.dasoni
mahesh.dasoni
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 1025
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.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search