Simon OM


I have a problem with shrinking a log file on SQL 2000. The log file has grown to 25gb (the db is 30gb) . The DB was in simple recover mode but I have changed it to full so that the log file can be backed up. I have run the dbcc shrinkfile on the log file several times (plus log file backups) and it has increased the logfile size. Each time I have run shrinkfile I get ¡®Cannot shrink log file 2 (db_Log) because all logical log files are in use¡¯ message. I have used the dbcc loginfo command and this shows that all the virtual log files are in use. The db also publishes 4 snapshot replications. Any suggestion for how I can easily shrink the log file

thanks

Simon





Re: Shrinking log file

ggciubuc


After you backup the log try to run


BACKUP LOG { database_name | @database_name_var }
WITH { NO_LOG | TRUNCATE_ONLY }
[;]







Re: Shrinking log file

Madhu K Nair

Check for active transaction run DBCC OPENTRAN. When u have active trasaction system can not trucate and shrink

Madhu







Re: Shrinking log file

Simon OM

I have run the DBCC OPENTRAN and this is what is returned.

Transaction information for database 'DB'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (30610:518:1)

When the log file is viewed in taskpad it shows that 24Gb (of 25Gb) is in use. I can only assume that replication is holding something open/active in the log file. Is there a way of releasing this without having to delete or remove replication

Thanks






Re: Shrinking log file