Estimation of Full Backup size

Has anyone come across a more accurate method than sp_spaceused to estimate the size of a full database backup for SQL Server 2000 ?

I have found this to have too great a variance (even after running updateusage) to rely on any accuracy for it. I have also looked at perhaps using the ALLOCATED Pages indicated in the GAM pages but this also seems to be pretty inaccurate.

I have a number of servers where space can be limited and backups using Maintenance Plans have occasionally failed because they delete the old backups AFTER they do the latest one. I am writing a script which can check the space remaining and adjust the backup accordingly but the variance I have observed so far with sp_spaceused is too great.

Any ideas welcomed.

Full backups copy all pages within allocated extents. So, there may be a few pages which are not allocated, but which are copied to the backup. Also, there is a small percentage of overhead in the backup format, but this should be predictable.

The most accurate estimate would be to use updateusage and then look at the reserved column of sp_spaceused.

What is the variance that you are seeing? It should be fairly small.

The real bottom line is that if you have servers which you know occasionally run out of backup space, they need to have storage added so that you can ensure the safety of your databases. You can minimize the space requirements by compressing the backups and/or copying them off the server as soon as they are created, but you need the space there.

|||

Thanks for that Kevin

I need to do a few more tests to confirm the variance that I am seeing - it seemed to be inconsistent for a couple of databases (shooting up to 4MB) after being within about 85KB - 90KB for all of the others so there may have been something else happening.

You mention all pages within allocated extents - does that mean that I could use the GAM or SGAM pages to count the number of allocated extents and use this as an estimate? I already have a method of doing this for estimating differential backups using the DIFF_MAP pages.

Martin

|||

The other component of a backup is that the portion of the log created during the execution of the backup is also backed up. This is required in order to get a consistent view of the data at a single point in time.

Depending on the activity level and size of your database, this could easily account for this much variability and more.

Lets look at this from a different angle:

What are your business requirements around backups that are driving your backup plans? If we understand those, we can perhaps better optimize your strategy. I find that the best way to craft a backup strategy is to start from the recovery requirements and work backwards to come up with a plan which will support those needs.

What are the sizes of the databases you are backing up?

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>sql

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram Shahzad
Hi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:
> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Errors: Recovery Mode: Full and Simple

Hi
I have a production database that is set to Recovery Mode: Simple.
However I decided to change this to Recovery Mode: Full. A scheduled
backup of the database was then run that night.
Unfortunately I was then told to reset the Recovery Mode back to
Simple as not enough testing had been done. So I did this.
The next day we received error messages that "The log file for the
database is full. Back up the transaction log for the database to free
up some log space."
The production db was then unusable for the apps. As a quick solution
I changed the recovery mode back to full and backed up the transaction
log ( because you cannot backup the transaction log in simple mode).
This made the database operational again.
I then changed the Recovery mode back to simple for the next night's
scheduled backup.
So..... the nightly backup goes ahead with the db still in simple
mode.
HOWEVER we are still getting the log full error messages and I keep
having to change to recovery mode full and backup the transaction log
and then swap back again to simple mode.
I realise that if the database is set to Recovery Mode: Full then the
transaction log must be backed up regularly. But we are no longer
running in Full mode and I don’t not want to run in full mode
and the transaction logs never used to get full in Simple mode and we
never had to back them up.
I would just like things to get back to how they were - the database
running in simple mode and nightly backups taking place. Then those
nightly backups - which are in one file - restoring ok on a test
machine.
Has anyone got any ideas?
Many thanks!
Thiko!Thiko
How large is your transaction log? Even with simple
recovery model the transaction log is still used.
It is just cleared when transactions complete. If you have
some/one very large transaction running on the system you
may still fill up the transaction log even in simple
recovery mode.
If your transaction log is quite small, you may solve your
problem by increasing it.
Regards
Johnsql

errors whan running backup plan

My event log has the following entries every day ( because the plan runs every
day)
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels Backup
Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by Schedule
20 (Schedule 1). The last step to run was step 1 (Step 1).
That error message follows four other entries like this.
Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump devices:
1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
I seem to have all the backups in the folder that there should be, but then what
is happening with the first error message?
I also can manually make a backup without any error messages appearing so
something is wrong with my plan but what? it doesn't look that complicated to
set up.Do you have any other steps on this maintenance plan such as an integrity
check?
Jim
"mmac" <mmac@.junkmail.bin> wrote in message
news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> My event log has the following entries every day ( because the plan runs
every
> day)
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels
Backup
> Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
> 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
Schedule
> 20 (Schedule 1). The last step to run was step 1 (Step 1).
> That error message follows four other entries like this.
> Database backed up: Database: model, creation date(time):
2000/08/06(01:40:52),
> pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
devices:
> 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
SQL
> Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> I seem to have all the backups in the folder that there should be, but
then what
> is happening with the first error message?
> I also can manually make a backup without any error messages appearing so
> something is wrong with my plan but what? it doesn't look that complicated
to
> set up.
>|||Do you get any better errormessages if you specify a report file for the
plan and check that report file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mmac" <mmac@.junkmail.bin> wrote in message
news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> My event log has the following entries every day ( because the plan runs
every
> day)
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels
Backup
> Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
> 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
Schedule
> 20 (Schedule 1). The last step to run was step 1 (Step 1).
> That error message follows four other entries like this.
> Database backed up: Database: model, creation date(time):
2000/08/06(01:40:52),
> pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
devices:
> 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
SQL
> Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> I seem to have all the backups in the folder that there should be, but
then what
> is happening with the first error message?
> I also can manually make a backup without any error messages appearing so
> something is wrong with my plan but what? it doesn't look that complicated
to
> set up.
>|||Ahh that was helpful.
here is what I get:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
...
So I removed the system databases leaving only my database from the backup
and it went fine. do I need to save the system databases differently? or at
all?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Do you get any better errormessages if you specify a report file for the
> plan and check that report file?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "mmac" <mmac@.junkmail.bin> wrote in message
> news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > My event log has the following entries every day ( because the plan runs
> every
> > day)
> >
> > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
'Michaels
> Backup
> > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked
on:
> > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
> Schedule
> > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > That error message follows four other entries like this.
> >
> > Database backed up: Database: model, creation date(time):
> 2000/08/06(01:40:52),
> > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
> devices:
> > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
> SQL
> > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> >
> > I seem to have all the backups in the folder that there should be, but
> then what
> > is happening with the first error message?
> > I also can manually make a backup without any error messages appearing
so
> > something is wrong with my plan but what? it doesn't look that
complicated
> to
> > set up.
> >
> >
>|||> So I removed the system databases leaving only my database from the
backup
> and it went fine. do I need to save the system databases differently? or
at
> all?
Yes, create a different maint pan for master, model and msdb and do backup
once a day... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mmac" <no@.thanks.com> wrote in message
news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> Ahh that was helpful.
> here is what I get:
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
> 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode.
> ...
> So I removed the system databases leaving only my database from the
backup
> and it went fine. do I need to save the system databases differently? or
at
> all?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Do you get any better errormessages if you specify a report file for the
> > plan and check that report file?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mmac" <mmac@.junkmail.bin> wrote in message
> > news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > > My event log has the following entries every day ( because the plan
runs
> > every
> > > day)
> > >
> > > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
> 'Michaels
> > Backup
> > > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked
> on:
> > > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
> > Schedule
> > > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> > >
> > > That error message follows four other entries like this.
> > >
> > > Database backed up: Database: model, creation date(time):
> > 2000/08/06(01:40:52),
> > > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
> > devices:
> > > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program
Files\Microsoft
> > SQL
> > > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> > >
> > > I seem to have all the backups in the folder that there should be, but
> > then what
> > > is happening with the first error message?
> > > I also can manually make a backup without any error messages appearing
> so
> > > something is wrong with my plan but what? it doesn't look that
> complicated
> > to
> > > set up.
> > >
> > >
> >
> >
>|||and they will backup in "single user mode"?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUnYqaf$DHA.688@.tk2msftngp13.phx.gbl...
> > So I removed the system databases leaving only my database from the
> backup
> > and it went fine. do I need to save the system databases differently? or
> at
> > all?
> Yes, create a different maint pan for master, model and msdb and do backup
> once a day... :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "mmac" <no@.thanks.com> wrote in message
> news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> > Ahh that was helpful.
> > here is what I get:
> >
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> > Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
> > 'MASTER'.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> > [1] Database master: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Repair statement not processed. Database needs
> to
> > be in single user mode.
> > ...
> > So I removed the system databases leaving only my database from the
> backup
> > and it went fine. do I need to save the system databases differently? or
> at
> > all?
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > Do you get any better errormessages if you specify a report file for the
> > > plan and check that report file?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "mmac" <mmac@.junkmail.bin> wrote in message
> > > news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > > > My event log has the following entries every day ( because the plan
> runs
> > > every
> > > > day)
> > > >
> > > > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
> > 'Michaels
> > > Backup
> > > > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked
> > on:
> > > > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
> > > Schedule
> > > > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> > > >
> > > > That error message follows four other entries like this.
> > > >
> > > > Database backed up: Database: model, creation date(time):
> > > 2000/08/06(01:40:52),
> > > > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
> > > devices:
> > > > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program
> Files\Microsoft
> > > SQL
> > > > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> > > >
> > > > I seem to have all the backups in the folder that there should be, but
> > > then what
> > > > is happening with the first error message?
> > > > I also can manually make a backup without any error messages appearing
> > so
> > > > something is wrong with my plan but what? it doesn't look that
> > complicated
> > > to
> > > > set up.
> > > >
> > > >
> > >
> > >
> >
> >
>|||There's no need to set the single user mode unless you check the dreaded
"Attempt to repair minor problems" under the integrity check part. I do not
recommend using this option at all. If I have a problem with a corrupt
database, I want to know that.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mmac" <mmac@.junkmail.bin> wrote in message
news:uy7MGro$DHA.2212@.TK2MSFTNGP10.phx.gbl...
> and they will backup in "single user mode"?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OUnYqaf$DHA.688@.tk2msftngp13.phx.gbl...
> > > So I removed the system databases leaving only my database from the
> > backup
> > > and it went fine. do I need to save the system databases differently?
or
> > at
> > > all?
> >
> > Yes, create a different maint pan for master, model and msdb and do
backup
> > once a day... :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mmac" <no@.thanks.com> wrote in message
> > news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> > > Ahh that was helpful.
> > > here is what I get:
> > >
> > >
> > > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server
> > > 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> > > Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30
PM
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
database
> > > 'MASTER'.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
> > > [1] Database master: Check Data and Index Linkage...
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Repair statement not processed. Database
needs
> > to
> > > be in single user mode.
> > > ...
> > > So I removed the system databases leaving only my database from the
> > backup
> > > and it went fine. do I need to save the system databases differently?
or
> > at
> > > all?
> > >
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > > Do you get any better errormessages if you specify a report file for
the
> > > > plan and check that report file?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > > >
> > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "mmac" <mmac@.junkmail.bin> wrote in message
> > > > news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > > > > My event log has the following entries every day ( because the
plan
> > runs
> > > > every
> > > > > day)
> > > > >
> > > > > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
> > > 'Michaels
> > > > Backup
> > > > > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed -
Invoked
> > > on:
> > > > > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked
by
> > > > Schedule
> > > > > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> > > > >
> > > > > That error message follows four other entries like this.
> > > > >
> > > > > Database backed up: Database: model, creation date(time):
> > > > 2000/08/06(01:40:52),
> > > > > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of
dump
> > > > devices:
> > > > > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program
> > Files\Microsoft
> > > > SQL
> > > > > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> > > > >
> > > > > I seem to have all the backups in the folder that there should be,
but
> > > > then what
> > > > > is happening with the first error message?
> > > > > I also can manually make a backup without any error messages
appearing
> > > so
> > > > > something is wrong with my plan but what? it doesn't look that
> > > complicated
> > > > to
> > > > > set up.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

errors whan running backup plan

My event log has the following entries every day ( because the plan runs eve
ry
day)
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels Ba
ckup
Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by Schedu
le
20 (Schedule 1). The last step to run was step 1 (Step 1).
That error message follows four other entries like this.
Database backed up: Database: model, creation date(time): 2000/08/06(01:40:5
2),
pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump device
s:
1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
SQL
Server\MSSQL\BACKUP\model_db_20040211020
0.BAK'}).
I seem to have all the backups in the folder that there should be, but then
what
is happening with the first error message?
I also can manually make a backup without any error messages appearing so
something is wrong with my plan but what? it doesn't look that complicated t
o
set up.Do you have any other steps on this maintenance plan such as an integrity
check?
Jim
"mmac" <mmac@.junkmail.bin> wrote in message
news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> My event log has the following entries every day ( because the plan runs
every
> day)
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels
Backup
> Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
> 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
Schedule
> 20 (Schedule 1). The last step to run was step 1 (Step 1).
> That error message follows four other entries like this.
> Database backed up: Database: model, creation date(time):
2000/08/06(01:40:52),
> pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
devices:
> 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft[/colo
r]
SQL
> Server\MSSQL\BACKUP\model_db_20040211020
0.BAK'}).
> I seem to have all the backups in the folder that there should be, but
then what
> is happening with the first error message?
> I also can manually make a backup without any error messages appearing so
> something is wrong with my plan but what? it doesn't look that complicated
to
> set up.
>|||Do you get any better errormessages if you specify a report file for the
plan and check that report file?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mmac" <mmac@.junkmail.bin> wrote in message
news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> My event log has the following entries every day ( because the plan runs
every
> day)
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels
Backup
> Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
> 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
Schedule
> 20 (Schedule 1). The last step to run was step 1 (Step 1).
> That error message follows four other entries like this.
> Database backed up: Database: model, creation date(time):
2000/08/06(01:40:52),
> pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
devices:
> 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft[/colo
r]
SQL
> Server\MSSQL\BACKUP\model_db_20040211020
0.BAK'}).
> I seem to have all the backups in the folder that there should be, but
then what
> is happening with the first error message?
> I also can manually make a backup without any error messages appearing so
> something is wrong with my plan but what? it doesn't look that complicated
to
> set up.
>|||Ahh that was helpful.
here is what I get:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs
to
be in single user mode.
...
So I removed the system databases leaving only my database from the backup
and it went fine. do I need to save the system databases differently? or at
all?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Do you get any better errormessages if you specify a report file for the
> plan and check that report file?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "mmac" <mmac@.junkmail.bin> wrote in message
> news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> every
'Michaels
> Backup
on:
> Schedule
> 2000/08/06(01:40:52),
> devices:
> SQL
> then what
so
complicated
> to
>|||> So I removed the system databases leaving only my database from the
backup
> and it went fine. do I need to save the system databases differently? or
at
> all?
Yes, create a different maint pan for master, model and msdb and do backup
once a day... :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mmac" <no@.thanks.com> wrote in message
news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> Ahh that was helpful.
> here is what I get:
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in databa
se
> 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs[/colo
r]
to
> be in single user mode.
> ...
> So I removed the system databases leaving only my database from the
backup
> and it went fine. do I need to save the system databases differently? or
at
> all?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
>
http://groups.google.com/groups?oi=...ublic.sqlserver
runs
> 'Michaels
> on:
Files\Microsoft
> so
> complicated
>|||and they will backup in "single user mode"?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUnYqaf$DHA.688@.tk2msftngp13.phx.gbl...
> backup
> at
> Yes, create a different maint pan for master, model and msdb and do backup
> once a day... :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
>
> "mmac" <no@.thanks.com> wrote in message
> news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> SQL
> SQL
> to
> backup
> at
> in
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> runs
> Files\Microsoft
>|||There's no need to set the single user mode unless you check the dreaded
"Attempt to repair minor problems" under the integrity check part. I do not
recommend using this option at all. If I have a problem with a corrupt
database, I want to know that.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mmac" <mmac@.junkmail.bin> wrote in message
news:uy7MGro$DHA.2212@.TK2MSFTNGP10.phx.gbl...
> and they will backup in "single user mode"?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OUnYqaf$DHA.688@.tk2msftngp13.phx.gbl...
or
backup
http://groups.google.com/groups?oi=...ublic.sqlserver
Server
PM
[Microsoft][ODBC
database
failed.
[Microsoft][ODBC
needs
or
wrote
the
http://groups.google.com/groups?oi=...ublic.sqlserver
plan
Invoked
by
dump
but
appearing
>

Errors of System DB Backup T-logJobs for Maint Plan

I have been bothered with the error signs of the DB Backup
jobs for my DB Maint Plan. These job errors were shown in
SQL Server Agent node of the Enterprise Manager.
After checking the DB Maint Plan history, I found the
errors were due to the Backup T-log job errors on system
databases, e.g. master, msdb. Later on I found out that
we can set master to Full recovery model, but still no T-
log backup choice available, while tempdb can not even be
set to Full model.
If I still want to select 'All Databases' in my Maint
Plan, how can I avoid these Maint Plan job errors in the
SQL Server Agent node? How do I decide the DB recovery
model (Full or Simple) for the system databases in my
database servers when configuring my Maint Plan?
Furthermore, I have heard that there are no T-log backup
device for the system databases, only for user databases.
What exactly does this mean?
Thanks in Advance.
JeffreyI normally create one maint plan for my sysdbs this way you can avoid that
and some other errors.
You cannot restore the master database like you restore the users db, the
master db has to be rebuild. Because of the size of your system db i
normally backed them up twice a day, and overwrite every two weeks and i
have it set to simple.
Take a look at BOL (books online) to understand more about backup models.
Yovan Fernandez
"Jeffrey" <jwang@.dot.state.tx.us> wrote in message
news:0bd901c377d7$bcf03a40$a001280a@.phx.gbl...
> I have been bothered with the error signs of the DB Backup
> jobs for my DB Maint Plan. These job errors were shown in
> SQL Server Agent node of the Enterprise Manager.
> After checking the DB Maint Plan history, I found the
> errors were due to the Backup T-log job errors on system
> databases, e.g. master, msdb. Later on I found out that
> we can set master to Full recovery model, but still no T-
> log backup choice available, while tempdb can not even be
> set to Full model.
> If I still want to select 'All Databases' in my Maint
> Plan, how can I avoid these Maint Plan job errors in the
> SQL Server Agent node? How do I decide the DB recovery
> model (Full or Simple) for the system databases in my
> database servers when configuring my Maint Plan?
> Furthermore, I have heard that there are no T-log backup
> device for the system databases, only for user databases.
> What exactly does this mean?
> Thanks in Advance.
> Jeffrey
>

Errors during Maintenance Plan execution

I am getting error on several database when trying to use
the Maintenance Plan to backup all databses in our SQL
Server 2000 with Service Pack 3.
The following is the text generated from one of the
database in the log file. The other databases generate
the same error message
Log portion============================== [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'SQLCatalog'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[18] Database SQLCatalog: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
========================================
Any ideas on how to handle this? Is this a matter of
running the maintenance plan at a different time?You've checked the option to "Attempt to repair minor problems" for which
SQL Server tries to set the db in single user mode which will fail if you
have users in the database. Remove that option, if you do get a problem with
the database, you want to know about it and be there to make a conscious
decision of your actions at that stage.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> I am getting error on several database when trying to use
> the Maintenance Plan to backup all databses in our SQL
> Server 2000 with Service Pack 3.
> The following is the text generated from one of the
> database in the log file. The other databases generate
> the same error message
> Log portion==============================> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'SQLCatalog'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [18] Database SQLCatalog: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ========================================> Any ideas on how to handle this? Is this a matter of
> running the maintenance plan at a different time?|||That helped. So now I have an error with the transaction
log backup portion of the Maintenace Plan.
Here is the erro
==========================================================Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step failed.
===============================================
I can't find this in the error logs or the backup log so
I'm not sure what is happening
>--Original Message--
>You've checked the option to "Attempt to repair minor
problems" for which
>SQL Server tries to set the db in single user mode which
will fail if you
>have users in the database. Remove that option, if you
do get a problem with
>the database, you want to know about it and be there to
make a conscious
>decision of your actions at that stage.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Jim" <jim.abel@.lmco.com> wrote in message
>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
>> state cannot be changed while other users are using the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>
>.
>|||Possibly the database is in simple recovery model. You will not find details
error info from looking at just the job. There's a GUI for this in the main
folder, but I prefer the text report file option that maint wiz has.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:10f501c3be74$0be078d0$a401280a@.phx.gbl...
> That helped. So now I have an error with the transaction
> log backup portion of the Maintenace Plan.
> Here is the erro
> ==========================================================> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> failed. [SQLSTATE 42000] (Error 22029). The step failed.
> ===============================================> I can't find this in the error logs or the backup log so
> I'm not sure what is happening
> >--Original Message--
> >You've checked the option to "Attempt to repair minor
> problems" for which
> >SQL Server tries to set the db in single user mode which
> will fail if you
> >have users in the database. Remove that option, if you
> do get a problem with
> >the database, you want to know about it and be there to
> make a conscious
> >decision of your actions at that stage.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Jim" <jim.abel@.lmco.com> wrote in message
> >news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying to
> use
> >> the Maintenance Plan to backup all databses in our SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases generate
> >> the same error message
> >>
> >> Log portion==============================> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> >> state cannot be changed while other users are using the
> >> database 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: Check Data and Index
> Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in single
> >> user mode.
> >>
> >> The following errors were found:
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in single
> >> user mode.
> >> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >
> >
> >.
> >|||Hello Jim,
First of all, thanks to Tibor for pointing you in the right direction.
Just as an additional information,
you can refer to the following article which explains how to t-shoot
General SQL Maint Wiz failures,
INF: Troubleshooting Database Maintenance Plan Failures
http://support.microsoft.com/default.aspx?scid=kb;en-us;288577
In case, you are doing Transaction log backups of one of the databases
which is set to
Simple Recovery Mode, you might want to refer to following article
explaining this scenario,
BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292
Please let us know if these suggestions resolve your issue, if not , feel
free to post any further questions you have.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security."
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Tue, 9 Dec 2003 08:46:52 -0800
>Lines: 71
>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:319894
>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>That helped. So now I have an error with the transaction
>log backup portion of the Maintenace Plan.
>Here is the erro
>==========================================================>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>failed. [SQLSTATE 42000] (Error 22029). The step failed.
>===============================================>I can't find this in the error logs or the backup log so
>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>problems" for which
>>SQL Server tries to set the db in single user mode which
>will fail if you
>>have users in the database. Remove that option, if you
>do get a problem with
>>the database, you want to know about it and be there to
>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
>use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
>> state cannot be changed while other users are using the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>|||All right guys are really helping me out. I looked up
the proble and it has come down to the following The
maintenace Plan says that it cannot do transaction log
backups on the master and the msdb databases.
My question now is how can I exclude those 2 DB's from
the Transaction log backup portion of the maintenance
Plan?
>--Original Message--
>
>Hello Jim,
>First of all, thanks to Tibor for pointing you in the
right direction.
>Just as an additional information,
>you can refer to the following article which explains
how to t-shoot
>General SQL Maint Wiz failures,
>INF: Troubleshooting Database Maintenance Plan Failures
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;288577
>In case, you are doing Transaction log backups of one of
the databases
>which is set to
>Simple Recovery Mode, you might want to refer to
following article
>explaining this scenario,
>BUG: Expired Transaction Log Backups May Not Be Deleted
by Maintenance Plan
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292
>Please let us know if these suggestions resolve your
issue, if not , feel
>free to post any further questions you have.
>Thanks for posting to MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security."
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>==========================================================>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
failed.
>>===============================================>>I can't find this in the error logs or the backup log
so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
to
>>use
>> the Maintenance Plan to backup all databses in our
SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Database
>> state cannot be changed while other users are using
the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>.
>|||Hello Jim,
You need to create a seperate Maintenance Plan for those DBs which need
both the Database and Transaction log backups and
a seperate one for master and msdb for only Database backups.
Does that answer your question ?
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
<10f501c3be74$0be078d0$a401280a@.phx.gbl>
<TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Fri, 12 Dec 2003 08:27:51 -0800
>Lines: 171
>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:320465
>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>X-Tomcat-NG: microsoft.public.sqlserver.server
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>Database
>> state cannot be changed while other users are using
>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>|||Thank you Vikrant
That answers all of my questions.
I appreciate all of the help.
Happy Holidays
>--Original Message--
>Hello Jim,
>You need to create a seperate Maintenance Plan for
those DBs which need
>both the Database and Transaction log backups and
> a seperate one for master and msdb for only Database
backups.
>Does that answer your question ?
>Thanks for using MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
><10f501c3be74$0be078d0$a401280a@.phx.gbl>
><TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Fri, 12 Dec 2003 08:27:51 -0800
>>Lines: 171
>>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:320465
>>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>All right guys are really helping me out. I looked up
>>the proble and it has come down to the following The
>>maintenace Plan says that it cannot do transaction log
>>backups on the master and the msdb databases.
>>My question now is how can I exclude those 2 DB's from
>>the Transaction log backup portion of the maintenance
>>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;288577
>>In case, you are doing Transaction log backups of one
of
>>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be
Deleted
>>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;303292
>>Please let us know if these suggestions resolve your
>>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>>that they visit the
>>http://www.microsoft.com/protect site and perform the
>>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties,
and
>>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair
minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>>which
>>will fail if you
>>have users in the database. Remove that option, if
you
>>do get a problem with
>>the database, you want to know about it and be there
>>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>>to
>>use
>> the Maintenance Plan to backup all databses in our
>>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>>Database
>> state cannot be changed while other users are
using
>>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Repair
>> statement not processed. Database needs to be in
>>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Repair
>> statement not processed. Database needs to be in
>>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter
of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>>
>.
>|||I have a similar problem so I verified that my database is
the only one in the maintenance plan (does not include
master or msdb). Yet my Transaction Log backup still
fails with the same message:
"The job failed. The Job was invoked by User U1ST074SORTS2
\rdenison. The last step to run was step 1 (Step 1)."
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
step failed."
Any other ideas? I'm really interested in resolving this
because my Xaction log gets huge, especially on a
distributor server.
Roger.
>--Original Message--
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>Database
>> state cannot be changed while other users are using
>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>.
>|||Why not create your own job to do the backups and not rely on the MP at all?
Then you will know exactly what it is trying to do and when.
--
Andrew J. Kelly
SQL Server MVP
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:215001c3cfee$1f72d010$3101280a@.phx.gbl...
> I have a similar problem so I verified that my database is
> the only one in the maintenance plan (does not include
> master or msdb). Yet my Transaction Log backup still
> fails with the same message:
> "The job failed. The Job was invoked by User U1ST074SORTS2
> \rdenison. The last step to run was step 1 (Step 1)."
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
> step failed."
> Any other ideas? I'm really interested in resolving this
> because my Xaction log gets huge, especially on a
> distributor server.
> Roger.
> >--Original Message--
> >All right guys are really helping me out. I looked up
> >the proble and it has come down to the following The
> >maintenace Plan says that it cannot do transaction log
> >backups on the master and the msdb databases.
> >
> >My question now is how can I exclude those 2 DB's from
> >the Transaction log backup portion of the maintenance
> >Plan?
> >
> >>--Original Message--
> >>
> >>
> >>Hello Jim,
> >>
> >>First of all, thanks to Tibor for pointing you in the
> >right direction.
> >>Just as an additional information,
> >>you can refer to the following article which explains
> >how to t-shoot
> >>General SQL Maint Wiz failures,
> >>
> >>INF: Troubleshooting Database Maintenance Plan Failures
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;288577
> >>
> >>In case, you are doing Transaction log backups of one of
> >the databases
> >>which is set to
> >>Simple Recovery Mode, you might want to refer to
> >following article
> >>explaining this scenario,
> >>
> >>BUG: Expired Transaction Log Backups May Not Be Deleted
> >by Maintenance Plan
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;303292
> >>
> >>Please let us know if these suggestions resolve your
> >issue, if not , feel
> >>free to post any further questions you have.
> >>
> >>Thanks for posting to MSDN Managed Newsgroup.
> >>
> >>Vikrant Dalwale
> >>
> >>Microsoft SQL Server Support Professional
> >>
> >>Microsoft highly recommends to all of our customers
> >that they visit the
> >>http://www.microsoft.com/protect site and perform the
> >three straightforward
> >>steps listed to improve your computer's security."
> >>This posting is provided "AS IS" with no warranties, and
> >confers no rights.
> >>
> >>
> >>
> >>--
> >>Content-Class: urn:content-classes:message
> >>From: "Jim" <jim.abel@.lmco.com>
> >>Sender: "Jim" <jim.abel@.lmco.com>
> >>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
> >><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
> >>Subject: Re: Errors during Maintenance Plan execution
> >>Date: Tue, 9 Dec 2003 08:46:52 -0800
> >>Lines: 71
> >>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="iso-8859-1"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
> >>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: cpmsftngxa07.phx.gbl
> >>Xref: cpmsftngxa07.phx.gbl
> >microsoft.public.sqlserver.server:319894
> >>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>That helped. So now I have an error with the
> >transaction
> >>log backup portion of the Maintenace Plan.
> >>
> >>Here is the erro
> >>
> >>========================================================> >==> >>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> >>failed. [SQLSTATE 42000] (Error 22029). The step
> >failed.
> >>===============================================> >>
> >>I can't find this in the error logs or the backup log
> >so
> >>I'm not sure what is happening
> >>--Original Message--
> >>You've checked the option to "Attempt to repair minor
> >>problems" for which
> >>SQL Server tries to set the db in single user mode
> >which
> >>will fail if you
> >>have users in the database. Remove that option, if you
> >>do get a problem with
> >>the database, you want to know about it and be there
> >to
> >>make a conscious
> >>decision of your actions at that stage.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at:
> >>http://groups.google.com/groups?
> >>oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Jim" <jim.abel@.lmco.com> wrote in message
> >>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying
> >to
> >>use
> >> the Maintenance Plan to backup all databses in our
> >SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases
> >generate
> >> the same error message
> >>
> >> Log portion==============================> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
> >5070:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >Database
> >> state cannot be changed while other users are using
> >the
> >> database 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: Check Data and Index
> >>Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
> >7919:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in
> >single
> >> user mode.
> >>
> >> The following errors were found:
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in
> >single
> >> user mode.
> >> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >>
> >>
> >>.
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Errors creating push subscriptions...

Hi All,
Had to recreate our SQLServer instance after a rebulid of
the server. Unfortuantely I was not able to
recreate/reload a backup so had to create from scratch.
In trying to setup replication I have the following error
message trying to create a push subscriptions:
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
'FHMYDATABSE: Error 14053: Cannot load the DLL
replincrementlsn extended procedure, or one of the DLLs it
references. Reason: 126(The specified module could not be
found.).
The subscription could not be updated at this time.
The subscription could not be created.'
Any ideas? I tried locating the extended proc mention but
no luck. Any help very much appreciated, as I've had this
problem for about 2 weeks now and the powers that bey want
a swift fix! Thanks...
Davej
Dave,
perhaps your service pack failed to fully install. Have a look at sqlsp.log
file from the c:\windows directory this might shed some light on any failed
actions during the sp installation.
HTH,
Paul Ibison
|||Thanks Paul for the note. I re-ran the sp update and it
must of cured something because the first error has gone
but I get the following!
FHMYDATABASE: Error 515: Cannot insert the value NULL
into column 'step_name', table 'msdb.dbo.sysjobsteps';
column does not allow nulls. INSERT fails.
why? any ideas?
Cheers...

>--Original Message--
>Dave,
>perhaps your service pack failed to fully install. Have a
look at sqlsp.log
>file from the c:\windows directory this might shed some
light on any failed
>actions during the sp installation.
>HTH,
>Paul Ibison
>
>.
>
|||Dave,do you have any remaining subscriptions/jobs from the previous
implementation of replication perhaps? You might try running
sp_remopvedbreplication on the databases involved prior to setting it up
again. If there are any orphaned records in the table
MSreplication_subscriptions on the subscriber database then these can be
deleted. After that, refresh the jobs folder and then implement as per
normal.
HTH,
Paul Ibison
|||Hi Paul,
Did what you suggested. Went right back to basics removing
replication from the database. Then went trough the
procedure using stored procs insetadt of EM. Came to
creating a push sub and got the following, bit more
detailed errors:
Server: Msg 515, Level 16, State 2, Procedure
sp_add_jobstep_internal, Line 129
Cannot insert the value NULL into column 'step_name',
table 'msdb.dbo.sysjobsteps'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
Server: Msg 14266, Level 16, State 1, Procedure
sp_verify_jobstep, Line 46
The specified '@.step_id' is invalid (valid values are:
1..1).
Server: Msg 14070, Level 16, State 1, Procedure
sp_changesubstatus, Line 993
Could not update the distribution database subscription
table. The subscription status could not be changed.
Server: Msg 14057, Level 16, State 1, Procedure
sp_addsubscription, Line 971
The subscription could not be created.
the script is:
use qboard
go
/* adds a publication to the database*/
exec sp_addpublication @.publication = 'Q Published Data
(from Script)'
,@.sync_method = 'native'
,@.repl_freq = 'continuous'
,@.status = 'active'
,@.allow_push = 'true'
go
/* adds a subscription to the publication just defined */
exec sp_addsubscription @.publication = 'Q Published Data
(from Script)'
,@.subscriber = 'FHQDB'
,@.subscription_type = 'push'
go
Is there someting I'm missing?
Thanks for any insights...
Davej

>--Original Message--
>Dave,do you have any remaining subscriptions/jobs from
the previous
>implementation of replication perhaps? You might try
running
>sp_remopvedbreplication on the databases involved prior
to setting it up
>again. If there are any orphaned records in the table
>MSreplication_subscriptions on the subscriber database
then these can be
>deleted. After that, refresh the jobs folder and then
implement as per
>normal.
>HTH,
>Paul Ibison
>
>.
>
|||Davej,
there seems to be some parts of you script missing. Here is a script for
transactional replication of CustomerDemographics:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'Northwind', @.optname = N'publish',
@.value = N'true'
GO
use [Northwind]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'Northwindcds', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of Northwind database from Publisher
PLL-DT-16.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'PLL-DT-16-Northwind-4'
exec sp_addpublication_snapshot @.publication =
N'Northwindcds',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 222700,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'PLL-DT-16-Northwind-Northwindcds-10'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'PLL-DT-16\xxx
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'domain\vvv'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'Northwindcds', @.article =
N'CustomerDemographics', @.source_owner = N'dbo', @.source_object =
N'CustomerDemographics', @.destination_table = N'CustomerDemographics', @.type
= N'logbased', @.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3, @.status =
16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
sp_MSupd_CustomerDemographics', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'Northwindcds', @.article = N'all',
@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
HTH,
Paul Ibison
|||Paul,
with these stored procs, do you need to specify all the
prameters or can some be defaulted? I'll use your example
as a template...
Thanx
davej
>--Original Message--
>Davej,
>there seems to be some parts of you script missing. Here
is a script for
>transactional replication of CustomerDemographics:
>-- Enabling the replication database
>use master
>GO
>exec sp_replicationdboption @.dbname = N'Northwind',
@.optname = N'publish',
>@.value = N'true'
>GO
>use [Northwind]
>GO
>-- Adding the transactional publication
>exec sp_addpublication @.publication = N'Northwindcds',
@.restricted =
>N'false', @.sync_method = N'native', @.repl_freq =
N'continuous', @.description
>= N'Transactional publication of Northwind database from
Publisher
>PLL-DT-16.', @.status = N'active', @.allow_push = N'true',
@.allow_pull =
>N'true', @.allow_anonymous = N'false',
@.enabled_for_internet = N'false',
>@.independent_agent = N'false', @.immediate_sync =
N'false', @.allow_sync_tran
>= N'false', @.autogen_sync_procs = N'false', @.retention =
336,
>@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder
= N'true',
>@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login
= N'anonymous',
>@.allow_dts = N'false', @.allow_subscription_copy =
N'false',
>@.add_to_active_directory = N'false', @.logreader_job_name =
>N'PLL-DT-16-Northwind-4'
>exec sp_addpublication_snapshot @.publication =
>N'Northwindcds',@.frequency_type = 4, @.frequency_interval
= 1,
>@.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 1,
>@.frequency_subday = 1, @.frequency_subday_interval = 0,
@.active_start_date =
>0, @.active_end_date = 0, @.active_start_time_of_day =
222700,
>@.active_end_time_of_day = 0, @.snapshot_job_name =
>N'PLL-DT-16-Northwind-Northwindcds-10'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'BUILTIN\Administrators'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'distributor_admin'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'PLL-DT-16\xxx
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'domain\vvv'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'sa'
>GO
>-- Adding the transactional articles
>exec sp_addarticle @.publication = N'Northwindcds',
@.article =
>N'CustomerDemographics', @.source_owner = N'dbo',
@.source_object =
>N'CustomerDemographics', @.destination_table =
N'CustomerDemographics', @.type
>= N'logbased', @.creation_script = null, @.description =
null,
>@.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status =
>16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
>sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
>sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
>sp_MSupd_CustomerDemographics', @.filter = null,
@.sync_object = null,
>@.auto_identity_range = N'false'
>GO
>-- Adding the transactional subscription
>exec sp_addsubscription @.publication = N'Northwindcds',
@.article = N'all',
>@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep',
@.sync_type =
>N'automatic', @.update_mode = N'read only', @.offloadagent
= 0,
>@.dts_package_location = N'distributor'
>GO
>
>HTH,
>Paul Ibison
>
>.
>
|||Dave,
the vast majority can be missed out - too many to mention, but details in
BOL (see sp_addpublication, sp_addarticle). You could get EM to generate the
script from another server if you restore your database and create the
publication there, and want to use scripts.
Regards,
Paul Ibison