May 25, 2005 at 2:52 am
Every night we do a full backup of a small database, in full mode (25 mb). After a week the log file is 5 GB in size.
When i look at the DBCC LOGINFO of the database i see that the entire log file is used (state 2). Even when i manualy do a full backup (when DBCC OPENTRAN returns nothing) i still see that the logfile wont be cleared.
When i do a Logfile backup, at last, the logfile is cleared (status 0).
Why isnt my logfile cleared when i do a ful backup? i find this realy weird and don't know where to look.
May 25, 2005 at 9:47 am
This is by design. Only a BACKUP LOG... command will truncate the log.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
May 25, 2005 at 9:50 am
Log file is cleared only when you backup the transaction logs. Or explicitly truncate it (which I think you don't want to do). When you do a full backup there can be some active transcations contained in the trn logs, and when you restore the database you use the trn logs to reconcile those transactions
May 25, 2005 at 10:01 am
Kasper,
If you're only going to do full database backups, make sure the database recovery model is set to Simple to keep the tlog from growing.
Greg
Greg
May 25, 2005 at 10:08 am
While technically true, that statement may not be the best advice... If you need to be able to recover your database to any point in time, you need to keep the recovery model at Full (or bulk_logged). But what you need to do is schedule regular t-log backups.
Read Books Online to get a good understanding of the implications of your options. Start by searching for the article titled "Backing Up and Restoring Databases" This article has links to more in-depth articles and should serve as a good starting point.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply