database maintenance plan

i currently am using: sharepoint portal 2003 / windows sharepoint
services with sp2 and sql 2000 with sp4.

i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added - SupportComputedColum
n to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.

i tried adding something like this:

use master
go
alter database db1_prod set single_user with rollback immediate
go

...and it didn't work. anyone have a better idea?

thanks!

> but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.

Backup log is perfectly possible while users are connected to the database. 
My guess is that your
integrity check has the "attempt to repair minor problems" checked. This is 
a bad option in the
first place. I suggest you remove that option.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


<eric.olson@gmail.com> wrote in message
news:1164121998.074547.209600@f16g2000cwb.googlegroups.com...
>i currently am using: sharepoint portal 2003 / windows sharepoint
> services with sp2 and sql 2000 with sp4.
>
> i'm trying to develop a database maintenance plan in sql 2000. so far,
> to get past the
> QUOTED_IDENTIFIER, i've added - SupportComputedColum
n to the T-SQL code
> in the steps, but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
>
> i tried adding something like this:
>
> use master
> go
> alter database db1_prod set single_user with rollback immediate
> go
>
> ...and it didn't work. anyone have a better idea?
>
> thanks!
>

Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."

You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.

-Sue

On 21 Nov 2006 07:13:18 -0800, eric.olson@gmail.com wrote:

>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added - SupportComputedColum
n to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>
>i tried adding something like this:
>
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>
>...and it didn't work. anyone have a better idea?
>
>thanks!

where i'm at now:

Backup cannot be performed on database 'master'. The sub task is
ignored.
Backup cannot be performed on database 'msdb'. The sub task is ignored.
Sue Hoegemeier  wrote:[color=darkred
]
> Regarding the statement:
>  "but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs."
>
> You can do database and log backups while the database is in use. You
> don't need to drop any connections to perform the backup.
>
> -Sue
>
> On 21 Nov 2006 07:13:18 -0800, eric.olson@gmail.com wrote:
> 

eric.olson@gmail.com  wrote:
> where i'm at now:
>
> Backup cannot be performed on database 'master'. The sub task is
> ignored.
> Backup cannot be performed on database 'msdb'. The sub task is ignored.

That means you're attempting to do a transaction log backup on those
databases, and they're in Simple recovery mode.  You can't backup the
t-log on a Simple mode database.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?

Tibor Karaszi  wrote:[color=darkred
] 
>
> Backup log is perfectly possible while users are connected to the database
. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This i
s a bad option in the
> first place. I suggest you remove that option.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> <eric.olson@gmail.com> wrote in message
> news:1164121998.074547.209600@f16g2000cwb.googlegroups.com... 

right.... but a microsoft article also said not to put master or msdb
in full mode.

should i put it in full recovery mode?

Tracy McKibben  wrote:
> eric.olson@gmail.com wrote: 
>
> That means you're attempting to do a transaction log backup on those
> databases, and they're in Simple recovery mode.  You can't backup the
> t-log on a Simple mode database.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

eric.olson@gmail.com  wrote:
> right.... but a microsoft article also said not to put master or msdb
> in full mode.
>
> should i put it in full recovery mode?
>

No, just don't include those databases in the transaction log backup
job.  Create two plans - one for system DB's, one for user DB's.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

that's exactly what i ended up doing :).

- plan1 - backup user databases w/transaction logs, database
optimization, and error checking
- plan2 - backup system databases and check for errors

thanks.
Tracy McKibben  wrote:
> eric.olson@gmail.com wrote: 
>
> No, just don't include those databases in the transaction log backup
> job.  Create two plans - one for system DB's, one for user DB's.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com