Database Maintenance Plan Grief

I've been setting up database maintenance plans for years, but lately, I've
been stumped by this particular database maintenance plan.
All it contains are full backup routines for 2 databases in Simple Recovery
Mode in a named instance to write out to a local drive. Every aspect of the
plan deals with local resources: e.g. not master / target server
relationships, no UNC drive paths, no linked servers, everything as simple
as can be. Drive space is more than ample. However, executing the database
maintenance plan-created job will fail. Maintenance Plan reports show
nothing. Searching google and KB has yielded nothing.
When I create a separate full backup job by using the 'BACKUP DATABASE'
command that virtually has the same characteristics as the maintenance plan
(same schedule, dumps to the same dir), this particular job runs flawlessly.
The job and SQL Server Agent are assigned to accounts that have unrestricted
access to the system at hand.
However, what I did was take the command from the job created by the
Database Maintenance Plan and attempt to run it in QA. Here are the
results.
COMMAND
--
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft SQL
Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
[Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
NULL
(7 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Here's my system information:
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )I would do two things.
1. Try the basic same plan, but using sqlmaint.exe
2. Check server network utility and client network utility (for instance if you have configured some
alias).
The error suggests that sqlmaint.exe can't find the sql server. Perhaps the sql server doesn't
listen to the IP netlib. Also, check the errorlog file. This should include info about the netlibs
listened to and also if there were any errors with this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H Lee" <anon@.anon.com> wrote in message news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
> I've been setting up database maintenance plans for years, but lately, I've been stumped by this
> particular database maintenance plan.
> All it contains are full backup routines for 2 databases in Simple Recovery Mode in a named
> instance to write out to a local drive. Every aspect of the plan deals with local resources: e.g.
> not master / target server relationships, no UNC drive paths, no linked servers, everything as
> simple as can be. Drive space is more than ample. However, executing the database maintenance
> plan-created job will fail. Maintenance Plan reports show nothing. Searching google and KB has
> yielded nothing.
> When I create a separate full backup job by using the 'BACKUP DATABASE' command that virtually has
> the same characteristics as the maintenance plan (same schedule, dumps to the same dir), this
> particular job runs flawlessly.
> The job and SQL Server Agent are assigned to accounts that have unrestricted access to the system
> at hand.
> However, what I did was take the command from the job created by the Database Maintenance Plan and
> attempt to run it in QA. Here are the results.
> COMMAND
> --
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program
> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program Files\Microsoft SQL
> Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>
> output
> -----------
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
> NULL
> (7 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
>
> Here's my system information:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>|||I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
follows:
d:
cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
sqlmaint
-S <appropriate_server_name>
-U "_test_sa"
-P "_test_sa"
-D master
and received the following error:
Executed as user: <domain_name>\<user_name>. The process could not be
created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason: Access
is denied). The step failed.
I'll check the server network utility on the server soon. Client network
Utility seems OK-- my SQL EM and QA have been connecting fine to the server
and are still able to connect successfully.
ERRORLOG's show that SQL is successfully listening on TCP. This server has
had many users who successfully connect to it multiple times each day.
"SQL server listening on TCP, Shared Memory, Named Pipes"
I do see one error before this, however:
"Resource Manager Creation Failed: Result Code = 0x8004d01c"
Any help would be appreciated.
Thanks,
- H -
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
>I would do two things.
> 1. Try the basic same plan, but using sqlmaint.exe
> 2. Check server network utility and client network utility (for instance
> if you have configured some alias).
> The error suggests that sqlmaint.exe can't find the sql server. Perhaps
> the sql server doesn't listen to the IP netlib. Also, check the errorlog
> file. This should include info about the netlibs listened to and also if
> there were any errors with this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "H Lee" <anon@.anon.com> wrote in message
> news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
>> I've been setting up database maintenance plans for years, but lately,
>> I've been stumped by this particular database maintenance plan.
>> All it contains are full backup routines for 2 databases in Simple
>> Recovery Mode in a named instance to write out to a local drive. Every
>> aspect of the plan deals with local resources: e.g. not master / target
>> server relationships, no UNC drive paths, no linked servers, everything
>> as simple as can be. Drive space is more than ample. However, executing
>> the database maintenance plan-created job will fail. Maintenance Plan
>> reports show nothing. Searching google and KB has yielded nothing.
>> When I create a separate full backup job by using the 'BACKUP DATABASE'
>> command that virtually has the same characteristics as the maintenance
>> plan (same schedule, dumps to the same dir), this particular job runs
>> flawlessly.
>> The job and SQL Server Agent are assigned to accounts that have
>> unrestricted access to the system at hand.
>> However, what I did was take the command from the job created by the
>> Database Maintenance Plan and attempt to run it in QA. Here are the
>> results.
>> COMMAND
>> --
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft SQL
>> Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
>> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
>> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>>
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
>> Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
>> (Connect()).
>> NULL
>> (7 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>>
>> Here's my system information:
>> --
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>|||It turns out that, indeed, an alias in the SQL Server's own Client Network
Utility was referring to itself by its own NetBIOS name but had a
manually-set port number that was different from what the server was
actually listening on.
Craziness.
Thanks for your help.
"H Lee" <anon@.anon.com> wrote in message
news:%237eYXQzVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
>follows:
>
> d:
> cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
> sqlmaint
> -S <appropriate_server_name>
> -U "_test_sa"
> -P "_test_sa"
> -D master
>
> and received the following error:
> Executed as user: <domain_name>\<user_name>. The process could not be
> created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason:
> Access is denied). The step failed.
> I'll check the server network utility on the server soon. Client network
> Utility seems OK-- my SQL EM and QA have been connecting fine to the
> server and are still able to connect successfully.
> ERRORLOG's show that SQL is successfully listening on TCP. This server
> has had many users who successfully connect to it multiple times each day.
> "SQL server listening on TCP, Shared Memory, Named Pipes"
> I do see one error before this, however:
> "Resource Manager Creation Failed: Result Code = 0x8004d01c"
> Any help would be appreciated.
> Thanks,
> - H -
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
>>I would do two things.
>> 1. Try the basic same plan, but using sqlmaint.exe
>> 2. Check server network utility and client network utility (for instance
>> if you have configured some alias).
>> The error suggests that sqlmaint.exe can't find the sql server. Perhaps
>> the sql server doesn't listen to the IP netlib. Also, check the errorlog
>> file. This should include info about the netlibs listened to and also if
>> there were any errors with this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "H Lee" <anon@.anon.com> wrote in message
>> news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
>> I've been setting up database maintenance plans for years, but lately,
>> I've been stumped by this particular database maintenance plan.
>> All it contains are full backup routines for 2 databases in Simple
>> Recovery Mode in a named instance to write out to a local drive. Every
>> aspect of the plan deals with local resources: e.g. not master / target
>> server relationships, no UNC drive paths, no linked servers, everything
>> as simple as can be. Drive space is more than ample. However,
>> executing the database maintenance plan-created job will fail.
>> Maintenance Plan reports show nothing. Searching google and KB has
>> yielded nothing.
>> When I create a separate full backup job by using the 'BACKUP DATABASE'
>> command that virtually has the same characteristics as the maintenance
>> plan (same schedule, dumps to the same dir), this particular job runs
>> flawlessly.
>> The job and SQL Server Agent are assigned to accounts that have
>> unrestricted access to the system at hand.
>> However, what I did was take the command from the job created by the
>> Database Maintenance Plan and attempt to run it in QA. Here are the
>> results.
>> COMMAND
>> --
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft
>> SQL Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
>> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
>> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>>
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
>> Server Driver][TCP/IP Sockets]SQL Server does not exist or access
>> denied.
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
>> (Connect()).
>> NULL
>> (7 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>>
>> Here's my system information:
>> --
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>>
>