Sql Error message: plan

Showing posts with label plan. Show all posts

Tuesday, March 27, 2012

error rebuild index with dbcc

I have an error like this, every time I reindex this table
via db maintenance plan.
Rebuilding indexes for table 'tbJOTrStatusbyWC'
[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'.
What does it means this error ?
How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
thx.You most probably have an index on a computed columns or an indexed view. And unfortunately, the
maint plan doesn't execute the needed SET commands to be able to rebuild these indexes. You would
have to create your own (TSQL) job where you execute needed SET commands and then DBCC DBREINDEX (or
DBCC INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> I have an error like this, every time I reindex this table
> via db maintenance plan.
> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> [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'.
> What does it means this error ?
> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> thx.|||Tibor is correct in that the maintenance wizard has issues with things such
as computed columns and indexed views. I suggest you create your own
scheduled job to do the DBREINDEX and not use the wizard and all should be
fine.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$PV9Y2eDHA.3788@.tk2msftngp13.phx.gbl...
> > You are wrong,
> I'm pretty certain that I'm not. But you are welcome to think so, if you
wish.
> I still recommend that you check very closely if you have either indexes
on computed columns or
> indexes on views.
> You do not need any special SET options if you have an PK over several
columns, so it has to be
> something else.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> news:3d9d01c37b5e$e471ee80$a601280a@.phx.gbl...
> > You are wrong,
> >
> > I don't use a computed columns, I use multi colums(JONo,
> > OPID,seqNo) for my primay key, this is the structure.
> >
> > TrackMainID int 4 1
> > JONo int 4 0
> > OPID int 4 0
> > SeqNo int 4 0
> > QtyIN numeric 5 1
> > QtyOut numeric 5 1
> > QtyOpen numeric 5 1
> > WCID int 4 1
> > FirstInDate datetime 8 1
> > LastOutDate datetime 8 1
> >
> > I still don't understand the message : incorrect
> > settings: 'QUOTED_IDENTIFIER, ARITHABORT'
> >
> > I must setting where ?
> >
> >
> >
> > >--Original Message--
> > >You most probably have an index on a computed columns or
> > an indexed view. And unfortunately, the
> > >maint plan doesn't execute the needed SET commands to be
> > able to rebuild these indexes. You would
> > >have to create your own (TSQL) job where you execute
> > needed SET commands and then DBCC DBREINDEX (or
> > >DBCC INDEXDEFRAG).
> > >
> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> > message
> > >news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> > >> I have an error like this, every time I reindex this
> > table
> > >> via db maintenance plan.
> > >>
> > >> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> > >> [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'.
> > >>
> > >> What does it means this error ?
> > >> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> > >>
> > >> thx.
> > >
> > >
> > >.
> > >
>sql

Friday, March 9, 2012

error mssqlserver

Hi all:
The errors always during the maintenance plan, and the last error we have is:
17066; SQL Server Assertion File:<page.cpp>, line=2831
Failed Assertion='spceContig>=Align (spaceNedded)'
What means?
Thank you.
Jose
Did you search KB and Google? Using below search words, I got a number of hits on Google:
SQL 17066
Often Assertion problems are bugs in LSQ server, but then can also be corruption problems in your
database. Run DBCC CHECKDB and also search KB etc and make sure you are current on service pack. If
that doesn't help, consider opening a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:54AF2F6A-BA57-4309-BE62-C92BD56C4ABD@.microsoft.com...
> Hi all:
> The errors always during the maintenance plan, and the last error we have is:
> 17066; SQL Server Assertion File:<page.cpp>, line=2831
> Failed Assertion='spceContig>=Align (spaceNedded)'
> What means?
> Thank you.
> Jose

error mssqlserver

Hi all:
The errors always during the maintenance plan, and the last error we have is
:
17066; SQL Server Assertion File:<page.cpp>, line=2831
Failed Assertion='spceContig>=Align (spaceNedded)'
What means'
Thank you.
JoseDid you search KB and Google? Using below search words, I got a number of hi
ts on Google:
SQL 17066
Often Assertion problems are bugs in LSQ server, but then can also be corrup
tion problems in your
database. Run DBCC CHECKDB and also search KB etc and make sure you are curr
ent on service pack. If
that doesn't help, consider opening a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:54AF2F6A-BA57-4309-BE62-C92BD56C4ABD@.microsoft.com...
> Hi all:
> The errors always during the maintenance plan, and the last error we have
is:
> 17066; SQL Server Assertion File:<page.cpp>, line=2831
> Failed Assertion='spceContig>=Align (spaceNedded)'
> What means'
> Thank you.
> Jose

error mssqlserver

Hi all:
The errors always during the maintenance plan, and the last error we have is:
17066; SQL Server Assertion File:<page.cpp>, line=2831
Failed Assertion='spceContig>=Align (spaceNedded)'
What means'
Thank you.
JoseDid you search KB and Google? Using below search words, I got a number of hits on Google:
SQL 17066
Often Assertion problems are bugs in LSQ server, but then can also be corruption problems in your
database. Run DBCC CHECKDB and also search KB etc and make sure you are current on service pack. If
that doesn't help, consider opening a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:54AF2F6A-BA57-4309-BE62-C92BD56C4ABD@.microsoft.com...
> Hi all:
> The errors always during the maintenance plan, and the last error we have is:
> 17066; SQL Server Assertion File:<page.cpp>, line=2831
> Failed Assertion='spceContig>=Align (spaceNedded)'
> What means'
> Thank you.
> Jose

Error msg 8616

Hello,
I got this massage,
'Warning: The query processor could not produce a query plan from the
optimizer because the total length of all the columns in the GROUP BY or
ORDER BY clause exceeds 8000 bytes'
How I can do it?
Thanks,
JNHi, JN
I believe the full text of the error message is:
Server: Msg 8619, Level 16, State 2, Line <n>
Warning: The query processor could not produce a query plan from
the optimizer because the total length of all the columns in the
GROUP BY or ORDER BY clause exceeds 8000 bytes.
Resubmit your query without the ROBUST PLAN hint.
However, if you try your query without "OPTION (ROBUST PLAN)" you may
get the following error:
Server: Msg 1540, Level 16, State 1, Line <n>
Cannot sort a row of size <x>, which is greater than the allowable
maximum of 8094.
Am I correct so far ? If yes, you should redesign your query so that
any necessary sorting will be done on columns with a total length less
than 8000 bytes. We can help you with this task only if you post DDL,
sample data, expected results and your current query. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Razvan

Sunday, February 26, 2012

Error message when trying to save modified maintenance plan?

I have a maintenance plan created that have been running for a few months.
Now I want to modify the plan and selected Modify on the plan and got into
an editor with the different steps. I selected "Edit" on the Backup step and
changed the name of the destination folder and clicked OK in that dialog.
When I try to save the modified maintenance plan, I only get an error dialog
with "No description found".
What is the problem?
OlavHi
I am not sure about the error, you may want to use SQL Profiler to find the
statement causing the error and try to run this manually. The quickest
solution may be to drop the maintenance plan and re-create it from scratch.
John
"Olav" wrote:
> I have a maintenance plan created that have been running for a few months.
> Now I want to modify the plan and selected Modify on the plan and got into
> an editor with the different steps. I selected "Edit" on the Backup step and
> changed the name of the destination folder and clicked OK in that dialog.
> When I try to save the modified maintenance plan, I only get an error dialog
> with "No description found".
> What is the problem?
> Olav
>
>|||I have the same problem when I try to modify a maintenance plan and then save
the changes I get an error message saying "No description found" - this even
happens when I try to rename the maintenance plan. The changes are not saved
and the maintenance plan executes ok.
I can create new plans using the wizard but not from scratch. I have
upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
Recreating maintenance plans each time I want to modify them is not an option.
Does any one have a solution ?
"John Bell" wrote:
> Hi
> I am not sure about the error, you may want to use SQL Profiler to find the
> statement causing the error and try to run this manually. The quickest
> solution may be to drop the maintenance plan and re-create it from scratch.
> John
> "Olav" wrote:
> > I have a maintenance plan created that have been running for a few months.
> >
> > Now I want to modify the plan and selected Modify on the plan and got into
> > an editor with the different steps. I selected "Edit" on the Backup step and
> > changed the name of the destination folder and clicked OK in that dialog.
> >
> > When I try to save the modified maintenance plan, I only get an error dialog
> > with "No description found".
> >
> > What is the problem?
> >
> > Olav
> >
> >
> >|||Hi
Are you seeing this error? http://tinyurl.com/mj88y
John
"Bridgepark" wrote:
> I have the same problem when I try to modify a maintenance plan and then save
> the changes I get an error message saying "No description found" - this even
> happens when I try to rename the maintenance plan. The changes are not saved
> and the maintenance plan executes ok.
> I can create new plans using the wizard but not from scratch. I have
> upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> Recreating maintenance plans each time I want to modify them is not an option.
> Does any one have a solution ?
> "John Bell" wrote:
> > Hi
> >
> > I am not sure about the error, you may want to use SQL Profiler to find the
> > statement causing the error and try to run this manually. The quickest
> > solution may be to drop the maintenance plan and re-create it from scratch.
> >
> > John
> >
> > "Olav" wrote:
> >
> > > I have a maintenance plan created that have been running for a few months.
> > >
> > > Now I want to modify the plan and selected Modify on the plan and got into
> > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > changed the name of the destination folder and clicked OK in that dialog.
> > >
> > > When I try to save the modified maintenance plan, I only get an error dialog
> > > with "No description found".
> > >
> > > What is the problem?
> > >
> > > Olav
> > >
> > >
> > >|||I see the "No description found" message but I have plans without Cleanup
tasks in them. I tried creating a new plan without using the wizard and when
I try and save it I get the error. If I create a plan using the Wizard its
fine (even if it has a clean up task) However if I then try and alter the
plan I get the message.
"John Bell" wrote:
> Hi
> Are you seeing this error? http://tinyurl.com/mj88y
> John
> "Bridgepark" wrote:
> > I have the same problem when I try to modify a maintenance plan and then save
> > the changes I get an error message saying "No description found" - this even
> > happens when I try to rename the maintenance plan. The changes are not saved
> > and the maintenance plan executes ok.
> > I can create new plans using the wizard but not from scratch. I have
> > upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> > Recreating maintenance plans each time I want to modify them is not an option.
> >
> > Does any one have a solution ?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not sure about the error, you may want to use SQL Profiler to find the
> > > statement causing the error and try to run this manually. The quickest
> > > solution may be to drop the maintenance plan and re-create it from scratch.
> > >
> > > John
> > >
> > > "Olav" wrote:
> > >
> > > > I have a maintenance plan created that have been running for a few months.
> > > >
> > > > Now I want to modify the plan and selected Modify on the plan and got into
> > > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > > changed the name of the destination folder and clicked OK in that dialog.
> > > >
> > > > When I try to save the modified maintenance plan, I only get an error dialog
> > > > with "No description found".
> > > >
> > > > What is the problem?
> > > >
> > > > Olav
> > > >
> > > >
> > > >|||Hi
You may have to log a call with PSS.
John
"Bridgepark" wrote:
> I see the "No description found" message but I have plans without Cleanup
> tasks in them. I tried creating a new plan without using the wizard and when
> I try and save it I get the error. If I create a plan using the Wizard its
> fine (even if it has a clean up task) However if I then try and alter the
> plan I get the message.
> "John Bell" wrote:
> > Hi
> >
> > Are you seeing this error? http://tinyurl.com/mj88y
> >
> > John
> >
> > "Bridgepark" wrote:
> >
> > > I have the same problem when I try to modify a maintenance plan and then save
> > > the changes I get an error message saying "No description found" - this even
> > > happens when I try to rename the maintenance plan. The changes are not saved
> > > and the maintenance plan executes ok.
> > > I can create new plans using the wizard but not from scratch. I have
> > > upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> > > Recreating maintenance plans each time I want to modify them is not an option.
> > >
> > > Does any one have a solution ?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I am not sure about the error, you may want to use SQL Profiler to find the
> > > > statement causing the error and try to run this manually. The quickest
> > > > solution may be to drop the maintenance plan and re-create it from scratch.
> > > >
> > > > John
> > > >
> > > > "Olav" wrote:
> > > >
> > > > > I have a maintenance plan created that have been running for a few months.
> > > > >
> > > > > Now I want to modify the plan and selected Modify on the plan and got into
> > > > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > > > changed the name of the destination folder and clicked OK in that dialog.
> > > > >
> > > > > When I try to save the modified maintenance plan, I only get an error dialog
> > > > > with "No description found".
> > > > >
> > > > > What is the problem?
> > > > >
> > > > > Olav
> > > > >
> > > > >
> > > > >

Error message when trying to do a maintenance plan

Hi all,

As far as I can tell I have installed all features of SQL2005 but when I try to setup a maintenance plan I am receiving this message:

TITLE: Microsoft SQL Server Management Studio

The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server component that is not installed on the remote computer. To proceed, install SQL Server 2005 Management Tools on the remote computer, and then try again. For more information, see "How to: Install SQL Server 2005 (Setup)" in SQL Server 2005 Books Online, or find the article on MSDN at http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)

For help, click: http://go.microsoft.com/fwlink/?LinkID=57083

BUTTONS:

Any ideas on why this is happening?

first of all you must apply sp2 of Mar

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19624

There are many problems related to MP solved in this version. Check whether you have installed Integration services and the services is running

Madhu

|||

First things first, have you applied any service pack after you have installed the SQL 2005 components on thsi machine?

If not try as suggested to take care of such issues with maintenance plans.

Friday, February 24, 2012

Error Message on optimization plan in sqlserver 2000

I am not a SQL Server DBA but trying to figure this out,
The 'OptimizationsJob For Db Maintenance Plan' is failing with an
error -- When I checked the error message the index rebuild is failing
for one of the tables with an error of
[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'.
This table "t" exists in both our Dev and Prod Servers but I see this
issue only in the Prod server (the prod server has more data)...
I ran this query:
select * from sysobjects where type = 'V'
and objectproperty(id, 'IsIndexed') = 1
for any indexed views and returns null
What are my options?
TIA,
MagWhat about computed columns. If memory serves me, it is enough to have statistics on a computed
column for this error to happen.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message news:1175025577.736504.80620@.b75g2000hsg.googlegroups.com...
>I am not a SQL Server DBA but trying to figure this out,
> The 'OptimizationsJob For Db Maintenance Plan' is failing with an
> error -- When I checked the error message the index rebuild is failing
> for one of the tables with an error of
> [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'.
>
> This table "t" exists in both our Dev and Prod Servers but I see this
> issue only in the Prod server (the prod server has more data)...
> I ran this query:
> select * from sysobjects where type = 'V'
> and objectproperty(id, 'IsIndexed') = 1
> for any indexed views and returns null
>
> What are my options?
>
> TIA,
> Mag
>|||On Mar 27, 3:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What about computed columns. If memory serves me, it is enough to have statistics on a computed
> column for this error to happen.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>
> <aspirin...@.gmail.com> wrote in messagenews:1175025577.736504.80620@.b75g2000hsg.googlegroups.com...
> >I am not a SQL Server DBA but trying to figure this out,
> > The 'OptimizationsJob For Db Maintenance Plan' is failing with an
> > error -- When I checked the error message the index rebuild is failing
> > for one of the tables with an error of
> > [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'.
> > This table "t" exists in both our Dev and Prod Servers but I see this
> > issue only in the Prod server (the prod server has more data)...
> > I ran this query:
> > select * from sysobjects where type = 'V'
> > and objectproperty(id, 'IsIndexed') = 1
> > for any indexed views and returns null
> > What are my options?
> > TIA,
> > Mag- Hide quoted text -
> - Show quoted text -
There is no computed columns on this table and how come the DEV/Test
instance is not showing the same exception...|||> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
That could have been because auto-statistics could have been created on the dev server but not the
prod server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message
news:1175029664.750939.316180@.p77g2000hsh.googlegroups.com...
> On Mar 27, 3:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> What about computed columns. If memory serves me, it is enough to have statistics on a computed
>> column for this error to happen.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>>
>> <aspirin...@.gmail.com> wrote in
>> messagenews:1175025577.736504.80620@.b75g2000hsg.googlegroups.com...
>> >I am not a SQL Server DBA but trying to figure this out,
>> > The 'OptimizationsJob For Db Maintenance Plan' is failing with an
>> > error -- When I checked the error message the index rebuild is failing
>> > for one of the tables with an error of
>> > [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'.
>> > This table "t" exists in both our Dev and Prod Servers but I see this
>> > issue only in the Prod server (the prod server has more data)...
>> > I ran this query:
>> > select * from sysobjects where type = 'V'
>> > and objectproperty(id, 'IsIndexed') = 1
>> > for any indexed views and returns null
>> > What are my options?
>> > TIA,
>> > Mag- Hide quoted text -
>> - Show quoted text -
> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
>

Error Message on optimization plan in sqlserver 2000

Wednesday, February 15, 2012

Error Message

I have a Maintenance Plan set to run every night that Optimizes the databases, runs an Integrity check and then backs them up. Each job is staggered to run 1 hour apart. It has been running fine for months and now I get the following intermittent error during the "Reorganize data and index pages" part:
[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 know what database it is failing on but I run dbcc checkdb and find no errors. From what I have found on the web this error pertains to an index problem. The only indices on this database reside in the system tables. I have dropped the database and restored it but no help there. Any ideas?

Thanks
DonDo you have indexes on views or computed columns ? If so,
you will need to run dbcc dbreindex or indexdefrag. This occurs because sql server agent does not (by default) set arithabort and quoted_identifier on. So, you will need to create a job that sets these:

set arithabort on
set quoted_identifier on
dbcc checktable(tablename)|||I don't have any indexes on views or any user tables, just the default system tables with a fresh database. I'll look into the arithabort setting.
Thanks
Don|||Have you tried the dbcc commands ?

Tuesday, March 27, 2012

error rebuild index with dbcc

Friday, March 9, 2012

error mssqlserver

error mssqlserver

error mssqlserver

Error msg 8616

Sunday, February 26, 2012

Error message when trying to save modified maintenance plan?

Error message when trying to save modified maintenance plan?

Error message when trying to do a maintenance plan

Friday, February 24, 2012

Error Message on optimization plan in sqlserver 2000

Error Message on optimization plan in sqlserver 2000

Error Message on optimization plan in sqlserver 2000

Wednesday, February 15, 2012

Error Message

Sql Error message

Blog Archive

About Me