| Author |
Topic |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2013-02-13 : 15:58:32
|
| Hi folks,I have a nText field in a DB which is populated with RTF-formatted text. After thinking upon it, no formatting is really required. So, to save space, I figured that I would simply alter the field to Varchar(Max) and translate the populated data from RTF to unformatted text which is lighter. But by doing so, my DB has grown in size by 25%! Can anyone explain why this is and what alternate approach I might use instead ?Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-02-13 : 22:51:37
|
| That's just it, the MDF increased in size! As expected, the log file did too but that's OK. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-14 : 11:34:59
|
| It depends on how you did the change. It's possible that a new table was created, loaded, old table dropped and new table renamed. So, you might have had double the data for that table for a period of time. |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-02-14 : 12:22:17
|
| Lamprey, no, that can't be. Let me explain the procedure that was used. I created a program that does the following:1. It loops through the records, reading the RTF-formatted text, converting it to unformatted text and replacing the content of the record. Although there are less characters to store, database size does not change at this point for reasons that elude me.2. Then the column is converted from nText to Varchar(Max) by doing: Alter Table MyTable Alter Column MyColumn varchar(Max) Null . Upon doing this, the MDF file jumps in size. My understanding is that storage size taken up by nText is supposed to be, in bytes, two times the string length that is entered and that the storage size of a VarChar record is the actual length of the data entered + 2 bytes. So not only should the overall size of the MDF file have dropped because less characters are being stored now, but the conversion from nText to Varchar should have helped reduce size as well.... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-14 : 13:24:44
|
| As for the database not getting smaller after your first operation (RTF to non-RTF), SQL doesn't like to give up what it has acquired. So, you'd have to force it to give up that space (shrinking for example (use with care!!)).You'd have to look at what SQL is doing under the covers. I believe that SQL will, under certain conditions, basically create a new column and copy the contents of the existing column or maybe it'll even create a new table, but I don't think it does (been a while since I've had to be concerned about this). I believe that converting from NTEXT to VARCHAR(MAX) qualifies and explains why you are seeing the datafile growth.Since you did a scheduled data cleanup/purge and it makes sense to re-size the data file(s), then you might want to investigate shrinking the DB. You didn't say how big your data file(s) are so, I'm not sure it would matter. But, if you do decide to run a shrink, make sure you run some form of maintenance to update indexes and statistics afterwards. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-02-14 : 13:27:51
|
| Have you rebuilt the indexes, especially the clustered index - and made sure the LOB compaction option is true?Because you changed from nText to VARCHAR(MAX), the way the data is stored is different and you may also have a lot of allocated/unused space in the table. This may be cleaned up using DBCC CLEANTABLE. |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-02-14 : 15:46:27
|
| The table contains a non-clustered index which had a AVG Fragmentation in % of 23%. I have now rebuilt it using ALTER INDEX aaaaaTransactionLogistics_PK on TransactionLogistics Rebuild and the fragmentation dropped to 2.3% with no impact on DB size on disk. I then did a DBCC CLEANTABLE on the table. The disk spun a lot so I know that some work was done. But still no impact on DB size.Then, although I didn't think it would have any impact after rebuilding the index, I did a ALTER INDEX aaaaaTransactionLogistics_PK on TransactionLogistics reorganize WITH (LOB_COMPACTION = ON). Still no impact on DB size. |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-02-14 : 15:51:21
|
| Oops. Forgot to try the ShrinkDatabase. That did the trick but only after having done the above because I had tried it earlier with no effect. So, this said, I guess the proper sequence of events should be:- do a DBCC CLEANTABLE- do a ShrinkDatabase- rebuild the indicesLet me try this and I will report back in a bit. |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-02-14 : 16:36:20
|
| Confirmed, that works beautifully. Thanks all of you for all your help!!! |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-14 : 18:28:12
|
| Just to add, DBCC ShrinkDatabase is not a good option. Instead, go with ShrinkFile |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-02-15 : 11:44:31
|
| Srimani, Tara, why not use ShrinkDatabase in this particular case? Not only do I want to shrink the data, but I don't need the log. Besides, I'm rebuilding all the indices post shrinkage... |
|
|
|
|
DB taking more space after space saving measure