Issue on Integrity Check and Optimisation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Issue on Integrity Check and Optimisation

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-10-08 : 21:47:52

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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 01:18:35

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"

dewacorp.alliances

452 Posts

Posted - 2006-10-09 : 01:31:56

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

Kristen
Test

22859 Posts

Posted - 2006-10-09 : 01:33:05

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

dewacorp.alliances

452 Posts

Posted - 2006-10-09 : 02:09:13

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.

dewacorp.alliances

452 Posts

Posted - 2006-10-09 : 02:22:12

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 03:56:45

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"

dewacorp.alliances

452 Posts

Posted - 2006-10-09 : 19:03:23

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-09 : 21:03:12

Dp you have a table with a computed column in the database where you have this problem?

CODO ERGO SUM

dewacorp.alliances

452 Posts

Posted - 2006-10-10 : 00:07:04

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.

vdavid70
Yak Posting Veteran

67 Posts

Posted - 2006-10-10 : 08:01:32

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-10 : 10:08:24

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

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-10 : 10:54:41

you have an index on a computed column.

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

-ec

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-10 : 12:10:20

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

dewacorp.alliances

452 Posts

Posted - 2006-10-10 : 18:56:46

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 ...

dewacorp.alliances

452 Posts

Posted - 2006-10-12 : 20:11:28

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 ?!?!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-12 : 20:19:31

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

dewacorp.alliances

452 Posts

Posted - 2006-10-12 : 20:54:48

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.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-12 : 21:44:10

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-12 : 22:18:24

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

dewacorp.alliances

452 Posts

Posted - 2006-10-13 : 00:23:08

After looking at this, I decide NOT to use Maintenance Plan for Integrity Check and Optimisation and used the script instead.

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources