SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Issue on Integrity Check and Optimisation		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/08/2006 :  21:47:52  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
Hi there

I am trying to solve this issue. Basically we had 1 database and we use the SQLMaint (Maintenance Plan) to do the Integrity Check and Optimisation and it's always failed for this particular database.

Looking the SQL Log ... and no error get recorded about this. Then looked at the Event Viewer and this error that I got for Optimisation which is there is nothing to say really.

quote:
SQL Server Scheduled Job 'XXX_SERV_PROD: Maintenance Plan - Optimizations Job' (0x2F273B248B1A0B45A90E4FD9E5BB57DC) - Status: Failed - Invoked on: 2006-10-08 01:00:02 - Message: The job failed. The Job was invoked by Schedule 26 (Schedule 1). The last step to run was step 1 (Step 1).


Any ideas what might be causing it? We are using SP3a and we have about 60 databases for prod and uat and dev and we don't have any issues regarding except this database.

I'm appreciated your comment.

Thanks

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 10/09/2006 :  01:18:35  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
It's really hard to get any idea of the error, unless you post here what you job steps are and table structures of course !

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/09/2006 :  01:31:56  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
Hi Harsh

Basically, the job get created when you created the Maintenance Plan.

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 63D95B92-5F2E-4F6C-A333-9BDB93C35F3E -Rpt "F:\MSSQL\LOG\XXX_SERV_PROD DB MAintenance Plan0.txt" -DelTxtRpt 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 63D95B92-5F2E-4F6C-A333-9BDB93C35F3E -Rpt "F:\MSSQL\LOG\XXX_SERV_PROD DB MAintenance Plan2.txt" -DelTxtRpt 4WEEKS -WriteHistory -CkDB '

Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 10/09/2006 :  01:33:05  Show Profile  Reply with Quote
This is the problem with the Maintenance Plan - you get no idea what the problem is when something goes wrong.

Try using the "Reports to Generate" option and then have a look whether there is anything useful in the text/log file that generates.

Otherwise you need to preform the reorganisation steps manually and see what fails.

Also worth doing a
DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS
just to check there is no damage to the database.

Is the database set to a fixed size (rather than auto grow)? If so that could be preventing REINDEX from completing

Kristen
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/09/2006 :  02:09:13  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
Hi Kristen

Check DBCC CHECDB regularly for this database and return no error.

quote:
CHECKDB found 0 allocation errors and 0 consistency errors in database


The database doesn't set as a fixed size.

I might try do Reports to Generate option.

When you said: "Otherwise you need to preform the reorganisation steps manually and see what fails.", do you mean manually using DBCC CHECKDN stuff? Does anyone know the equivalent SQL Script for Integrity Checks and Optimasation.
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/09/2006 :  02:22:12  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
Hi

Actually I got some clue from the Maintenance Plan - History of Integrity Check.

[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

Does anyone know how to solve this?

Thanks
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 10/09/2006 :  03:56:45  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by valdyv

Hi

Actually I got some clue from the Maintenance Plan - History of Integrity Check.

[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

Does anyone know how to solve this?

Thanks


Check the current setting of SET QUOTED_IDENTIFIER option by using DBCC USEROPTIONS and make changes accordingly.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/09/2006 :  19:03:23  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
quote:
Check the current setting of SET QUOTED_IDENTIFIER option by using DBCC USEROPTIONS and make changes accordingly.


I did run the DBCC USEROPTIONS and the value sets as SET which I belive is ON.

My question is: Is the SET QUOTED_IDENTIFIER isolated only for the database level or it's actually entire server configuration? If it's a server configuration which will be affecting entire database ... I rather not going to apply this.

Please advice.

Thanks
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/09/2006 :  21:03:12  Show Profile  Reply with Quote
Dp you have a table with a computed column in the database where you have this problem?

CODO ERGO SUM
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/10/2006 :  00:07:04  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
quote:
Do you have a table with a computed column in the database where you have this problem?


No I don't have that table with a computed column. There are hunders table in this database and it's going to be pain to search this.

So my question is: Is the SET QUOTED_IDENTIFIER isolated only for the database level or it's actually entire server configuration? If it's a server configuration which will be affecting entire database ... I rather not going to apply this.
Go to Top of Page

vdavid70
Yak Posting Veteran

67 Posts
Posted - 10/10/2006 :  08:01:32  Show Profile  Reply with Quote
I think it is specific to the database rather than to the server.

What i have done in the past is to go into the maintainance plan in question and write a set option for the quoted_identifier and then place a go statement just between the set lption and the maintainance plan script
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/10/2006 :  10:08:24  Show Profile  Reply with Quote
quote:
Originally posted by valdyv

quote:
Do you have a table with a computed column in the database where you have this problem?


No I don't have that table with a computed column. There are hunders table in this database and it's going to be pain to search this...


Your answer does not make any sense.

First you say you don't have any a computed columns. Then you say it's too much work to check.






CODO ERGO SUM
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts
Posted - 10/10/2006 :  10:54:41  Show Profile  Reply with Quote
you have an index on a computed column.

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



-ec
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/10/2006 :  12:10:20  Show Profile  Reply with Quote
quote:
Originally posted by eyechart

you have an index on a computed column.

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



-ec


I knew there was a problem with computed columns.

I didn't realize there was a fix with SP4: -SupportComputedColumn



CODO ERGO SUM
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/10/2006 :  18:56:46  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
quote:
Your answer does not make any sense. First you say you don't have any a computed columns. Then you say it's too much work to check.


No what I mean I don't know any table having a computed column. Anyway ...
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/12/2006 :  20:11:28  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
quote:
What i have done in the past is to go into the maintainance plan in question and write a set option for the quoted_identifier and then place a go statement just between the set lption and the maintainance plan script


I did try this by doing this:

quote:
SET QUOTED_IDENTIFIER OFF
GO

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 02D8A981-13DF-471D-8513-45A0C27BBF23 -Rpt "D:\Program Files\Microsoft SQL Server\MSSQL\LOG\XXX_SERV_UAT DB Maintenance Plan2.txt" -DelTxtRpt 4WEEKS -WriteHistory -CkDB '
GO


and it's the same error. What really bothering me is if I run DBCC CHECKDB('XXX_SERV_UAT') and no error what so ever ?!?!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/12/2006 :  20:19:31  Show Profile  Reply with Quote
So what did you think of the information in that Knowledge Base article that eyechart posted a link to?

Find anything there that might be relevant to your situation?






CODO ERGO SUM
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/12/2006 :  20:54:48  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
quote:
So what did you think of the information in that Knowledge Base article that eyechart posted a link to?

Find anything there that might be relevant to your situation?


The KB mentioned that this was fixed in SP4 and the issus is that our standard environment is SP3a.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts
Posted - 10/12/2006 :  21:44:10  Show Profile  Reply with Quote
quote:
Originally posted by valdyv

The KB mentioned that this was fixed in SP4 and the issus is that our standard environment is SP3a.



it is fixed if you use a specific switch.


-ec
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/12/2006 :  22:18:24  Show Profile  Reply with Quote
quote:
Originally posted by valdyv

quote:
So what did you think of the information in that Knowledge Base article that eyechart posted a link to?

Find anything there that might be relevant to your situation?


The KB mentioned that this was fixed in SP4 and the issus is that our standard environment is SP3a.

Then do what you need with a script or a stored procedure.

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








CODO ERGO SUM
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts
Posted - 10/13/2006 :  00:23:08  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
After looking at this, I decide NOT to use Maintenance Plan for Integrity Check and Optimisation and used the script instead.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000