Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
Maury
That's not much to go on but is there any chance the db is in Simple
Recovery mode?
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury
|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the problem
right there.
Maury
|||That's SQL Server 7.0 of course, not '97
|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97
|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury
|||Look up CHECKPOINT in BooksOnLine for details.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury
|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not be
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.

> This shouldn't be this confusing.
> Maury
|||"Greg D. Moore (Strider)" wrote:
> I missed it, is this a full or transaction log backup?
Transaction log.

> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should be
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury