SQL server thrashing?

[sql_server_user]

I have a loading process that periodically inserts a lot of relatively
large files (up to 400MB) into a few substantial tables (about 50GB).
Sometimes, after inserting many files with no problem, the DB server
memory usage will suddenly jump from about 4.5GB to about 5.5GB (the
server has 4GB physical), the normally heavy disk activity will stop,
and the normally low CPU utilization will spike to about 90%, and stay
there, and the DB will become totally unresponsive to any connection
attempt - sqlcmd dedicated admin connections included.

Has anyone seen this before? Does anyone have any idea what it might
be, or how I can avoid it? If I kill the sqlserver process and start it
up again, which entails a lengthy recovery process, I incur an
unacceptable service outage.

Thanks,
Seth

[Erland Sommarskog]

sql_server_user (ka*******@gmail.com) writes:

I have a loading process that periodically inserts a lot of relatively
large files (up to 400MB) into a few substantial tables (about 50GB).
Sometimes, after inserting many files with no problem, the DB server
memory usage will suddenly jump from about 4.5GB to about 5.5GB (the
server has 4GB physical), the normally heavy disk activity will stop,
and the normally low CPU utilization will spike to about 90%, and stay
there, and the DB will become totally unresponsive to any connection
attempt - sqlcmd dedicated admin connections included.

Has anyone seen this before? Does anyone have any idea what it might
be, or how I can avoid it? If I kill the sqlserver process and start it
up again, which entails a lengthy recovery process, I incur an
unacceptable service outage.

That sounds bad. Not the least that the SQL Server process gets more
virtual memory that the available physical memory. And it sounds even
worse that the dedicated admin connection does not provide access.

It's impossible to tell what might be going on, but it smells bug to
me. I urge to open a case with Microsoft on this, not the least they
need to know about it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[sql_server_user]

Thanks, Erland. I'll look into it further.