asked on

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 ?

Jim P.

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.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial