Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Dec 14 '09 at 6:05

This question came from our site for computer enthusiasts and power users.

    
Yes, it's in 'full' recovery model which it needs to be, because its a mirrored database. –  Chris Dec 14 '09 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 '09 at 4:13
    
Also, 'DBCC OPENTRAN' returns 'No active open transactions.' –  Chris Dec 14 '09 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 '09 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 '09 at 0:07

5 Answers 5

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.

share|improve this answer

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.

share|improve this answer
    
I was trying to find the reference for that. You are 100% correct. –  Sean Earp Jan 7 '10 at 4:35

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

share|improve this answer
    
+1 worked Thanks! –  Booji Boy Apr 25 '11 at 20:22
    
Didn't work for me. –  Triynko Oct 14 '13 at 18:49

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

Whenever I need.

share|improve this answer

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.

share|improve this answer
    
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 '13 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.