October 16, 2002 at 6:13 pm
I have tried to create a scheduled job to backup the transaction log on a regular basis
via the Database Maintenance Plans wizard.
This process seems OK but when the jon runs it fails with a message: "Backup cannot be performed on this database".
Is there something else that I should be doing here?
Franknf
October 16, 2002 at 7:13 pm
Which database? And is it set to truncate log on checkpoint.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 16, 2002 at 7:46 pm
Hi Antares,
The database involved is our main production database.
The message i refer to is from the Database MaintenancePlan History which referes to the Database that I am trying to backup the log for. In the message, the activity is Backup Transaction log and the Status column is ticked.
The database concerned does have truncate at checkpoint on. Is this wrong. If I change this now, what ranifications could it have for our production system? Thanks for your help
Franknf
October 17, 2002 at 3:50 am
No if you set truncate on checkpoint on it will not be able to make backups. Have you done any bcp or other bulk type transactions on this database? If so you must run a full backup immediately afterward for you to be able to perform another transaction log backup.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 20, 2002 at 6:07 pm
Hi antares, Sorry I've been so long replying.
No there have been no bulk updates of the database. If I just set truncate on checkpoint Off, can I then get this job to work, without causing any other hiccups in the system? Thanks for your help
Franknf
Franknf
October 21, 2002 at 4:30 am
quote:
If I just set truncate on checkpoint Off
If you are saying that it is on then, yes turn it off to be able to perform TL backups without issue. Now as for it causing other issues, depending on the Version of SQL you are running the log will grow and may not shrink on it owns. The best way to deal with it is to set a max limit or periodically turncate the log, shrink it and do a full backup of the database. My suggestions is to find a period of time (say a wekk or a month) taht you prefer to do this type of task and let the log grow to that size then set then turn of autimatically grow the file or set a max size ou want the file to stop at without interference. Then each time frame just truncate the log. Also, when doing this please setup and alert to make you aware when the log is nearly full just in case a higher number of transactions than normal occurr so you can directly deal with this need.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 23, 2002 at 12:09 am
Thanks Antares,
I turned Truncate on checkpoint off and now the logs are backing up regularly.
As the time between backups is well within limits of the log filling, that problem is Ok too,
Again, Thanks
Franknf
Franknf
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply