4

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?

Improve this question
5
  • Yes, it's in 'full' recovery model which it needs to be, because its a mirrored database.
    – Chris
    Dec 14, 2009 at 4:11
  • 1
    And yes. i've told our DBA's to make sure they do daily transaction log backups in future!
    – Chris
    Dec 14, 2009 at 4:13
  • Also, 'DBCC OPENTRAN' returns 'No active open transactions.'
    – Chris
    Dec 14, 2009 at 4:24
  • Okay, half an hour later the database (in the filesystem) just shrunk of its own accord. I can' figure out why it did that. So i guess i'm even more confused now!
    – Chris
    Dec 14, 2009 at 4:24
  • 1
    look at the log_reuse_wait_desc in sys.databases this is what your log is waiting on.
    – Nick Kavadias
    Dec 15, 2009 at 0:07

5 Answers 5

Reset to default
3

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

Improve this answer
2
  • Didn't work for me.
    – Triynko
    Oct 14, 2013 at 18:49
  • This did work for me with a caveat. Forced a single transaction through, THEN did the log backup. Shrank our out-of-control log from 200GB to 250MB.
    – finlaybob
    Sep 18, 2019 at 11:58
1

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.

Improve this answer
1
  • I was trying to find the reference for that. You are 100% correct.
    – Sean Earp
    Jan 7, 2010 at 4:35
1

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.

Improve this answer
1
  • Glad I'm not the only one. I've tried shrinking this 3Gig file in ever conceivable way and nothing works. Numerous transaction log backups, with and without copy option, tried the shrink immediately after, a while after, with and without updating the database, tried lowering the initial size of the log, restarted the database, nothing works. File is stuck at 3GB despite having 95% free space. What a waste of time.
    – Triynko
    Oct 14, 2013 at 18:57
0

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.

Improve this answer
0

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

Whenever I need.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .