Increasing size of row/DB?

[plange]

Help... someone else (who is no longer here) configured our old database (MS SQL 2K) to allow people to post larger chunks in ntext columns (as it was getting cut off) and we just ported that database to a new server and now a lot of our ntext data is truncated. I don't know what he did, but I remember him discovering something that we needed to do to the database, and now I cannot figure out what it is. Any help greatly appreciated. Luckily clients haven't noticed the missing content......yet....

 

[druer]

Is the data actually truncated in the database or are you only seeing what appears to be truncated values in the Query Analyzer? There is a SET TEXTLIMIT command that you can lookup in Books On Line. You can try querying for the length of the field instead of the field value to see if it is really truncated in the database or not.

 

[plange]

Not viewing it in the Query Analyzer, but in the output on the content on the website. The code hasn't changed, etc. and I remember this happening before and that we had to make some change to the setting for SQL but for the life of me I can't remember what....

 

[druer]

You probably want to look at the SET TEXTLIMIT command then. I know when I programmed low level SQL API code I had to ensure that it was set for each connection when I created a new connection. If it was set at the server as the default that would probably accomplish the goal.

 

[druer]

Go ahead and try using SQL Query Analyzer and query for the length of the field instead of the field itself and the database will then let you know if the field itself is truncated, or if the TEXTLIMIT on the connection is limiting the size of the data it will transfer and thus it is just appearing to be truncated.

 

[plange]

Thanks so much! You've put me on the right path.... ran the below

SELECT @@TEXTSIZE AS 'Text Size'
SET TEXTSIZE 645120
SELECT @@TEXTSIZE AS 'Text Size'

and the original value was 64512, so I added the 0, but it's still not displaying. I did a DATALENGTH query on one of the ones that is cutting off and it says the length is 65182 which is larger than the original text size, but now that I've set it to 645120 why isn't it displaying?

 

[plange]

Okay, am finding out it's getting truncated on INSERT too, and discovered that the default setting is 65536 (64K) and that to increase it (which you can increase up to 2GB) you have to do this:

sp_configure 'max text repl size', 131072
GO
RECONFIGURE WITH OVERRIDE
GO

which will increase it to 128K

Now my problem is that I'm wondering if this truncation happens during a backup of the DB, as I've reimported the data from the old server and it is still truncated at the same spot.

 

[plange]

Okay, have the truncation at insert fixed, but still have a problem with display. The full data is definitely in the database (I inserted twice what was in there and did the datalength query and its value doubled) but yet, it still only displays part of it despite my increasing the textsize per above. Anyone have any ideas. Losing hair rapidly.

 

[plange]

Okay, in case anyone else has this issue, here is the solution:

Apparently there is an upper limit to this textsize-- I put in 645120 and it took and displayed in QA that it was set at that, but when I changed it to 524288 it worked.

 

[plange]

okay, this is weird..... it keeps resetting itself randomly to 64512

 

[plange]

This is still happening. I don't know it's reset until a client calls complaining their content is cut off.....

 

[SQLBill]

I don't know if this is the issue, but check out the BOL, use the index tab, enter ntext. Then go to the option: Storing Data. Read through the three sub-options. What is your TEXT IN ROW set for? Maybe it needs to be OFF, so that only a pointer is stored.

-SQLBill

Posting advice: FAQ481-4875

 

[plange]

Thanks, I checked that out and did this:

SELECT OBJECTPROPERTY(OBJECT_ID('ET_Pages'),'TableTextInRowLimit')

and it returned a 0 which means textinrow is not turned on right?

 

[plange]

It seems to be resetting every time I restart......

 

[plange]

Does anyone have any idea why this keeps resetting?

 

[SQLBill]

Is this a vendor product? If so, could they have something that is setting it at that value?

We have a vendor supplied database and during one of their updates they felt their database should be in SIMPLE recovery mode. I switched it to FULL, but it kept going back. I finally found that it was the vendor setting it this way and got them to do another update that left it as the DBA set it.

-SQLBill

Posting advice: FAQ481-4875

 

[plange]

Thanks, unfortunately no, it's our own on our own box. It was a new install and there's just something that is not the same as our old DB on our old server because this never happened before.....