How to Release UnUsed space after Backup LOG?

Posted on 2013-10-12
Medium Priority
Last Modified: 2013-12-26
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:-

TO  DISK = N'C:\DbBackup\MyDBLogBackup.Bak' WITH NOFORMAT, NOINIT,  
NAME = N'MYDB-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
So i executed

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 ?
Question by:ali_alannah
  • 2
LVL 38

Expert Comment

by:Jim P.
ID: 39568910
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.
LVL 52

Accepted Solution

Mark Wills earned 1000 total points
ID: 39569084
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.
LVL 52

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 39569086
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 )

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question