October 22, 2001 at 6:14 pm
I have a problem with misallocation of tables containing text and ntext fields. The problem occurs when data is deleted from the tables and the database is shrunk. We have disabled auto shrink from all the db's to keep the problem from compounding. The only solution we have found is to dts all the data out of the original table to a temp table, truncating the original table, shrinking the database, and dts'ing the data back. Has anyone seen this and have a better solution.
October 22, 2001 at 7:09 pm
I have not seen this. What version of SQL? What service pack? Setting auto shrink to off is a good idea anyway.
Andy
October 22, 2001 at 8:28 pm
7.0 with anywhere from sp1 to sp3 on the machine. All the sql server installs for 7.0 are showing the problem. Microsoft had told us it was a bug and referred us to use a trace flag on start up to prevent the problem when the database is shrunk, but our testing has shown this does not work. Just looking for any ideas, the bug is fixed in 2000 and they have a new command dbcc cleantable to help deal with these data types.
October 23, 2001 at 5:47 am
Did they give you a Q article on it? The problem only happens when you shrink - why would you need to? They don't have any kind of hotfix available?
Andy
October 23, 2001 at 11:39 pm
No Q article was given. At present, the problem is contained. But, we have approx. 30 + databases with this problem. I work for an e-commerce company that stores quite a bit of XML data. We really need to reclaim space that is showing as used space, but in reality is free. It has been misallocated after the db's performed there auto shrinks. This would occur with the weekly archive and purge procedures that are done. The worst part is we now dynamically build web pages from XML stored in the databases and perform countless updates, inserts and deletes to these databases. We have some that SQL SERVER believes are 12GB in size, but are in reality only approx. 2GB. It is not an easy prospect to take any of these db's offline for any period of time and an extreme pain to repoint all app.'s to duplicate databases while we perform database surgery. Anyway, I was hoping someone else shared my pain and had figured out a work around that does not involve any lenghty downtime.
October 24, 2001 at 5:33 am
Wow. I am SO against XML in blobs! Until the tools progress some, it's just a royal pain to work with (Leon - come on, lets argue!).
How are you accessing your data, sp or dynamic sql from the app? First idea I had was you could set up a "status" table that said if your app should be using the "standard" table or the "tempcopy" table for all the tables involved. Then in each stored procedure you could check that value, then query against the appropriate table. This would let you at least minimize the down time.
Not a great idea - I'll think about it more this morning.
Andy
October 24, 2001 at 9:48 am
The MS site uses XML, but stores this as flat files for much of the data. The flat files are just transformed as the pages are requested. I'd recommend this for 2 reasons.
1. IIS will do a good job of caching this data if you have enough memory. This can be a big speed boost.
2. Reduces load on the db
Cons:
1. Have to rewrite the XML file when something changes.
2. If you have multiple web servers, you have to rewrite or replicate a bunch of stuff. Overall, however, most sites have many more reads than writes (changes), so I'd look at this.
Steve Jones
October 24, 2001 at 2:21 pm
We used to use doubletake to replicate the data out to the 25 webservers, but if you have worked with this software or other replication software you know the pitfalls. It was decided to build dynamically out of the database by the vp's. I felt like the lone voice of sanity. Anyway, I am now stuck with this situation and need to do whatever I can to work this out. However it is not very encouraging to hear that MS does not even use any method like this to do build there own website.
We do have a massive amount of writes throughout the day, since we are an auction site and each bid requires a change to the displayed page. As well as each opening and closing of an auction. Thanks you for the ideas and for this site.
October 24, 2001 at 2:23 pm
Andy,
We use both sp's and dynamic accesses through app.'s.
October 24, 2001 at 4:33 pm
I'm not against blobs always - we use them here on the site for storing articles (but we do cheat and generate semi-dynamic asp pages because we're cautious!). I just disagree with putting structured data in a blob unless you've built the tools to let you work with it. Ah well...an article one of these days where I can rant.
You're right, you're stuck with it for now:-) Is SQL 2000 an option? It is a worthwhile upgrade even without considering this issue.
Other ideas:
-Could you stop purging weekly? That would reduce the frequency anyway.
-Are your blobs over 8k? If not, you could store in a varchar.
-Recreate blobs yourself using varchars. Figure basically you need a table that has an id (the pointer), plus maintains a linked list to additional rows, one for each 8000 varchar. Bye bye blob. Sorta. You then need a way to make it look to your app like its a blob. With SQL2K you could use a function (but that would fix the problem anyway!)...so probably the best you could would be to return the rows and make the app deal with it (shaped recordsets not a bad way).
Are you actually updating the blob each time? This doesnt seem like a good plan. Keeping the data in rows and selecting back as XML seems like a better solution.
Is it with all blobs? Just the n ones? What about image?
Andy
October 24, 2001 at 7:04 pm
All text and ntext, we are not using image. All of the text and ntext fields contain XML.
Yes, all the XML data is over 8k. I know that SQL 2000 is probably my best solution at this point. It was not originally looked at as an option when it was decided to build web pages dynamically from the database. It had just been released and the general thought was let a service pack or 2 come out first and other companies go through the growing pains.
October 24, 2001 at 9:06 pm
We've been running it for a while now - solid. SP2 is in beta now. You won't be sorry you upgraded. The catch is the price - not cheap.
Andy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply