How to Release UnUsed space after Backup LOG?

I'm using SQL SERVER 2012

I have Strange problem
I have database with transaction log 12GB
So i made a log backup as follows:-

BACKUP LOG [MYDB]
TO  DISK = N'C:\DbBackup\MyDBLogBackup.Bak' WITH NOFORMAT, NOINIT,  
NAME = N'MYDB-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
So i executed
DBCC SHRINKFILE('MyDB_LOG', 0);

The problem is the log backup  still the same physical size "12GB"
DBCC sqlperf(logspace)
--------------------
Database Name      Log Size (MB)          Log Space Used (%)        Status
MyDB                     11954.43                  0.8721761                          0
---------------------

By the way i dont have replication to delay the trucation of log files so when i run
SELECT log_reuse_wait,log_reuse_wait_desc from sys.databases

the result comes with follows
------------------------------------------------
log_reuse_wait      log_reuse_wait_desc
2                              LOG_BACKUP

So Why the Shrinkfile command DBCC SHRINKFILE('MyDB_LOG', 0) didnt realse the unused Space was reserved by log file and been truncated by Backup LOG??? how can i release this space without switching the recovery mode temporary to SIMPLE ?
ali_alannahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
It's either go to SIMPLE or restart the SQL Services. Once SQL locks up the log like that, those are about the only two solutions.

But the other question is what is the expected size of the log? Shrinking to zero is a bad strategy over the long term. Every time the log has to expand it slows down the processing. So I would suggest picking a size that is acceptable and a monitoring task to let you know when it grows outsized.
0
Mark WillsTopic AdvisorCommented:
Just means that the logfile cannot be truncated... the shrinkfile doesn't reorganise pages, just releases space at the end if it can.

The easiest way is to use SSMS right click on the database, go into Tasks > Shrink > Files

That will pop up a new window.

Select the LOG file in the dropdown (half way down the pop-up screen) then further down, there is the option to "reorganise pages before releasing space". And set the size to something bit bigger than "currently used"

Alternatively, set to Simple. Do a full backup (that runs a checkpoint operation) and then set back to Full.

Often it is not until you get that second checkpoint operation that things start to actually happen. So, once back in full, do your normal backup and do your transaction log backup. Then schedule regular transaction log backups to keep it clean.

You don't have to take it offline while doing any of the above.

Have a read of my article : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html It says 2005 but still applicable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Oh, and check the initial size of your files - in SSMS right click on the DB go into properties and click on the "files" page.

You might want to adjust that so it reflects something more realistic (and let the system manage with regular trans log backups rather than forced shrinking and growing - that leads to fragmentation http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_691-Managing-Fragmentation-for-the-Accidental-DBA.html )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.