maintenance fails

Hi
Im running a Windows 2003 server and MSSQL 2000 that manages some 45
databases. I created an maintenance plan that does an integrity check,
backups all databases, and backup all transactionlogs.
If I look at the "Jobs" list it looks like backing up the databases runs OK,
but both the integrity check and transaction log seams to fail.
If I look at the folder holding the backupfiles all transactionfiles looks
to be there (backup of all the transaction logs). So what makes the job
fail? If I look at the event log all transaction-log-backups complete OK,
but still the job ends in failure and the eventlog only states that the job
faild - not why it failed.
How can I track this problem? And is this why the transaction logs are not
truncated?
Regards
Jan Agermose
Hi
Within a job specify an output file to see what is going on.
<msde@.csite.com> wrote in message
news:epoOEnVUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs
OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the
job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>
|||Hello Jan,
Go to your maintenance plan in enterprise manager and right click on it.
Select "Maintenance Plan History...". In the Status dropdown, select
"Failed". You should now see a list of failed tasks in your plan. Look for a
plan with an activity of "Backup Transaction Log" and double click it. The
reason for failure will appear. My guess is that the database is in simple
recovery mode and you will get an error similar to "Backup can not be
performed on this database. This sub task is ignored".
All you have to do in this instance is remove databases that have simple
recovery from your maintenance plan, or change them to a different recovery
model (if that's what you need).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"msde@.csite.com" wrote:

> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>
|||Ahhh :-) Of cause - I selsected to backup all databases - this includes
"master", "tempdb" and the test database "northwind". Doing a backup of the
transaction log fails.
I guess that means I need two plans
One for all user databases but not the system databases - this plan does a
databasebackup and a transactionlog backup.
And one for the system databases that does not try to backup the
transactionlog?
Regards
Jan
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...[vbcol=seagreen]
> Hello Jan,
> Go to your maintenance plan in enterprise manager and right click on it.
> Select "Maintenance Plan History...". In the Status dropdown, select
> "Failed". You should now see a list of failed tasks in your plan. Look for
> a
> plan with an activity of "Backup Transaction Log" and double click it. The
> reason for failure will appear. My guess is that the database is in simple
> recovery mode and you will get an error similar to "Backup can not be
> performed on this database. This sub task is ignored".
> All you have to do in this instance is remove databases that have simple
> recovery from your maintenance plan, or change them to a different
> recovery
> model (if that's what you need).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "msde@.csite.com" wrote:
|||You got the point, Jan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<msde@.csite.com> wrote in message news:O8YUBjbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Ahhh :-) Of cause - I selsected to backup all databases - this includes "master", "tempdb" and the
> test database "northwind". Doing a backup of the transaction log fails.
> I guess that means I need two plans
> One for all user databases but not the system databases - this plan does a databasebackup and a
> transactionlog backup.
> And one for the system databases that does not try to backup the transactionlog?
> Regards
> Jan
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...
>