January 25, 2006 at 11:40 am
I recently inherited a database and found out that no Log backups were being taken. There is a daily full database backup but no Log backups so I created one. Later, checking the existent jobs i found this one :
backup log csdata6 with truncate_only
go
dbcc shrinkfile (csdata_log,100)
It runs every 15min, every day, ALL day. So I guess the Log backup I just created isn't much helpful, right? I can only think of one reason for truncating the transaction log and is space.
Can you guys think of any other reason to not care about having the possibility of restoring the database to a specific point in time available?
If I disable this job, will it affect anything different that the space I need for it to run? We have enough space in that drive for the log to grow, so that shouldn't be a problem.
January 25, 2006 at 11:43 am
What type of database is it ? What is the nature of the application(s) it supports ? OLTP ? Reporting ? Datamart ?
January 25, 2006 at 11:59 am
OLTP with Visual Studio applications running against it. A lot of data modification happening. About 120 users changing data.
January 25, 2006 at 1:06 pm
you supposed to use NO_TRUNCATE , read on the following options
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
NO_TRUNCATE
Allows backing up the log in situations where the database is damaged.
bondada
January 25, 2006 at 1:16 pm
After starting at a new job I inherited a number of these types of situations. The problem here was the recovery option on the databases was set to Full but nobody bothered to set up transaction log backups so the logs would grow and grow and grow. So to correct the problem they wrote something similar to backup with truncate only and then then shrink the file to run every hour. What I did is force the business to make a decission on what there acceptable data loss was. if they could afford to lose a days worth of data then I would schedule nighlty backups and set the recovery mode to simple which truncates the transaction log on checkpoint. If they needed to recover to within an hour then I set recovery to Full and then set up the nightly backups along with hourly transaction backups and deleted that awfull little process they had set up before. It is not needed since when the transaction log is backed up it is then truncated automatically.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply