GilaMonster (9/1/2010)
kfk (9/1/2010)
According to Microsoft, REPAIR_REBUILD "Performs repairs that have no possibility of data loss".Correct, but you should still not be running that to check for errors. To just check for errors run the following:
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
What's the SQL instance's max and min memory set to?
I've tried setting them to various values, from 256MB to 4GB.
While 256MB seemed to make the problem worse, I couldn't tell any difference between 2GB and 4GB.
The problem was still the same, and there was still plenty of memory free.
kyleheath33 (9/1/2010)
Just for information the "There is insufficient system memory to run this query." error can also occur if you are running a query remotely from SSMS and it goes on the local systems memory, not the host but in this case if it's in the logs it def the host which is having a memory issue (there was a technet article on this but couldn't find it). In your earlier post you said it happens to several hundred databases? If those are all hosted on one server thats a lot :hehe: if that is the case what is the spec of the server? I would also (as gail suggested) look into the memory allocations within SSMS. Also what version of SQL is running and is the cpu 32 or 64 bit?
I'm not sure about the CPU, but the OS and SQL server is 32-bit.
I'm running SSMS from the same server that is running the database engines, and as I said, the machine generally has plenty of memory and virtual memory free when the issue starts, so I don't think it's really a memory issue at all.
As for the databases, there are actually nearly 3000 databases, it's just that the problem only seems to affect the 600 or so ones with the highest IDs.
Most of the databases are very small though.
At this point, I've temporarily "solved" the issue by creating another instance and moving over some older databases (about 1300 of them) that are mainly for historical purposes, and not frequently used.
I've also suggested that they get the server checked out and/or reinstalled, as there is obviously some pretty fishy stuff going on there.