[dba-SQLServer] SQL Server hanging

artful at rogers.com artful at rogers.com
Thu Aug 10 10:03:23 CDT 2006


DTS Global variables might help solve your problem, JC. Look it up in BOL. The gist is that you can use a DTS global variable in place of an actual filename or even a PK. As an example of the latter, a while back I had need to copy all the rows from variables tables that were in a hierarchy, specifying the PK of the topmost table in the tree. 

IOW:
T1.PK = 123
T2.FK = 123
T3.PK = all the rows where the FK = the T2.PKs returned from the previous step.

I used to save the package, then clone it for each new T1.PK whose relational tree I needed. Then I discovered global variables, and was able to use the same package over and over, just by redefining the global variable.

HTH,
Arthur

----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Thursday, August 10, 2006 10:05:57 AM
Subject: Re: [dba-SQLServer] SQL Server hanging

It doesn't appear that it is possible to do this via the wizard, or at least
I can't see how.  However under data transformation / local packages / right
click / New package you can get at the designer which does allow all of this
stuff.  

I have been doing more than one transform (several different files) from the
simple wizard.  Under normal circumstances, the second and subsequent
transforms wait for the first to finish.  However, and I have not determined
how or why, sometimes the second or third or fourth transform that I define
will start executing as well.  Sometimes all of them at once!!!  When that
happens of course, things slow down a LOT.  I could never tell if the slow
down was linear or not.  Unfortunately, I also started getting "unable to
obtain lock" failures when this happens.  Of course the error messages don't
make clear whether the locks are database licks or file system locks.  I
suspect that it is db (row) locks.  

I thought perhaps the designer would give enough more control that it would
significantly speed up the imports.  I set the properties to use a table
lock which it seems would speed things up.  I also set it up to read 1000
records (lines, or at least so I think) into the source buffer from the
source text file instead of one line at a time, thinking that this too might
speed things up.  I did however ask it to commit after each 1000 rows which
is supposed to slow it down a bit.  All in all I cannot see any visible
speedup, though it doesn't appear to be any slower either.

I must say that the process of switching from file to file using the same
transform is a bit cumbersome.  You have a source object and a destination
object, and then a transform object.  The transform defines all these
properties such as buffer size, table / row locks, commit after N rows etc.
What makes no sense to me however is that it ALSO defines the source, i.e.
the NAME of the table is also embedded in the transform object.  This just
makes no sense to me since the transform operates on a source (thus already
defined) and a destination.  The upshot is that in order to move from one
text file to the next using the same setup (transform) I have to open the
source object and change the file name there, then open the transform object
and change the source file there.  No big deal except that doing the change
in the transform object then prompts for something like "specify which
transform to delete", very confusing.

The upshot of all this is that I do now have more control than using the
simple wizard, however it is more of a PITA.  This may be mitigated by being
able to simply edit the transform package in two places (the source object
table and the transform object source property) instead of running through
the entire wizard again.  I may also be able to get around the locking issue
by going back to the row level locking, but commit after every 1000 rows.
This might allow me to queue up 4 or 5 of these and let them run so I am not
baby sitting this thing all day.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin Reid
Sent: Thursday, August 10, 2006 9:12 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server hanging

John
 
Look up Insert Commit Size re DTS
 
Soorry my server is down at the moment so cant check it for you.
 
Martin
 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974477
 

________________________________

From: dba-sqlserver-bounces at databaseadvisors.com on behalf of JWColby
Sent: Thu 10/08/2006 13:42
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server hanging



>using DTS, you can adjust the "commit after # rows" level.

Is this possible using the DTS wizard?  I am not able to find this property
anywhere and of course the help doesn't.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
artful at rogers.com
Sent: Wednesday, August 09, 2006 5:19 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server hanging

Assuming that you are importing the data using DTS, you can adjust the
"commit after # rows" level. Cut it back to say 100 or 1000 rows and then
see what is happening.

----- Original Message ----
From: Martin Reid <mwp.reid at qub.ac.uk>
To: dba-sqlserver at databaseadvisors.com
Sent: Wednesday, August 9, 2006 4:04:10 PM
Subject: Re: [dba-SQLServer] SQL Server hanging

John

Try and run a trace on the server and database your importing to. Open SQL
Proflier -> File -> New->Trace

See what that shows you.

Martin

Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast

tel: 02890 974477



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com <http://www.databaseadvisors.com/> 





_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com <http://www.databaseadvisors.com/> 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com <http://www.databaseadvisors.com/> 




_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com








More information about the dba-SQLServer mailing list