Delete Transaction Logs?
-
We have a situation where a transaction log has grown to the point where a backup is impossible because there isn't enough space on any local drive to run a backup. Although I know deleting the transaction log is not a good idea, this isn't a critical application (it is hardly used) and we have backups to tape and from Live State anyway. How can this newbie simply delete the existing log and create a new, empty log? Any advice on other pre- or post-steps would be appreciated.
-
First change the recovery model to SIMPLE. If you aren't going to run transaction log backups then don't set recovery to FULL. Next, use the command BACKUP LOG [databasename] WITH NO_LOG to truncate the log. Your next step is to shrink the log file to a more suitable size.
Francis
-
One solution is to change to simple recovery mode:
alter database mydatabase set recovery simplethen shrink the logfile (usually 2 is the log file id, check by looking into sysfiles)
DBCC SHRINKFILE (2)
then change back to full or bulk recovery mode, and do take a backup
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Wow good post Andras, we must have been typing our reply simultaneously
Great Minds think alike
or is it Fools don't differ
I can never remember those aphorisms
Francis
-
Many thanks for the responses! These were a great help!
Rich
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply