Shrinking a DB Log File

[n2ckb]

Hi guys,

(SQL Server 7)

I have been struggling to reduce the size of a database log file that is currently over 5Gb. To cut a long story short, I have taken over a database that had a full backup performed once a day. No transaction log backups had been carried out up until I added them as scheduled jobs every two hours. We are running low on disk space and would like to reduce the log file down to a reasonable size. I have searched the site and the rest of the web, and carried out all of the instructions, but to no avail. Should I uncheck the 'automatically grow file' checkbox to try and force the logfile to move to the start of the file. (status 2 is currently at the end after running DBCC Loginfo. If it continually grows, does it ever return to the start?
If anyone has any ideas that would be a great help. If the worst comes to the worst, would you recommend taking the server down and detaching the db, renaming the log file and reattaching the db?

Thanks for the help.

Nick

 

[n2ckb]

After much fiddling, we managed to reduce the log file by using a script found on the web. This did not work originally, but after backing up the db, turning off the grow file automatically option and then running the script, it worked! This may be of use to someone in the future if they search and find this thread.

Thanks

Nick

 

[ClaireHCM]

First Issue Checkpoint
Then do
Backup log DBName with truncate_only
Then Do
DBCC shrinkfile 'log file logical name'

Example:
use MyDatabase
go
Checkpoint
Go
Backup log MyDatabase with Truncate_Only
go
DBCC Shrinkfile 'MyDatabase_Log'
Go