December 30, 2004 at 6:48 am
I have a database with a 3 MB datafile, but a 25GB log file (I have no clue how it got out of control). I switched the recovery mode from Bulk-Logged to Simple, turned on Auto Shrink and then did a backup. This did not shrink the file. I then did a backup log with truncate only...still nothing. I finally tried a dbcc_shrinkfile....and nothing.
Does anyone have any idea how to shrink this log file and prevent it from getting this bloated in the future?
Thanks,
Michael
December 30, 2004 at 7:03 am
did you verify that your log is free with dbcc sqlperf(logspace)
March 2, 2005 at 2:19 am
Once I had something like that. I couldn't do this through a QA but successfully did it through an EM. But first I backed up my DB and then shrinked it with truncateonly in EM
Hope this helps
March 2, 2005 at 5:43 am
I normally run this step by step whilst connected to the DB
ran it a couple of days ago on a 100 mb db with a 5 gb log file.
took a couple of minutes to do step one, but once step three is run i have no issues ever again.
Hope this helps
backup log %DB Name% with truncate_only
sp_dboption '%DB Name% ', 'trunc. log on chkpt.'
--If last result off then
sp_dboption '%DB Name% ', 'trunc. log on chkpt.', 'on'
sp_helpdb '%DB Name% '
dbcc shrinkfile (2, 10, truncateonly)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply