July 2, 2003 at 2:45 am
Hi
All of the jobs I have on all my SQL Server works apart from the transaction log backup. I checked the Job history and came up with the following error: Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
I then checked the Maintainance plan logs and got Backup can not be performed on this database. This sub task is ignored for all the database. Please help. What should I do to prevent this error!
July 2, 2003 at 2:55 am
Check the DB options.In SQL Server 7 if db option "truncate log on checkpoint" is enable, or, in SQL Server 2K,the recovery model is set to simple, you cannot backup the transaction log.
HTH
Franco
Franco
July 2, 2003 at 3:06 am
Thanks Franco. You are right the options were either simple (SQL2000 )or "truncate log on checkpoint" is ticked on the SQL Server7. My next question is is it better to have this options or would you rather have the alternate options then backup the transaction log? What would you recommend?
July 2, 2003 at 4:45 am
Thats a hard question to answer. I think you need to look at the criticality of each system and what level of recovery your users would expect in the event of a major system failure. Are daily full or diff backups enough or do you need frequent transaction log backups as well ?
You may also need to consider the size of the database as very large databases need time to backup and also review when the system is accessed e.g. office hours only or 24/7.
July 2, 2003 at 6:35 am
I agree with Tim. You need to consider your disaster recovery plan and in case of restore if it is sufficent to restore your last backup or if you must have all the transactions from last backup till the problem occurs.
Critical decision.
Franco
Franco
July 2, 2003 at 6:51 am
I'm having a problem too with my transaction log backup. it fails all the time.... any ideas of how to fix this (its SQL 7.0)?
Here is the error...
[1] Database standard: Transaction Log Backup...
Destination: [d:\mssql7\BACKUP\standard_tlog_200307021347.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'Standard_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or di
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'test transaction log backup plan' on Wed Jul 02 13:47:05 2003
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
any help will be ace!
Meg
July 2, 2003 at 7:15 am
meghardy,
Please check if "select into/bulkcopy is set to true" in your database options.
In this case, You need to take a FULL ot INCREMENTAL DB Backup before starting the Transaction Log Back Job.
Could you tell us at what time you are taking Database Backups/Transaction LogBackups/Optimising/Integrity Checks are running???
.
July 2, 2003 at 9:45 am
Hi, thanks for all the advise,
This is a server which part of our company will not let us administer...hence the mess of it!! They only come to me when things are failing!
Anyway, I had a look at the database options and yes, Select Into/Bulk Copy was checked on alot of them. I have unchecked that option now (not sure what the effect will be, but pretty sure it should be fine. They dont have any applications which use the databases). I did a full backup and then run the transaction log backup, and woohoo, it worked...thanks for that.
This server has full backups once every 3 weeks to disk (tape backups are done daily).
Transaction log backups are done once a day (alot of these databases had truncate log on chk pt. on too!!)
Integrity checks are done once a week, and optimisation is not done at all....
I think i need to sort the server out!!
Its not a critical server, just used by analysis staff who create databases, tables, sp's, and everything!!
Cheers
Meg
July 3, 2003 at 4:52 am
Hi again,
Please can someone explain why when 'select into/bulkcopy' is set to true, you cant do a transaction log backup.
Also, why does it fix the problem if you do a full backup first. I'm trying to explain it to someone and not doing a very good job of it!
Thanks
Meg
July 3, 2003 at 7:09 am
When 'select into/bulkcopy' is set to true, the operation is non-logged. So you must use the BACKUP DATABASE statement, because changes made by the nonlogged operation cannot be recovered from transaction logs.
Note that this effect is only seen in SQL Server 7.0; in version 2000, the transaction log backup will not fail but if your non-logged operation has to do with text/image data, while the trasaction log appears to succeed, you may actually be backing up empty text pages.
quote:
Hi again,Please can someone explain why when 'select into/bulkcopy' is set to true, you cant do a transaction log backup.
Also, why does it fix the problem if you do a full backup first. I'm trying to explain it to someone and not doing a very good job of it!
Thanks
Meg
Joseph
July 3, 2003 at 7:48 am
The select into/bulk copy option is mainly to make bulk inserts/updates go smoothly. When doing a huge insert, the transaction log needs to continuously grow and it might not be able to grow fast enough.
Imagine inserting a million rows, but only allowing your transaction log to grow by 100 MB. It probably won't grow fast enough to keep track of the inserts. So it fails.
SELECT INTO/BULK COPY ignores the transaction log completely.
-SQLBill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply