Transaction Log Shrink

[mikeyb540]

This is a strange one

I am trying to shrink my Tranasction Log
Im using DBCC SHRINKFILE (TRANSLOG1_LOG, 2)

But nothing happens says:
20MB used
800MB free
and the file LDF is 820MB

I have tried the TRUNCATEONLY option and still nothing.

I know all the transactions have been commited so why the 800MB of free space?

Txs in Advance

 

[JayKusch]

Try ...

BACKUP LOG MyDB WITH NO_LOG

Thanks

J. Kusch

 

[mikeyb540]

Hi Jay,

Thanks for the feedback but didnt work.

I also tried WITH TRUNCATE_ONLY
My SQL Server is 7.

Are these options for 2000?

 

[mikeyb540]

Any other ideas... anyone?

 

[SQLSister]

The transaction log will not shrink lowere than the original size of the log. That could be your problem if it was set up with a log that size.

 

[LeonAtRC]

I work with SQL 2000 but I've had good luck with first detaching the database. Then deleting or moving the logfile elsewhere and then reattaching the database.
The system will complain about not finding a log file so it will create a new one. The new log file always starts out at 504 KB.

BUT...do a thorough backup before attempting this. I've seen no documentation about this procedure so I'm not sure if it will work in all environments.

Good luck.
--Leon

 

[mikeyb540]

Thanks for all your feedback.

SQLSister, the log settings are dynamic so there is no limit or restrictions.

Leon, thats an option and I have done what you suggested few times. The only issues is the DB is live and I dont want to kick users out.