Discussion Locked

how to shrink a SQL logfile

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How to shrink the logfile of my SQL 7.0 database?
DBCC SHRINKFILE and DBCC SHRINKDATABASE does not seem to work.

7 replies

by mark.thomson

This generally works for me -

In the Enterprise manager right-click the database whose logfile you want to shrink
then click "all task" followed by "Truncate log" then follow the prompts

Then Shrink the database then truncate the log (as above) then shrink the database

In addition to this you might want to set the "Truncate Log on Checkpoint" in the "Options" tab in the Database properties dialog to prvent the logfile from getting too big althought there are reasons why you might notwant to do this

by miket

Depending on your situation:
1) If your database is set to "Full" or "Bulk" recovery model, then doing a regular backup of the logfile should keep it truncated.

2) If you don't need to keep a copy of the logfile for recovery purposes, set your database to use the "Simple" Recovery model and your log will truncate after each transaction is completed.

Mike T.

This discussion is closed to new replies.