[dba-SQLServer] System not responding for seconds at a time

Well... it appears that I have fixed the issue.  After Googling "slow 
server response time" or something similar I ran across references to a 
"Maximum Memory" property for SQL Server.  I finally found that and it 
was set to something insane like 200000000 MEGABYTES (several petabytes 
of server memory).  While I can dream of such a machine, it ain't 
happening here!

I adjusted the Maximum Memory down to 2 gigs and the "amount available" 
in Task Manager Performance tab popped up to 4 gigs.  Having done that, 
and after perhaps 30 seconds, suddenly my machine is responding in a 
normal way.  I am now adjusting that number to "leave" perhaps 2 gigs 
available to applications other than SQL Server.  I probably don't even 
need 2 gigs but it is critical that I don't end up unable to do anything 
on that machine.

John W. Colby
www.ColbyConsulting.com


jwcolby wrote:
> I don't quite know where to address this so I am cross posting it.  I am 
> working on a fairly powerful server running Windows 2003 x64 and SQL 
> Server 2003 x64.  It has 8 gigs of memory.  No malware software, 
> software firewall, virus scanner etc.  Nothing.
> 
> I am running a file shrink on the SQL Server file, trying to remove 
> about 140 gigs of empty space.  That process has been running since last 
> night, well over 8 hours now.  It APPEARS that process is using all of 
> available memory since task manager shows only about 200 megs 
> "available".  However if you look at the process tab, no process says it 
> is using more than 100 megs of ram.  The performance tab shows almost no 
> CPU cycles used, 4 cores hanging out about 0 - 10% used, and even then 
> only one of the cores appears to be doing anything.
> 
> The computer is "stuttering" badly.  Try to do anything - change to a 
> different program, page up in visual studios code etc, and the computer 
> will usually hesitate before doing whatever you requested.  I am trying 
> to work on a VB project and can't get anything done because as I move 
> around in the doc it may take 2 to 5 seconds just to respond to my 
> request to move my cursor.
> 
> Has anyone seen SQL Server lock up the the system like this, IOW is it 
> SQL Server?  Does anyone know how long the file shrink could take - 
> days, weeks, months?  Does anyone know how to cancel the file shrink? 
> This is the most powerful server I have, quad core, running x64 
> software, 8 gigs, high speed raid arrays etc.
> 
> Unfortunately I was in the middle of a vb.net project yesterday before I 
> started the shrink running after work and the server is unusable for 
> anything right now.
>