Maybe Gail has a better crystal ball that I do, but without seeing the columns, what type of data you keep in there and how the table is used it's...

david.holley (5/11/2011)

---

The habit of using column (field) names is very ingrained into me. Using * honestly feels bad at this point. I was just curious not being entirely familary with...

kr.nithin (5/11/2011)

---

No. Auto shrink is off & no shrinks applied as of now.

There is a correction: I had mentioned 18 tables. It should be 18 Indexes. and the Indexes...

Please run this

DBCC CHECKDB('db-name') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS

We can't provide a plan for the solution before we have all the errors.

Index definition including the datatypes please.

Is this table really heavy on insert / deletes?

IIRC this is why xml was invented...

You've said on another thread that you can't use nvarchar(max). Why?

Assuming this is true then you are basically screwed at building the code...

The real tuning is to NOT returned columns you don't need.

Select * or select 100 column names won't change much on sql server. If there's a difference it's...

Any reason why you're not using select *?

P.S. Yes I know this is not best practice.

Please post the whole code.

Do they have more than 1000 pages of data?

What's the leading column of the index? If it's a guid then yes it's normal.

It's also possible if this is some...

Then use nvarchar(max). That works.

ilker.cikrikcili (5/11/2011)

---

sql server forces me to use nvarchar of which max size is 4000:

I have the same problem, I need a 8000 long data...

Ken Davis (5/11/2011)

---

Thanks for the ideas. BTW, the job failed again today 6:35am.

Yes, that message was from the job history. Another part of this mystery is...

You're not giving any usefull info to help you.

unclosed quotation mark means you have something like this in your code :

where whatever = 'ok'wrong'

it should be

where whatever = 'ok''wrong'

If...

Arithabort maybe? Total guess.