Home / server / Questions / 94052
In Process
Chris
Asked: 2009-12-14 20:10:18 +0800 CST

SQL will not shrink a log file even though it is 99% free space, gives 'all logical log files are in use' error

  • 772

I've got a log file which has grown to 32gig and filled my hard drive. So i've done a transaction log backup, and now when i do "DBCC SQLPERF ( LOGSPACE )" it says that 99% of my log file is empty space which is great:

DBCC SQLPERF ( LOGSPACE )
Database Name   Log Size (MB)   Log Space Used (%)  Status
abc             32140.02        0.3069714       0

Now i want to shrink the file (it should be only a couple of megs!!!), so i do:

DBCC SHRINKFILE ( abc_log )

In the query results in SSMS, i get the following:

Results tab:
DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
14  2   4113923 128 4113920 128

And in the 'Messages' tab, this little piece of info:

Cannot shrink log file 2 (abc_log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What's going on?

sql-server

5 Answers

  1. Wait for a quiet time and do a transaction log backup and then immediately do the shrink.

    • 3

  2. You did not mention your SQL Server version. According to "SQL Server Intenals", " (in 7.0) ... Physical shrinking can take place only from the end of the log, and the active portion is never shrinkable. To remedy this situation, you had to enter some dummy transactions after truncating the log to force the active part of the log to move around to the beginning of the file". Later version should do that for you.

    The shrinking after half an hour could just be the auto-shrink (did you just turn that option on?) kicking in. It does that every 30 minutes.

    • 1

  3. I'm a bad SQL admin and usually just change the db to the simple recovery model, shrink and then change it back to Full. I guess this wouldn't work in a situation where you're relying on Full for the mirroring.

    • 1

  4. That is weird, but perhaps there was some DB operation going on at the time, even though there were no active "transaction"? I don't know SQL server well enough. I doubt that it had anything to do with free space, anyway.

    You might have more luck with questions like this on serverfault.com than on SU.

    • 0

  5. I do have have a procedure in my site that does just that.

    Whenever I need.

    • 0