Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhjackal
Starting Member
42 Posts |
 Posted - 2009-08-12 : 07:41:05
|
I have the following 3 maintenance plans running every day :"Full backup - system databases" @ 21:30 - 21:32"Full backup - user databases" @ 22:15 - 22:30"Transaction log backups" - runs hourlyI have the following 2 maintenance plans running every Sunday : "Weekly maintenance - system databases" (reorganise indexes, update stats and check db integrity) @ 23:30 - 23:32"Weekly maintenance - user databases" (rebuild / reorganise indexes, update stats and check db integrity) @ 00:30 - 01:30For the last 3 weeks the transaction log backup has failed at midnight on Sunday and doesn't work until the databases are fully backed up again at 22:15 the following night. This only happens on a Sunday! The message i get in the extended log file for the task is ; Failed:(-1073548784) Executing the query "BACKUP LOG [nextccdb] TO DISK = N'\\\\ldnfilv01\\backups$\\ldnsqlv01\\nextccdb\\nextccdb_backup_200908090500.trn' WITH NOFORMAT, NOINIT, NAME = N'nextccdb_backup_20090809050009', SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Which suggests I don't have a full backup of the db in order to carry out the trx backup BUT i have a successful backup of the db from 22:15 and the 22:00 and 23:00 trx backup completes fine  Any ideas? Thanks in advance |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-08-12 : 08:40:51
|
| Someone has either run a BACKUP LOG ... WITH TRUNCATE ONLY or has switched the recovery model from full to simple and back since the last full backup. Either of those operations break the log chain and you will not be able to take a log backup until you have taken another full or diff backup.If it happens around midnight on sunday, I would check the jobs that run just before that time to see if any one of them has truncated the log or messed with the recovery model. My guess, someone didn't like how the index rebuild impacted the log and put one of those statements into the rebuild/reorganise job without realising the consequences.--Gail ShawSQL Server MVP |
 |
|
|
dhjackal
Starting Member
42 Posts |
Posted - 2009-08-12 : 08:55:19
|
| Thanks Gail. This is exactly what I was looking at. I'll check the other jobs but most (if not all are standard SQL Server SSMS created). I was also going to set up and run a profiler job between 23:00 and 01:00 to see if that captured anything connecting to the db and changing the recovery mode. Thanks againAnyone any other theories |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-12 : 11:46:30
|
| your log chain is broken. that's pretty much the only theory :)check windows event log. any recovery model changes and backups (including with truncate_only) should be in the application log. |
|
|
|
|
|
|
|
|
Transaction log backup failing