[dba-SQLServer] SQL Server 2005 unresponsive

I think the issue is Access related and not MS SQL. Had exactly the same
issues when connected to an Oracle DB. I ended up writing code that would
save my results to a temporary table, in the Oracle DB and then would have
Access just retrieve the table results. 

The single process step of gathering the data and downloading a recordset,
via ADO would sometimes cause a hang up. If I used ODBC, it would always
cause a hold up especially on larger data sets.

Have you tried .Net?

Jim


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, September 04, 2009 8:56 AM
To: Dba-Sqlserver
Subject: [dba-SQLServer] SQL Server 2005 unresponsive

I am looking for a little information about an issue I have with my SQL
Server 2005 instance which 
becomes unresponsive when a long running job from Access occurs.

I have a set of stored procedures that builds a table, imports 2 million
record files into an import 
table, appends that to the main table, then when all the 2 million record
files are imported, starts 
building up indexes.  Something is happening (one of these index builds I
suspect) that causes 
Access to "go away" and when this happens SQL Server "goes away" as well.

Access goes "not responding" which makes sense given the single threading
nature of VBA, however the 
server goes unresponsive as well.  In fact I tried to get the database
properties for the database 
being worked in and got a blue screen.  I cannot prove that the blue screen
was caused by SQL Server 
but... that was the last thing I did before the blue screen.

So fine, I opened another instance of management studio so that I could go
build an index in a 
completely different db / table.  That instance of Management studio took a
looooooonnnnnggggg time 
even opening.  Expanding the database normally is instantaneous.  ATM it
takes many seconds. 
Likewise expanding the tables, etc.  Each step which normally "just happens"
takes a much longer time.

Is this normal behavior?  The server is a quad core (AMD) with 16 gigs of
ram, with 12 gigs assigned 
to SQL Server.  I would have thought that SQL Server would allow multiple
things to occur quickly.

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