more help from any sql peoples here please

[Mupepe]

so on one of our regional servers, we noticed that we were low on hard drive space this morning and when we checked it we saw that our transaction log had grown to over 2 gb's.  it's set to truncate and backup every night, but it had apparently failed.  when we ran the job or maintenance plan, it failed.  But if we went to the database and right clicked, went to backup and did it that way, it backed up and truncated perfectly.  it went down to 670 mb's.

Now the problem is that we tried to create a new maintenance plan (cleaned up some old ones created by god knows who) and tried to consolidate our backups and checks into two plans.  we started the backup jobs from the job window and they all ran except for the transaction log backup.  the job failed on startup.  when we right clicked on the database and went to backup, we could no longer select "transaction log" only database.  when we checked the sql error logs, it said "could not perform backup on database 'accesscontrol'.  subtask ignored."

any ideas on why we can no longer do this?

[Mupepe]

by the way we tried to have it backup on a local disk (c:\ and seperate partition) and an external drive.  all failed.

[cubicle47b]

Check your recovery model under database properties.  It sounds like it's set to simple which will lock you out of transaction log backups.

[Mupepe]

hm i just checked and yep, it's under simple.  is there anything that would have changed that?  because it was seriously just like an hour between backing it up and then it being greyed out.  And we didn't go in and change the recovery model manually.

[cubicle47b]

I can't think of anything that would automatically switch it like that.  It sounds like it had been set to Simple for a while and that's why the transaction log backup had been failing but then how were you able to do a manual backup an hour ago?  Something is strange here.

Anyway, is your maintenance plan working now?

[Mupepe]

yes sir.  if i leave it on simple and just do a backup, it will automatically truncate the log anyhow, correct?

if that's the case, i'm going to leave it that way.

also, something we just discovered, someone changed the time of one of our replications to 3am on sunday mornings.  well, we had a backup maintenance plan running every morning at 3am, so that's why it's been failing every Sunday it seems.

[cubicle47b]

Yeah, it will, but it hurts your recovery options.

Simple

The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.

Full

The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.

Select Simple if:

    * Your data is not critical.
    * Losing all transactions since the last full or differential backup is not an issue.
    * Data is derived from other data sources and is easily recreated.
    * Data is static and does not change often.
    * Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.)

[Mupepe]

oh shit.  we're like the complete opposite of any of those