Backups of tran log not working

I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.