May 16, 2012 at 12:36 pm
[font="ArialI have an SSIS job (SQL Server 2008) that refreshes data nightly. Here's a synopsis of how it's done:
1) Script out all FKs into a table at the destination DB
2) Truncate all tables at the destination
3) Refresh data in destination with data from source DB
4) Reapply FKs
The job started producing the following error:
Code: 0xC002F325
Source: Refresh PreProd with Prod CCIMS Data Transfer SQL Server Objects Task
Description: Execution failed with the following error: "ERROR : errorCode=-2147024784 description= helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".
I reran the job again, but this time watched it run through SQL Profiler. I realized it failed on the same table each time. This table is nothing special, but data takes forever (>40 min) to copy even when I do a simple import from the same table at the source DB.
I verified the table attributes are exactly the same on source and destination DB. I have tables in this DB with close to a million rows, and those tables barely take 5 min to transfer data from source to destination. Here are the table stats for the "problem child":
Rows 16386, reserved 326384KB, data 316040kb, index size 848kb, unused 9496kb
Next I ran dbcc checkdb. Here are the results:
DBCC results for '(database name'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 8439 rows in 81 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 1377 rows in 23 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 1403 rows in 30 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
I then removed that table from the Execute SQL task within SSIS. The refresh ran successfully. So I added a step to the job to pull the data over for that problem table alone. It still takes forever to copy the data. My first thought is that there's a bad sector on that server drive, but I want to rule out all other possibilities before I ask the SAs to scan the hard drive. Any ideas on what could be wrong with this one table? Running a simple import manually takes 45 minutes for those 16K rows to come over. That's unacceptable.
Thanks!
[/font]
May 16, 2012 at 1:07 pm
Can you please provide DDL for the table including indexes and such?
Jared
CE - Microsoft
May 16, 2012 at 1:58 pm
Here it is (there are no triggers on the table):
CREATE TABLE [dbo].[MTSA_Data](
[mtsa_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ssn_id] [char](9) NOT NULL,
[uic_cd] [varchar](6) NULL,
[uic_tx] [varchar](100) NULL,
[pers_cntrl_nbr_id] [char](10) NULL,
[report_dt] [smalldatetime] NULL,
[special_instructions_tx] [text] NULL,
[dml_cd] [char](3) NULL,
[dmsl_cd] [char](3) NULL,
[con_cd] [char](3) NULL,
[proj_cd] [char](3) NULL,
[record_type_cd] [char](1) NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[mdc_cd] [char](2) NULL,
CONSTRAINT [PK_MTSA_Data] PRIMARY KEY CLUSTERED
(
[mtsa_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MTSA_Data] WITH CHECK ADD CONSTRAINT [CK_MTSA_Data_record_type_cd] CHECK (([record_type_cd]='N' OR [record_type_cd]='A' OR [record_type_cd]='R' OR [record_type_cd]='D'))
GO
ALTER TABLE [dbo].[MTSA_Data] CHECK CONSTRAINT [CK_MTSA_Data_record_type_cd]
GO
ALTER TABLE [dbo].[MTSA_Data] ADD CONSTRAINT [DF__MTSA_Data__rowguid] DEFAULT (newsequentialid()) FOR [rowguid]
GO
ALTER TABLE [dbo].[MTSA_Data] ADD CONSTRAINT [PK_MTSA_Data] PRIMARY KEY CLUSTERED
(
[mtsa_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MTSA_Data_rowguid] ON [dbo].[MTSA_Data]
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MTSA_Data_ssn_id] ON [dbo].[MTSA_Data]
(
[ssn_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
May 16, 2012 at 2:04 pm
My first guess is the TEXT column. 2nd guess is the check constraint. Were any of these things added right before you saw the first error?
Jared
CE - Microsoft
May 16, 2012 at 2:48 pm
I thought it was the TEXT column too, but there's another table in this DB that has 5 TEXT columns and it doesn't seem to have a problem. It has a 26K rowcount. Aren't TEXT data types being phased out soon?
Regardless, I just created a test table that is just like the table with the 5 TEXT columns. There were no CK constraints on that table, just PK. I tried copying table data from the source to the destination and it worked. Very slow (10 min for 3K records), but at least it moved. The table above spends a large amount of time setting up for the import and then takes forever to import. Should I try dropping the check constraint or changing the TEXT data types to VARCHAR(2000) as a test?
May 16, 2012 at 2:52 pm
If you know the data in the TEXT column is not longer than 2000 characters, sure. You change it to varchar(max) instead.
May 16, 2012 at 3:03 pm
I just tried to guage the length of the text fields with len(cast(special_instructions_tx as varchar(max)))
Some lengths returned were > 35K
May 16, 2012 at 3:30 pm
I just tried this:
Created a new table named MTSA_Data2 at both the source and destination databases. Changed the TEXT datatype to VARCHAR(MAX) on both. I left off the Check constraint on both source and destination tables as well.
I then executed the following statement:
INSERT INTO mtsa_data2
(ssn_id, uic_cd, uic_tx, pers_cntrl_nbr_id, report_dt, special_instructions_tx, dml_cd, dmsl_cd, con_cd, proj_cd, record_type_cd, mdc_cd)
SELECT ssn_id, uic_cd, uic_tx, pers_cntrl_nbr_id, report_dt,
CAST(special_instructions_tx AS VARCHAR(MAX)), dml_cd, dmsl_cd, con_cd,
proj_cd, record_type_cd, mdc_cd
FROM mtsa_data
Then I pulled up the import/export wizard to manually move the data from the MTSA_Data2 table at the source to the empty MTSA_Data2 destination table. No CK, no TEXT datatypes.
Same problem -- on the Wizard's status window, it's been at the "Executing" stage for over 10 minutes. There are exactly 16,351 rows to pull over.
May 16, 2012 at 3:35 pm
Curious, what happens if you leave out the varchar(max) field while moving the data using the import/export wizard?
May 16, 2012 at 4:30 pm
Tried a few things - first I noticed the TEXTIMAGE_ON [PRIMARY] within the PK constraint in the DDL. Since there was no text image, I removed that and tried the import. No luck.
Next, I removed the ROWGUIDCOL column just for grins. No luck.
Then I tried the import wizard and chose "ignore" to the special_instructions_tx column for the copy. Interestingly no luck either. Just churned forever and ever at the "Executing" stage.
I remove the VARCHAR (MAX) column entirely from both sides and then run the wizard and all is well. However, I still have another table with 5 text columns and while it is slow it does copy. This smaller table I'm wrestling with only has 1 text column.
May 16, 2012 at 6:00 pm
Andrea Sawyer (5/16/2012)
Tried a few things - first I noticed the TEXTIMAGE_ON [PRIMARY] within the PK constraint in the DDL. Since there was no text image, I removed that and tried the import. No luck.Next, I removed the ROWGUIDCOL column just for grins. No luck.
Then I tried the import wizard and chose "ignore" to the special_instructions_tx column for the copy. Interestingly no luck either. Just churned forever and ever at the "Executing" stage.
I remove the VARCHAR (MAX) column entirely from both sides and then run the wizard and all is well. However, I still have another table with 5 text columns and while it is slow it does copy. This smaller table I'm wrestling with only has 1 text column.
And I will bet you that the table you are having a problem with has more BLOB data than the the other table with 5 text fields. You indicated that in this particular table there were records where the text column had values exceeding 35K in length.
What you may want to try is this. Add a second file group to the database, call it TextData for lack of a better name. When you create the destion table again, include the TEXTIMAGE clause in the create statment and have it point to this new filegroup, TEXTIMAGE_ON TextData.
If you want to know how large to make this file group, sum the lengths of the data in the column and to be safe, multiply by 1.5.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy