Error 207: plan

Showing posts with label plan. Show all posts

Monday, March 26, 2012

Error 823 with msdb

Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the followin
g
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage

But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should no
t
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msd
b
> is fixed, I am dead in the water (the existing plans still run fine). And
I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas
"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens

Error 823 with msdb

Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the following
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>
|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens
|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage

But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should not
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msdb
> is fixed, I am dead in the water (the existing plans still run fine). And I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>
|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens
|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas

"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens

Error 823 with msdb

When I try to create a new Maintenance Plan or revise an existing one I
get the following error:
Error: 823, Severity: 24, State: 2
Microsoft SQL-DMO (ODBC SQL State:HY000)
Error 823: I/O error (torn page) detected during read at offsett
0x000000000f0000 in file 'd:\sqldata\MSSQL\data\msdbdata.mdf'.
There are no known disk problems that I can detect. The existing
Maintenance Plans run fine. I do not have a backup of msdb prior to when
this problem started (I have no idea of when it started, it only appears
when I try to modify the maintenance plan).
I can easily recreate the maintenance plans but I need to fix the problem
with msdb first. Can msdb be repaired, can I empty it to start fresh, can I
use the msdb from an original install'
Any suggestions are greatly appreciated.
Thanks
Russ StevensI just tried to repair msdb
DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
It fixed 6 errors - I now have a bigger mess <g> - am getting the following
error (and other strange errors) when I try to modify a Maintenance Plan;
Error 170: Line 1: Incorrect syntax near ','.
I need a clean working msdb - appreciate any help.
Thanks
Russ Stevens|||Anytime you run a dbcc checkdb with repair_allow_data_loss you risk data
integrity. there is no way to know what kind of data is lost etc. In
your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.
Alternatively you can always restore from backup :)
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the following
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage :) But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should not
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msdb
> is fixed, I am dead in the water (the existing plans still run fine). And I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas
"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevenssql

Friday, March 9, 2012

Error 4305 - Log-Shipping

Hi-
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.
Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwise
you would loose transactions.
Regards
Mike
"Jez" wrote:

> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup and
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing this
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think they
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>
|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...[vbcol=seagreen]
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:

Error 4305 - Log-Shipping

Hi-
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwis
e
you would loose transactions.
Regards
Mike
"Jez" wrote:

> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup a
nd
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing th
is
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think the
y
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...[vbcol=seagreen]
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
>

Error 4305 - Log-Shipping

Hi-
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwise
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup and
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing this
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think they
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
>> Hi-
>> SQL 2000
>> Win 2k sp4
>> We have created a maintenance plan to ship one of our databases to a
>> secondary server. The initial snapshop shipped fine but we get error 4305
>> when trying to apply subsequent shipped tran logs.
>> The error says that the transaction log is too new.
>> We have another maintenance plan running on this DB that does backing
>> up(complete and tran logs) and it did do a tran-log backup after the
>> log-shipping snapshot. The log-shipping plan then made a tran log backup
>> and
>> shipped it(which is too new).
>> I am trying to clarify if we can have two maintenance plans on the same
>> db(one for backup and one for log-shipping). Is this is what is causing
>> this
>> error since they are overlapping.
>> I have not been able to find any info on this and am inclined to think
>> they
>> are and I can only have oneplan that does all the backing up and the
>> log-shipping.
>> Can anyone confirm?
>> Thanks.
>>

Wednesday, March 7, 2012

ERROR 3628 Floating point error

Our system just started receiving an error on the
optimization phase of backup maintenance plan. This is
during index rebuild.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628:
[Microsoft][ODBC SQL Server Driver][SQL Server]A floating
point exception occurred in the user process. Current
transaction is canceled.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
execution completed. If DBCC printed error messages,
contact your system administrator.
This system is Sql2k SP3. I have found a couple of hits on
floating point errors, but nothing directly related to
index rebuilds.
Any ideas?The first thing I'd do is to check if the database is clean using DBCC
CHECKDB and DBCC CHECKCATALOG.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Judy" <jranson@.harris.com> wrote in message
news:015a01c3be88$b4c5afb0$a401280a@.phx.gbl...
> Our system just started receiving an error on the
> optimization phase of backup maintenance plan. This is
> during index rebuild.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A floating
> point exception occurred in the user process. Current
> transaction is canceled.
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> execution completed. If DBCC printed error messages,
> contact your system administrator.
> This system is Sql2k SP3. I have found a couple of hits on
> floating point errors, but nothing directly related to
> index rebuilds.
> Any ideas?

Sunday, February 26, 2012

Error 3013

I have a database maintenance plan job that failed with the following error on sql server 2000

Executed as user: HACIEXXWVNDA\MH-GAGI-WEB01$. Operation on device '\\.\Tape0' exceeded retry count. [SQLSTATE 42000] (Error 3230) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Does anyone know why this happened? Any help is appreciated.

Thanks

Hi ..

I'm getting the same error; please anyone can help in the matter ASAP.

Thanks and regards.

|||

I'd check out the hardware, but in the mean time why don't you back up to a file instead of to tape, then backup the files to tape. It's a safer way to back up, and you're not relying on that particular tape drive to be available when you need to restore, should that need arise.

Error 3013

I have a database maintenance plan job that failed with the following error on sql server 2000

Does anyone know why this happened? Any help is appreciated.

Thanks

Hi ..

I'm getting the same error; please anyone can help in the matter ASAP.

Thanks and regards.

|||

Sunday, February 19, 2012

Error 22029 on maint jobs

OK this is a new error on me.

I have a SQL server 2000 on Windows Server 2003.

I have setup a Maint plan that backs up the user and system databases
and the transaction Logs on my user databases.

Does optimises and integrity checks on those databases as well.
I am not attempting to repair any minor problems.
I am not performing integrity checks before backing up the database
and transaction logs.

All of the databases that I am doing a transaction log backup on have
recovery mode of full.

When the jobs run they fail instantly with error code 22029.
Sqlmaint.exe failed to run. This was working up until the end of the
day on the Thursday before Good Friday. (last week) The transaction
log backup ran fine at 4:00 PM the same job failed at 8:00 PM. No
changes were made to the server during that time.
Another issue that started at the same time. I have a web page used to
grab data from a text file export and pull it into an AS400. I use
this server to house the DTS packages and stored procedures that the
process needs. I suddenly quit working when the jobs quit running.

We are in a mixed enviroment with most clients on AD and this server
and most others still in a NT 4 domain. We have been like this for at
least 3 months and everything has worked fine. I setup the various
users and systems the way the need to be, to work.

Originally the SQL Agent was running as a generic NT 4 Domain user with
local administrator rights to the local box and server role of system
administrator. The MSSQLSERVER and DTC services were running as the
built in system account.

Everything was happy then it broke.

When I reboot the server the jobs and the website work fine. Then
after a period of time that I have not figured out yet, but more than
15 minutes and less than 2 hours. Everything quits working.

I have changed all the services to run as a domain admin with SQL
system adminsitrator server role. Still same thing.

I have changed all the services to run as the built in system account.
Still same thing.

I run the sqlmaint from command line with the switches that I am using
in the maint jobs and it runs fine.

I have run dbcc checkdb on all the databases and they are fine.

The DTS packages and the stored procedures used by the website run fine
from with in Query analyzer.

Any help you can provide would be greatly appreciated.

I don′t know i this might help, but we experienced the exact same symptoms. And it started around the date you mentioned.

In our case we had a job that made a backup of 'All databases'. However we had 2 detached databases on our server, when I deleted those the job ran without errors!

Good luck

Wednesday, February 15, 2012

Error 1934 from maintenance plan

I'm getting an error resulting from one of the jobs for my maintenance plan:
Rebuilding indexes for table 'MyTable'
[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, ARITHABORT'.
Now, I know that there is an issue with indexes on computed columns and the
maintenance wizard. This table has a computed column, but there is no index
on that column (just a primary key on the table). Plus, there are several
other databases with the identical table (structure) that do NOT generate
this error. And the maintenance on this table worked fine until a month
ago. The structure did not change, but there could have been some records
inserted and then deleted.
I'm at a loss as to why this one database throws the error, whereas others
don't. Any ideas? We are SQL 2K SP3.Can you post the output of sp_helpindex and sp_helpstats for the table it's
failing on
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Microsoft Public" <allisonanderson_nospam@.visi.com> wrote in message
news:etIJuoqsDHA.536@.tk2msftngp13.phx.gbl...
I'm getting an error resulting from one of the jobs for my maintenance plan:
Rebuilding indexes for table 'MyTable'
[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, ARITHABORT'.
Now, I know that there is an issue with indexes on computed columns and the
maintenance wizard. This table has a computed column, but there is no index
on that column (just a primary key on the table). Plus, there are several
other databases with the identical table (structure) that do NOT generate
this error. And the maintenance on this table worked fine until a month
ago. The structure did not change, but there could have been some records
inserted and then deleted.
I'm at a loss as to why this one database throws the error, whereas others
don't. Any ideas? We are SQL 2K SP3.|||sp_helpindex:
PK__GREENSHEETITEM__00AA174D clustered, unique, primary key located on
PRIMARY RECORDID
sp_helpstats:
_WA_Sys_ADJOB_5F492382 ADJOB
_WA_Sys_BINDCOUNT_5F492382 BINDCOUNT
_WA_Sys_DEPARTMENTNUMBER_5F492382 DEPARTMENTNUMBER
_WA_Sys_DESCRIPTION_5F492382 DESCRIPTION
_WA_Sys_GREENSHEETNUMBER_5F492382 GREENSHEETNUMBER
_WA_Sys_ITEMCOUNT_5F492382 ITEMCOUNT
_WA_Sys_ITEMDATE_5F492382 ITEMDATE
_WA_Sys_LINETOTAL_5F492382 LINETOTAL
_WA_Sys_PRICE_5F492382 PRICE
The computed column is LINETOTAL, which is ITEMCOUNT * PRICE
I'm comparing similar output from sp_helpstats to other databases with this
table, and they either have no stats or just one on GREENSHEETNUMBER. I've
only selected a few dbs so far, but could this be the reason for the
difference?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uqxhDErsDHA.3236@.tk2msftngp13.phx.gbl...
> Can you post the output of sp_helpindex and sp_helpstats for the table
it's
> failing on
>|||i have the same problem. computed column with no index creates this error for
me too. weird thing is that i can drop the computed column, recreate it and the
maintenance plan optimization job will work. eventually, the error message will
start happening again. i have yet to figure out what is causing the error to
reappear after weeks or months of not appearing.
Microsoft Public wrote:
> I'm getting an error resulting from one of the jobs for my maintenance plan:
> Rebuilding indexes for table 'MyTable'
> [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, ARITHABORT'.
> Now, I know that there is an issue with indexes on computed columns and the
> maintenance wizard. This table has a computed column, but there is no index
> on that column (just a primary key on the table). Plus, there are several
> other databases with the identical table (structure) that do NOT generate
> this error. And the maintenance on this table worked fine until a month
> ago. The structure did not change, but there could have been some records
> inserted and then deleted.
> I'm at a loss as to why this one database throws the error, whereas others
> don't. Any ideas? We are SQL 2K SP3.|||I was actually thinking it might be caused by hypothetical indexes created
by the Index Tuning Wizard confusing the maintenance plan. If you drop the
_WA_Sys_LINETOTAL_5F492382 LINETOTAL statistics on the computed column, does
that have any effect on the ability to reindex the table ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Microsoft Public" <allisonanderson_nospam@.visi.com> wrote in message
news:eFKOyRrsDHA.684@.TK2MSFTNGP09.phx.gbl...
sp_helpindex:
PK__GREENSHEETITEM__00AA174D clustered, unique, primary key located on
PRIMARY RECORDID
sp_helpstats:
_WA_Sys_ADJOB_5F492382 ADJOB
_WA_Sys_BINDCOUNT_5F492382 BINDCOUNT
_WA_Sys_DEPARTMENTNUMBER_5F492382 DEPARTMENTNUMBER
_WA_Sys_DESCRIPTION_5F492382 DESCRIPTION
_WA_Sys_GREENSHEETNUMBER_5F492382 GREENSHEETNUMBER
_WA_Sys_ITEMCOUNT_5F492382 ITEMCOUNT
_WA_Sys_ITEMDATE_5F492382 ITEMDATE
_WA_Sys_LINETOTAL_5F492382 LINETOTAL
_WA_Sys_PRICE_5F492382 PRICE
The computed column is LINETOTAL, which is ITEMCOUNT * PRICE
I'm comparing similar output from sp_helpstats to other databases with this
table, and they either have no stats or just one on GREENSHEETNUMBER. I've
only selected a few dbs so far, but could this be the reason for the
difference?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uqxhDErsDHA.3236@.tk2msftngp13.phx.gbl...
> Can you post the output of sp_helpindex and sp_helpstats for the table
it's
> failing on
>|||Yep, that did the trick. Thanks for your help!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23fB%23V6ssDHA.2520@.TK2MSFTNGP10.phx.gbl...
> I was actually thinking it might be caused by hypothetical indexes created
> by the Index Tuning Wizard confusing the maintenance plan. If you drop the
> _WA_Sys_LINETOTAL_5F492382 LINETOTAL statistics on the computed column,
does
> that have any effect on the ability to reindex the table ?
>|||This is because the statistics for the column will be dropped when you drop
the column but be automatically created when you use the column in a
predicate for a query. You could add a job step to the maintenance plan job
to check for the existence of statistics on computed columns and drop them
before the reindex runs. They will be automatically recreated.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:3FC2525D.45C3BC3D@.dontemailme.com...
i have the same problem. computed column with no index creates this error
for
me too. weird thing is that i can drop the computed column, recreate it and
the
maintenance plan optimization job will work. eventually, the error message
will
start happening again. i have yet to figure out what is causing the error
to
reappear after weeks or months of not appearing.
Microsoft Public wrote:
> I'm getting an error resulting from one of the jobs for my maintenance
plan:
> Rebuilding indexes for table 'MyTable'
> [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, ARITHABORT'.
> Now, I know that there is an issue with indexes on computed columns and
the
> maintenance wizard. This table has a computed column, but there is no
index
> on that column (just a primary key on the table). Plus, there are several
> other databases with the identical table (structure) that do NOT generate
> this error. And the maintenance on this table worked fine until a month
> ago. The structure did not change, but there could have been some records
> inserted and then deleted.
> I'm at a loss as to why this one database throws the error, whereas others
> don't. Any ideas? We are SQL 2K SP3.

Error 1934 during index rebuild

Hi,
I have SQL Server 200 SP3a running on Windows Server
2003. During the optimization setp of my maintenance
plan, I see the following error:
[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, ARITHABORT'.
I saw an article in KB about creating indices on views,
which had a similar error message. The problem that I
have does not create indices on views, though. The table
being re-indexed is very simple, not many records
(thousand or so), and has only one index, which is a
clustered index on two integer values and a date. The
cluster is set to a 90% fill factor.
Other tables are re-indexed just fine in the DB, just
this one seems to be failing. The table does have null
fields, but they are not included in the index. There is
no primary key defined on the table.
Any help is greatly appreciated.
-- Jake
jwa@.beyond-ordinary.com> I have SQL Server 200 SP3a running on Windows Server
> 2003. During the optimization setp of my maintenance
> plan, I see the following error:
> [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, ARITHABORT'.
Is that index set on a computed column? See if it helps:
PRB: SET OPTION Considerations When Running DBCC with Indexes On Computed
Columns
http://support.microsoft.com/default.aspx?scid=KB;en-us;q301292
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 26, 2012

Error 823 with msdb

Error 823 with msdb

Error 823 with msdb

Friday, March 9, 2012

Error 4305 - Log-Shipping

Error 4305 - Log-Shipping

Error 4305 - Log-Shipping

Wednesday, March 7, 2012

ERROR 3628 Floating point error

Sunday, February 26, 2012

Error 3013

Error 3013

Sunday, February 19, 2012

Error 22029 on maint jobs

Wednesday, February 15, 2012

Error 1934 from maintenance plan

Error 1934 during index rebuild

Error 207

Blog Archive

About Me