I have a server that until yesterday had SQL 7 on it, and it ran okay.

However, after upgrading from SQL 7 to SQL 2000, I have one job that won't
run. It's a scheduled job to be run each night, and it kicked out last
night as soon as it was scheduled.

A "normal" run of the job would give this output:

Output of sqlmaint.exe
----------------------------------------------------------------------------
---------------------------------------
Microsoft (R) SQLMaint Utility (Unicode), Version 7.00.961
Copyright (C) Microsoft Corporation, 1995 - 1998

Logged on to SQL Server 'SISDB2' as 'MYDOMAIN\mylogin' (trusted)
Starting maintenance plan 'sales backup - tape' on Wed Jul 07 03:00:02 2004

End of maintenance plan 'sales backup - tape' on Wed Jul 07 03:00:02 2004

SQLMAINT.EXE Process Exit Code: 0 (Success)
(10 rows(s) affected). The step succeeded.

The "bad" run's output was:

Executed as user: MYDOMAIN\mylogin. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.

I checked the space on the drive, and it was under 10 gigs. I've created
some space, but don't know if that's enough.

From what I understand, I want to run a DBCC SHRINKDATABASE manually on it,
but don't know if space is my main problem.

The TSQL that was actually running was:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID
669422CD-7916-11D4-95F6-00A0C9B148AA -WriteHistory -VrfyBackup -BkUpMedia
TAPE -BkUpLog "\\.\Tape0" '

Any ideas as to what I might need to do to get this to run?

So far, that has been the only unknown hiccup in upgrading to SQL 2000.

Any help/advice appreciated.

SC

Do you have enough space?
Have the maintenance plan log to text file.
Copy and paste the command into QA and execute it there, you should get
more output that way.


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

There's about 18 gigs of space on the drive. The last few backups have been
in the 8-10 gig size for the backup.

The output from Query Analyzer is:

output
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
Logged on to SQL Server 'CORP-SISDB2'
as 'sa' (non-trusted)
NULL
Starting maintenance plan 'sfa backup - tape' on 7/8/2004 11:41:28 AM
NULL
Backup can not be performed on database 'sfa'. This sub task is ignored.
NULL
NULL
NULL
End of maintenance plan 'sfa backup - tape' on 7/8/2004 11:41:28 AM
NULL
SQLMAINT.EXE Process Exit Code: 1 (Failed)
NULL

(17 row(s) affected)

Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.


SC



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Here's the output from the job, once I turned on the Reporting for it:


Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SISDB2' as 'MYDOMAIN\mylogin' (trusted)
Starting maintenance plan 'sfa backup - tape' on 7/8/2004 12:01:12 PM
Backup can not be performed on database 'sfa'. This sub task is ignored.

End of maintenance plan 'sfa backup - tape' on 7/8/2004 12:01:12 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

Emailing report to operator 'Eddie & Steve'


SC

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

What is special about database sfa?


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

It is the sales information database that is on each of the servers.

As far as "special" - how do you mean that? By a particular type of SQL
database, what we use it for, or what?

SC



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I mean does it have any particulr settings

dbo use only
read only


The error message said that this database could not be backed up. I am
trying to figure out why. This is a FULL backup we are trying to do
right?


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Allan:

the job name is "Transaction Log Backup Job for DB Maintenance Plan 'sfa
backup - tape'".

In looking at the Database Maintenance plan under the Management folder, I
see that it backs up the sfa database, the Optimizations tab has nothing
checked on it, and 10% is the value in the top percentage box. Nothing is
checked on the Integrity tab, but the "include indexes" is selected but
greyed out. Nothing on the Complete Backup is checked,, but the Verify box
is checked but greyed out. On the Transaction log backup tab, the Back Up
the transaction log of the database is checked, as is Verify the Integrity
of the backup of completion box. It's set to go to tape, by \\.\Tape0. The
other boxes are greyed out, but the one that's selected is Use the Default
Backup directory. The job is scheduled for each day. The reporting tab has
the Text reports boxes checked and also the History to table
msdb.dbo.sysdbmaintplan_history is checked, as is the limit rows to 1000.

Steve


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Have you performed a FULL backup yet on this DB. If not then there is
no way a T Log backup will work. The lack of a check is the Full
backup tab of your maintenance lans would seem to indicate this as
well.

Also check your Recovery model <> SIMPLE


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

I performed a backup of it -

go to Management, select backup, then right click and back up database. It
completed fine.

If I go back in and add a Database Maintenance plan, I created one that was
both an integrity check & a transaction log backup, it appeared to complete.

Out put of that job was:


Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SISDB2' as MYDOMAIN\mylogin (trusted)
Starting maintenance plan 'Test DB Maintenance Plan - Steve' on 7/9/2004
1:45:00 PM
[1] Database sfa: Check Data and Index Linkage...

** Execution Time: 0 hrs, 2 mins, 43 secs **

End of maintenance plan 'Test DB Maintenance Plan - Steve' on 7/9/2004
1:47:43 PM
SQLMAINT.EXE Process Exit Code: 0 (Success)

Emailing report to operator 'Eddie & Steve'

I took the same job and edited it, and made it a transaction log backup
only, and it failed. Output is:


Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SISDB2' as 'MYDOMAIN\mylogin' (trusted)
Starting maintenance plan 'Test DB Maintenance Plan - Steve' on 7/9/2004
2:00:01 PM
Backup can not be performed on database 'sfa'. This sub task is ignored.

End of maintenance plan 'Test DB Maintenance Plan - Steve' on 7/9/2004
2:00:01 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

Emailing report to operator 'Eddie & Steve'

One other question - once I figure out what's going on with this maintenance
plan, how can I create a job in the SQL Server Agent Jobs folder to do this,
like the original TSQL I posted?

Thanks for the help so far,

Steve







"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote