Maintenance Plan Failure

SQL Server 2000 sp3a, running on Windows 2003 server, sp1.
I have two jobs, based on maintenance plans, that have been running fine for
over two years. One updates statistics for a database, and the other rebuild
s
indexes. Yesterday, they both started failing. The only info I can get is
from the Maintenance Plan History, both messages are similar:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER'.
and
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following
SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
quoted_identifier is set to 1, and has not been changed. Nothing has changed
at all with the database and server.
I created two new plans to duplicate the above, and each of them fails with
the same messages.
I have no clue as to why these all of a sudden started to fail, and any
assistance would be greatly appreciated.
TomTSorry for the double post, I got a message saying the post was unsuccessful,
and posted again (and got the same message, by the way)...
"TomT" wrote:

> SQL Server 2000 sp3a, running on Windows 2003 server, sp1.
> I have two jobs, based on maintenance plans, that have been running fine f
or
> over two years. One updates statistics for a database, and the other rebui
lds
> indexes. Yesterday, they both started failing. The only info I can get is
> from the Maintenance Plan History, both messages are similar:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> and
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the followi
ng SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> quoted_identifier is set to 1, and has not been changed. Nothing has chang
ed
> at all with the database and server.
> I created two new plans to duplicate the above, and each of them fails wit
h
> the same messages.
> I have no clue as to why these all of a sudden started to fail, and any
> assistance would be greatly appreciated.
> TomT|||Hi Tom,
That's OK, I am composing a reply to that thread
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Maintenance Plan Failure

SQL Server 2000 sp3a, running on Windows 2003 server, sp1.
I have two jobs, based on maintenance plans, that have been running fine for
over two years. One updates statistics for a database, and the other rebuilds
indexes. Yesterday, they both started failing. The only info I can get is
from the Maintenance Plan History, both messages are similar:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.
and
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
quoted_identifier is set to 1, and has not been changed. Nothing has changed
at all with the database and server.
I created two new plans to duplicate the above, and each of them fails with
the same messages.
I have no clue as to why these all of a sudden started to fail, and any
assistance would be greatly appreciated.
TomT
Sorry for the double post, I got a message saying the post was unsuccessful,
and posted again (and got the same message, by the way)...
"TomT" wrote:

> SQL Server 2000 sp3a, running on Windows 2003 server, sp1.
> I have two jobs, based on maintenance plans, that have been running fine for
> over two years. One updates statistics for a database, and the other rebuilds
> indexes. Yesterday, they both started failing. The only info I can get is
> from the Maintenance Plan History, both messages are similar:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> and
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> quoted_identifier is set to 1, and has not been changed. Nothing has changed
> at all with the database and server.
> I created two new plans to duplicate the above, and each of them fails with
> the same messages.
> I have no clue as to why these all of a sudden started to fail, and any
> assistance would be greatly appreciated.
> TomT
|||Hi Tom,
That's OK, I am composing a reply to that thread
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Maintenance Plan Failure

SQL Server 2000 sp3a, running on Windows 2003 server, sp1.
I have two jobs, based on maintenance plans, that have been running fine for
over two years. One updates statistics for a database, and the other rebuilds
indexes. Yesterday, they both started failing. The only info I can get is
from the Maintenance Plan History, both messages are similar:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.
and
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
quoted_identifier is set to 1, and has not been changed. Nothing has changed
at all with the database and server.
I created two new plans to duplicate the above, and each of them fails with
the same messages.
I have no clue as to why these all of a sudden started to fail, and any
assistance would be greatly appreciated.
TomTSorry for the double post, I got a message saying the post was unsuccessful,
and posted again (and got the same message, by the way)...
"TomT" wrote:
> SQL Server 2000 sp3a, running on Windows 2003 server, sp1.
> I have two jobs, based on maintenance plans, that have been running fine for
> over two years. One updates statistics for a database, and the other rebuilds
> indexes. Yesterday, they both started failing. The only info I can get is
> from the Maintenance Plan History, both messages are similar:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> and
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> quoted_identifier is set to 1, and has not been changed. Nothing has changed
> at all with the database and server.
> I created two new plans to duplicate the above, and each of them fails with
> the same messages.
> I have no clue as to why these all of a sudden started to fail, and any
> assistance would be greatly appreciated.
> TomT|||Hi Tom,
That's OK, I am composing a reply to that thread :)
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Maintenance Plan backup fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
george sarlas
gsarlas@.yahoo.com
A few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
george sarlas
gsarlas@.yahoo.com
|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegr oups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
> Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>
sql

Maintenance Plan backup fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
--
george sarlas
gsarlas@.yahoo.comA few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
--
george sarlas
gsarlas@.yahoo.com|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegroups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
> Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>

Maintenance Plan backup fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_2005080904
49.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE i
s
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
george sarlas
gsarlas@.yahoo.comA few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
george sarlas
gsarlas@.yahoo.com|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegroups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQ
L
> Backups\LW1SQL1\MyDatabase_db_2005080904
49.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE
is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>

Mail notifications for scheduled jobs

I have a sql server 2000 sp3a box with 4 named instances.

I have read all of the articles and I cannot get the 'send mail' stuff working.

I have a mailbox associated with the Domanin account that starts the sql server and sql server agent services. I installed Outlook and created a mail profile for this account. I bounced the SQL server agent on one of the Instances but when I go into the properties for the Agent, on the General Tab, the option to choose a mail profile is GREYED out. I tried 'xp_startmail' and I get this error: xp_startmail: failed with mail error 0x80040111

can anyone help???Are you logging on to the server as the account SQL runs under? You will need to be logged onto to the server with the service account in order to set the mail properties in the agent|||are you sure you have covered all of the basics. does the sql and sql agent services run under a network account that maps to the exchange account you are attempting to use?

have you attempted to use the outlook client installed on the db server.|||You've got about 9 months until maintstream support for SQL 2000 ends. Upgrade to SQL 2005 and use the Database Mail feature (a lightweight SMTP client, instead of the clunky MAPI compliant interface).

In case that is not an option, here are some MS KB articles on SQL 2000 and setting up mail profiles.

http://support.microsoft.com/kb/315886

http://support.microsoft.com/default.aspx/kb/281293

http://support.microsoft.com/kb/263556/EN-US/

Some CRITICAL things to remember:

1. Outlook is single-threaded; if it pops an error message at the console, it can and will lock up SQL's job system.

2. SQL Mail and SQL Agent mail do not have to use the same profile. However, you must set up each profile separately (ie, to use alerts you must set up SQL Agent Mail, to use xp_sendmail you must set up SQL Mail). The two features are set up in different places (SQL Agent mail is set up by right-clicking on SQL Agent and selecting properties; SQL Mail is set up under support services).

3. You will have to configure SQL Mail/SQL Agent Mail separately for each instance. You might want to consider a separate profile for each. Be careful of the overhead associated with running four MAPI client instances (plus for SQL instances) on the same server.

Having said all that, I don't really recommed using SQL Agent Mail or SQL mail on a production server. Like I said, upgrade to SQL 2005.

Regards,

hmscott