[dba-SQLServer] SQL Server hanging

James Barash James at fcidms.com
Thu Aug 10 10:00:35 CDT 2006


John,
Here is an exaple of a dts package that loops through all the files in a
directory and imports them by dynamically changing the source object. It's a
bit hard to understand at first but I've used it as a starting point for
doing something very similar to what you're doing now.

http://www.sqldts.com/default.aspx?246

It's written for SQL Server 2000. If you are using 2005, it is much easier
to create an SSIS package that does the same thing since looping is written
into the dtsx object set.

Hope this helps


James Barash

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Thursday, August 10, 2006 10:06 AM
To: dba-sqlserver at databaseadvisors.com
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