August 13, 2010 at 7:47 am
I discovered an odd situation this morning.
A few weeks back I set up a new database instance and a scheduled maintenance plan to back it up.
I used the standard maintenance plan template I use for each database instance and tested it. Each database was backed up into its proper directory, as it should be.
The next morning, I checked that the maintenance plan ran as scheduled and all the databases were backed up.
Then, I just monitored that the jobs ran sucessfully.
All was well.
But, being paranoid about backups (a healthy diagnosis in the DBA job slot), I periodically verify that the backup files are in the directories that they are supposed to be in.
To my surprise, one of the databases was not being backed up! All the others were being backed up just as they should be.
I ran the plan again, starting it by hand, and it ran successfully. But that same database was skipped.
The database is online, and the backups are told to skip databases that are offline. On a whim, I changed the backup plan to no longer ignore off line databases.
The backup plan now backs up the database.
The application event logs for the regularly scheduled backup job time frame was interesting. I can see entries for the backups of each database except for the missing one. Instead of a backup entry for the problem database, I see two messages right before the backup entries for the others. One says that database started up, the other that it passed a checkdb test successfully.
However, now that the database is being backed up, I'm getting a problem about that database in the event log. It's complaining that the log file for that database isn't available. It's a SIMPLE recovery mode database, so there isn't supposed to be a log file, correct? None of the other SIMPLE mode databases are giving this error in the event log.
It's a MS-supplied application database, so I'll pursue it thru their channels in case MS is doing something funky.
Any other ideas in case this is a SQL Server based problem?
August 13, 2010 at 8:01 am
David
Databases in Simple recovery mode do have a log file, but you can't back it up since it is automatically truncated by SQL Server on a regular basis. Therefore if you issue a BACKUP LOG command against such a database, it will fail. What was the exact error message relating to the log file?
What do you get if you run the following command?
SELECT state_desc
FROM master.sys.databases
WHERE [name] = 'MyDB'
John
August 13, 2010 at 8:21 am
John Mitchell-245523 (8/13/2010)
DavidDatabases in Simple recovery mode do have a log file, but you can't back it up since it is automatically truncated by SQL Server on a regular basis. Therefore if you issue a BACKUP LOG command against such a database, it will fail. What was the exact error message relating to the log file?
What do you get if you run the following command?
SELECT state_descFROM master.sys.databases
WHERE [name] = 'MyDB'
John
The query comes back "ONLINE". It's one of the things I checked first, knowing that the backup job would skip offline DBs.
The error message relating to the log file is:
The log for database 'MyDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Additional info: Event ID = 9001.
The error message also shows up when no backup job is running.
I've seen references elsewhere to detach and reattach the database to solve the log file problem. ??
August 13, 2010 at 8:31 am
David
I think you can get that message when you have ant-virus software trying to access your database file. If you do have anti-virus software installed, please check that it is configured to exclude your database and log files.
John
August 13, 2010 at 8:41 am
Thanks, I'll look into that. It's a new box, the SAs might have forgotten to do that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply