SQL Clone
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-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1647 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 (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

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

Pooyan
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90273 Visits: 45284
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
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20618 Visits: 17244
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
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 791
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-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4887 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/
lukehuang2012
lukehuang2012
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
viduvs - Monday, October 22, 2012 9:12 AM
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

Hi Viduvs,
As a DBA. We need backup first. Then change the database to simple mode (Use SSMS, choose database, right click, choose properties, choose opition, set recovery mode to simple). Click OK. Then shrink log file. You will see the disk really free up it's storage.
Don't forget to modify database recovery mode back to Full.
I hope it can help you well. Good luck!

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