January 7, 2007 at 1:18 am
Hi
I have a table which contains 85GB of data. The table has a text column which is the main reason for such a huge size as the column actually contains XML. Now I have to move this table to another database. But I want to do the below steps while transferring the data. Could any one please help on the below points?
1. I want to copy this table from source to Destination without writing any log. So will I for BCP? If so can I embed this BCP is a DTS?
2. How can I compress the data of this table [At least the text column] while transferring the data? Does SQL 2000 supports any way to compress data in table/column level? Also is there any free tool for the same?
Thanks in advance for your help.
Regards
Utsab Chattopadhyay
January 7, 2007 at 11:00 pm
I don't think you can compress text data while copying using sql server native functions/tools...
Defintely there are third party tools out there but I don't remember at this time...
MohammedU
Microsoft SQL Server MVP
January 8, 2007 at 9:49 am
you can't do what you're asking as such. Turning the destination database to simple recovery will decrease the effect of logging in the transfer. You could bcp out, zip, transfer, unzip, bcp in .. I figure you could write this in dts but to be honest a straight bcp might be easier or come to that a straight dts transfer. Sometimes when you take out the time you spent trying to be clever you could have done the job already. 85Gb should transfer quite quickly unless you're using a slow connection.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2007 at 9:52 am
You can probably get hold of dll to use as an extended proc to compress the data into a blob if you wanted.
January 8, 2007 at 10:03 am
I've never transferred a table quite that large - 85 Gb. My largest to date was about 10 Gb. I whole heartedly advise some testing, especially since text column(s) are involved. bcp is definitely the way to go in my opinion. You can create multiple bcp out files. This way you can execute multiple bcp in processes concurrently - I'd go with one concurrent process per CPU on your SQL Server. Stagger the execution start times by 5 minutes or so. This will enable you to keep a continuous process stream into your table. You should also use the batchsize option on each bcp in and set it to something like 10,000 - this will help with the transaction log size. Also be wary of the text column(s). You will probably have to experiment to find out what column delimiter and row terminator work properly because the defaults of tab and newline in bcp will most proably be found in your text column(s). I've used the vertial bar ( | ) as a column delimiter and the tilde ( ~ ) as the row terminator with reasonable success in the past. Also, bcp in works faster with local files rather than network attached files.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 8, 2007 at 12:16 pm
neat idea;
does anyone have a compression algorythm / stored proc to do this to text? i could see how it could be nice to stuff some compressed, non-binary xml into a varchar field, and decompress when needed; a zip code/address database for example.
Lowell
January 8, 2007 at 1:16 pm
I agree with Rudy, batched bcp. I have moved 100Gb tables about and I always take the batch approach. You could probably have done it by now < grin >
I can't see why you'd really want to use on the fly compression - I'd be positive you could write such a routine, however in c# in sql 2005 in the CLR - that would be the only way I'd consider taking on that sort of task - sql2000 no way.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2007 at 1:24 pm
Hi All
Thanks all for your analysis and sorry for the slow response. I am planning to do it tomorrow and trying to figure out a final strategy from all the above analysis. Actually I am still wondering with the compression technique as I am yet to figure out a good tool / xp which can help me to compress the table. The reason I need to compress the table to my new database is the crunch of space as the server can not accept a new HDD now. L
Any idea how to find a tool/xp which will compress text? Thanks in advance.
Regards
Utsab Chattopadhyay
January 8, 2007 at 1:47 pm
You can configure the disk to compress...MS will not support but it works...
SQL Server databases not supported on compressed volumes
http://support.microsoft.com/default.aspx/kb/231347
MohammedU
Microsoft SQL Server MVP
January 9, 2007 at 3:45 pm
The reason you will struggle to find such a tool is the limitations that compressed text would place on your logical operations, you would only be able to search by uncompressing it, effectively you would just be storing binary data. You could certainly store a zip file in sql server I figure, but the issues of extracting and compressing and writing say a couple of million rows, especially as I figure you'd probably have to use row by row ops - uhh!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2007 at 4:56 pm
Hi
To battle this issue.. I have decided to move the problematic table to SQL 2005 and then using SQLCompress.net for a column level compression. [www.sqlcompress.net].
In testing with sample data.. surprisingly after the tool is deployed.. it is using more space than actually compressing. The site forum is possibly not working. [Tried only once.]
Any one has experience on this tool for achieving column level compression? In that case please let me know so that I can ask further help to understand the issue.
Thanks for your helps....
Regards
Utsab Chattopadhyay
January 18, 2007 at 12:24 pm
Utsab:
1 - Yes, your best choice is BCP, make sure to set the database to BULK-LOGGED recovery model, also check BOL for non-logged BCP operations.
2 - I check their forums and they suggested to run DBCC CLEANTABLE and "...after DBCC CLEANTABLE you should run an ALTER INDEX ALL ON <compressed schema>.<compressed table> REORGANIZE WITH (LOB_COMPACTION = ON) this should help compact those pages containing LOB data... these are new features in SQL 2005."
You can use the article on compression I posted here http://www.sqlservercentral.com/columnists/ymartinez/2798.asp and see if you get good compression... sp_spaceused doesn't always show the right information (make sure you run DBCC UPDATEUSAGE before running it) and there are many other factors involved in space utilization that you should be aware of (fill factor, fracmentation, etc.).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply