September 16, 2003 at 3:05 am
Hi,
I have a database that has a biggish transaction log.Would there be any safe way that I can reduce the size of the log, without affecting the actual data file of the database?
many thanks,
yogi.
September 16, 2003 at 7:14 am
1. Do a full backup and then backup the transaction log.
2. Truncate the transaction log.
3. Shrink the transaction log.
None of the above will affect the actual data file of the database. Which you use depends on what kind of recovery you want to be able to do.
If you want to be able to recover to a 'point-in-time', you need to have backups of the transaction logs.
If you just want to 'free up' some space in the log, shrink it. (DBCC SHRINKFILE)
If you don't care about the transaction log and just want it gone, truncate it. (BACKUP LOG databasename WITH TRUNCATE_ONLY).
IF YOU TRUNCATE IT, IMMEDIATELY DO A FULL BACKUP. If you don't care about restoring to a 'point-in-time' and you never will care about that, change the Recovery Mode to SIMPLE.
-SQLBill
September 16, 2003 at 9:09 am
Hi Bill,
cheers for the reply.
In my scenario I do not presently need the transaction log.(but I will need it in the future for 'point-in-time' recovery.)
so, I have now backed up both the data file and the log file.
Would I be correct in understanding that the best thing for me to do now would be:
1)<BACKUP LOG databasename WITH TRUNCATE_ONLY>
2) <IMMEDIATELY DO A FULL BACKUP>
cheers,
yogiberr
September 16, 2003 at 10:21 am
You have two options now:
The one you state. Except then if you need to do a restore, you'll be doing it from the latest full backup (which doesn't have any transaction log backups at this time). That's okay, but don't forget to start doing transaction log backups on a regular basis.
Another option is to use the command DBCC Shrinkdatabase or DBCC Shrinkfile.
DBCC Shrinkdatabase will shrink BOTH the data and log files. It will also truncate any empty space.
DBCC Shrinkfile will shrink whichever file you specify. It will truncate only that file.
Check out these options in the Books OnLine.
I suggest using the DBCC SHRINKFILE and shrinking just your log file (if the data file is okay with it's size).
-SQLBill
September 16, 2003 at 4:16 pm
hi Bill,
Many thanks for the advice.I appreciate it.
yogiberr.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply