SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 DB taking more space after space saving measure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WindChaser
Posting Yak Master

213 Posts

Posted - 02/13/2013 :  15:58:32  Show Profile  Reply with Quote
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

USA
35932 Posts

Posted - 02/13/2013 :  16:22:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
Was it just the transaction log or was it the data file(s)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

WindChaser
Posting Yak Master

213 Posts

Posted - 02/13/2013 :  22:51:37  Show Profile  Reply with Quote
That's just it, the MDF increased in size! As expected, the log file did too but that's OK.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 02/14/2013 :  11:34:59  Show Profile  Reply with Quote
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.
Go to Top of Page

WindChaser
Posting Yak Master

213 Posts

Posted - 02/14/2013 :  12:22:17  Show Profile  Reply with Quote
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....

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35932 Posts

Posted - 02/14/2013 :  13:21:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have you rebuilt the associated indexes? Maybe it's just fragmentation?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 02/14/2013 :  13:24:44  Show Profile  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
768 Posts

Posted - 02/14/2013 :  13:27:51  Show Profile  Reply with Quote
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.

Go to Top of Page

WindChaser
Posting Yak Master

213 Posts

Posted - 02/14/2013 :  15:46:27  Show Profile  Reply with Quote
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.
Go to Top of Page

WindChaser
Posting Yak Master

213 Posts

Posted - 02/14/2013 :  15:51:21  Show Profile  Reply with Quote
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 indices

Let me try this and I will report back in a bit.
Go to Top of Page

WindChaser
Posting Yak Master

213 Posts

Posted - 02/14/2013 :  16:36:20  Show Profile  Reply with Quote
Confirmed, that works beautifully. Thanks all of you for all your help!!!
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/14/2013 :  18:28:12  Show Profile  Reply with Quote
Just to add, DBCC ShrinkDatabase is not a good option. Instead, go with ShrinkFile
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35932 Posts

Posted - 02/14/2013 :  20:06:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Agreed, always use DBCC SHRINKFILE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

WindChaser
Posting Yak Master

213 Posts

Posted - 02/15/2013 :  11:44:31  Show Profile  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000