Cannot shrink log file because all logical log files are in use

Question

Cannot shrink log file because all logical log files are in use

iBar

SSCertifiable

Points: 5872
More actions
August 19, 2011 at 3:26 am

#390222

Hi,
SQL Server 2005 64-Bit standard edition
Database is stand-alone and recovery model is simple and no one is connected to the database.
I have executed the following commands to shrink the log file but its not reducing and giving this information message
"Cannot shrink log file because all logical log files are in use"
I have exectued the following steps
No inconsistencies reported by DBCC
DBCC CheckDB
-- CHECKDB found 0 allocation errors and 0 consistency errors in database 'Big_VirtualLog_DB'.
-- Tried this when database was in Full Recovery Model but no benefit.
BACKUP LOG Big_VirtualLog_DB WITH TRUNCATE_ONLY
GO
THEN
-- Disconnected all sessions and explicitly changed the recovery model to simple.
USE [master]
GO
ALTER DATABASE Big_VirtualLog_DB SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE Big_VirtualLog_DB SET READ_ONLY
GO
GO
ALTER DATABASE Big_VirtualLog_DB SET READ_WRITE WITH NO_WAIT
GO
ALTER DATABASE Big_VirtualLog_DB SET READ_WRITE
GO
USE [master]
GO
ALTER DATABASE Big_VirtualLog_DB SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE Big_VirtualLog_DB SET RECOVERY SIMPLE
GO
No Active transaction reported by DBCC
DBCC OPENTRAN
-- Now tried all these commands but still getting the same error and not reducing the log size.
USE Big_VirtualLog_DB
GO
DBCC shrinkfile('database_LogFile',EMPTYFILE )
GO
DBCC shrinkfile('database_LogFile',TRUNCATEONLY )
GO
-- select * from sys.database_files where type = 1
So WHY?
I have already studied the related questions and article on SQLServerCentral and MSDN Social but i think i am covering all in above steps. Do you have any other reason in your mind?
Thanks.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

iBar SSCertifiable Points: 5872 More actions · Answer 2

As always, SPOT ON. Great.

DECLARE @DatabaseName VARCHAR(50);

SET @DatabaseName = 'Big_VirtualLog_DB'

SELECT name, recovery_model_desc, log_reuse_wait_desc

FROM sys.databases

WHERE name = @DatabaseName

log_reuse_wait_desc is showing "REPLICATION". Because this database was restored from a backup of databases which is part of replication.

If transactions are being marked ‘pending replication’ and the Log Reader is not running, then those log records will never be marked as ‘replicated’, and the VLFs that contain those log records will never become inactive, and the log will grow.

Well now how can i remove replication from such database OR any solution to solve this situation?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

One way that I know works:

Create a publication (transactional)

Publish a single table

Delete the publication.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

iBar SSCertifiable Points: 5872 More actions · Answer 4

Well tried to create publication and got following error

A exception occurred while attempting to execute a tsql statement or batch

Can not find procedure “”

Change the database context to “XXX” . (Microsoft sql server :xxxx)

SO finally tried this and it works and now i can shrink Log and databases files.

exec sp_removedbreplication @dbname = 'Big_VirtualLog_DB'

Thank you Gail.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Something sounds broken there...

I've seen cases where the sp fails saying that the DB is not replicated, that's why I usually got the full route. Glad it worked for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OakDBA Say Hey Kid Points: 673 More actions · Answer 6

Just ran into the same issue. Cleared the hold on the log by running

sp_repldone null, null, 0, 0, 1

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

OakDBA (9/14/2011)
Just ran into the same issue. Cleared the hold on the log by running
sp_repldone null, null, 0, 0, 1

Just bear in mind that is usually a temporary fix, not a permanent one. Depending on the cause you may need to fully remove replication, not just mark the log records currently marked as pending replication as replicated.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

webtomte Ten Centuries Points: 1135 More actions · Answer 8

Hi,

I use this

First of all

BACKUP DATABASE

then

Backup Log

Do this in a normal way and put the files somewhere

Then run

USE Big_VirtualLog_DB

GO

DBCC shrinkfile('database_LogFile',1 )

1 = 1MB

Even if you stated 1 MB it will only shrink to the lowest possible value possible.

If you know that it contains 10MB for normal usage yhen set 10 instead of one.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

None of that will help when the log can't be used due to replication.

Shrinking the log as small as possible is bad practice unless you immediately regrow it to a sensible size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

webtomte Ten Centuries Points: 1135 More actions · Answer 10

I aggre but still if someone for a reason needs to shrink the files as described here.

This may do the trick.

Best practice is to never shrink log files (defragmentated discs as result - slower performance)

Keep track of your usage to prevent oversized files as due from failures in backup or ather stuff.

But....

You have to do what a mans gotta do....:w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

webtomte (9/15/2011)
I aggre but still if someone for a reason needs to shrink the files as described here.
This may do the trick.

If the reason for the log being full is replication what you suggested will not do the trick. If the log is full due to commands pending replication then those commands need to be replicated or the 'pending replication' flag needs to be removed before the log can be reused or shrunk and backups will not do that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thiyagarajan Natarajan SSC Enthusiast Points: 106 More actions · Answer 12

Hi,

I agree that , shrink is not recommended everytime. But in my case, I used to have million levels of records in tables. All the procedure will have DELETE from those tables based on the parameter. In this case, I can't use the TRUNCATE too. so while delete, Transaction log is full everytime......

So I keep on getting the log full error message. SO I need to do Shrinking everytime...

Please let me know your thoughts.

Thanks

Thiyagarajan N

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

thiyagarajann (9/15/2011)
Hi,
I agree that , shrink is not recommended everytime. But in my case, I used to have million levels of records in tables. All the procedure will have DELETE from those tables based on the parameter. In this case, I can't use the TRUNCATE too. so while delete, Transaction log is full everytime......
So I keep on getting the log full error message. SO I need to do Shrinking everytime...
Please let me know your thoughts.
Thanks
Thiyagarajan N

Delete in smaller batches and take more log backups.

Edit : Forgot the replication part, was talking about "normal" delete.

ps. One Orange Chip Points: 29422 More actions · Answer 14

Since log truncation is waiting on replication, either u create and drop the replication (as suggested by Gail earlier) or you keep on running sp_repldone at intervals. Log backups will not truncate the log file in this case.

Pradeep Singh