[dba-SQLServer] SQL Server hanging

Michael Maddison michael at ddisolutions.com.au
Wed Aug 9 20:32:35 CDT 2006


John,

Yes SQL will stop and 'think' in situations like yours  :-)
Stopping SQL when it is busy is almost always a bad idea.
It will attempt to recover, roll back etc when you restart it again as
you discovered.

IIRC you can configure DTS to log any errors.
You can also specify that each package operates within a transaction.
1 fails all fails and rolls back, which is probably what was happening
when SQL stopped responding.
It takes a while to roll back a large number of transaction.  I have
learnt to give it
the benefit of the doubt and 99/100 it will eventually roll back and
then display whatever
the problem was with the package.  

Making the data chunks smaller is also a good idea.

You could also look at bcp to import your files, it should be faster.

cheers

Michael M

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

It turns out it was just thinking.  Unfortunately I am now totally
confused on which files succeeded and which failed.  

Arthur, I do think that adjusting the number of records that are
imported before a commit is a grand idea.  I also need info on how to
shrink the log file.  As you might guess I am still muddling through
this SQL Server thing.

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 JWColby
Sent: Wednesday, August 09, 2006 3:52 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Server hanging

Is there any way to tell what is going on with SQL Server when it hangs?


I am trying to import these tens of millions of records into SQL Server.
They are flat file comma delimited text files, 3 million recs / file.
Everything is working fine, I have ~12 files imported (36 million
records) and I am humming along.  I have about 6 import processes
running (import and export data manager) at once and of course things
slow down but suddenly none of the imports is progressing at all.  I try
to connect with EM and when I try to expand the "databases" tree it
gives an error connecting, "unable to obtain a lock" or some such.  I
let the import functions continue to run but 20 minutes later still not
a single more record imported.  So I use task manager to shut down all
of the import processes, shut down EM, shut down the SQL Server manager
normally, unload it, shut off the computer, restart it.  Try to connect
with enterprise manager and no go.  In fact it APPEARS to be locked up.
When I go in with task manager and look at the process, it is slowly
gaining "memory used" until it basically runs out then starts increasing
the page file size, until that hits about 1.7g and then that too stops
incrementing but EM is still hung.

CPU usage is now down about 2%, no "more" memory being grabbed by EM,
the page file is static, but EM is hung.

I need to find out what is going on here.  I have about 12 of these 3
million record text files imported and don't want to have to clean it
out and start over, but worse, I have no clue why it stopped working.
Is there any (free) tool that will tell me what SQL Server thinks it is
up to?  If I can't get EM to see the databases I am basically hosed
here.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
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