October 7, 2009 at 7:49 pm
I want to copy tables from one DB to another on the same server. Source Tables are non-unicode, target tables are unicode. I created the target tables with just the clustered index defined. I then ran the following SQL script
Insert into TargetTable
select * from SourceTable
The script ran in 2 hours, created a 35 GB table and the DB log was 38 GB. I then was reading about the bulk-logged recovery model and how it would allow minimal logging in certain situations (empty target table and tablock optimizer hint set). I then created a DB with the bulk-logged recovery model and tried the following script:
Insert into TargetTable WITH (TABLOCK)
select * from SourceTable (nolock) --figured nolock might speed things up a bit--
This actually ran in 2 1/2 hours and created a 35 GB table with a 39 GB log. Does anyone know the proper way to get minimal logging using the bluk-logged recovery type? ... or a better method of transferring the data from table to table? Thanks
October 7, 2009 at 11:39 pm
If you are using SQL Server 2005 then you can try SSIS with Data Flow Task. Define Set Rows Per Batch & Maximum Insert Commit Size under OLE DB Destination to some finitie value e.g. 10000,100000.
October 8, 2009 at 8:30 am
Well, you are doing the entire copy in a single transaction which is why the log file is growing so much. If you batch it with TX Log backups between batches or you go with SIMPLE recovery and CheckPoints your log shouldn't grow so much.
Do you have indexes on the table already? If so you may want to drop the indexes and then re-create
them AFTER the load.
How big is the source table? Remember going from non-unicode to unicode will double the storage required for string data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2009 at 10:49 am
Thanks Jack,
I understand that I can break the inserts into batches and then I could create smaller TXN logs. But, it will still mean ~40 GB of data will have been written to the TXN logs (let's say 8 X 5 GB TXN logs). My hope is to reduce the amount of logging that occurs. My question was regarding the bulk-logged recovery model and reading something that stated that one could reduce logging on inserts to an empty table by using the bluk-logged recovery model. When I turned on the bulk-logged recovery model and reran my insert (see above) I still ended up with the same size TXN log, so either I did something wrong in the insert statement or the bulk-logged recovery model doesn't reduce the amount of logging that occurs for my particular insert.
I am inserting to an empty table that only has a Clustered Index defined. The table is ~35 GB, log ended up being ~39 GB.
I'm starting to think that the perhaps the bulk-logged recovery model option fully logs data pages and minimally logs index pages. If that is the case then the bulk-logged recovery model doesn't help me in this situation because I only have data pages. I thought SQL Server might skip logging altogether and just truncate the table if there was a failure. In other words, if SQL Server knows it started with nothing in the table and the insert fails it can just restore the table to empty rather than rolling back individual inserts in the TXN log.
October 8, 2009 at 12:14 pm
Logging is the way that SQL Server guarantees consistency. By reducing batch size and backing up the transaction log you will not/should not experience log growth which can be an expensive operation.
The bulk logged recovery model has no effect on an insert into statement. See http://msdn.microsoft.com/en-us/library/aa173529(SQL.80).aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2009 at 12:22 pm
Can I ask a stupid question...
What is the problem you are trying to solve with the copy of data. It seems like a lot of effort to take this copy of data..
CEWII
October 8, 2009 at 4:00 pm
USE SourceDB
SELECT CAST(columnname1 as NVARCHAR(whatever)) AS columnname1,
CAST(columnname2 as NVARCHAR(whatever)) AS columnname2,
etc, etc...
INTO dbo.DestinationTable
FROM SourceDB.dbo.SourceTable
... then create the index...
Of course, the code above is for when the table doesn't already exist.
If that doesn't work for performance (and it may not), then try BCP OUT to a file and BCP it back into the new table. Behind the scenes, that's what replication does.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 4:02 pm
PS... don't forget to set the recovery mode to BULK LOGGED.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply