September 1, 2010 at 2:35 am
The background story:
I'm working on trying to get databases up and running at a customer's server.
From what I understand, the OS partition on their server ran out of disk space, and after that there were major issues with their databases.
The databases are interestingly not located on that partition, so why exactly they were affected is a mystery to me.
I have now installed a new instance (because the old one seemed unstable, and failed to be updated to SP3), which has also been updated to SP3 (unlike the original), and moved all the databases over to the new instance. All databases have been attached, but for some reason some (but not all) databases are always "not accessible" when the SQL server is started.
In order to make them become usable, I have to take them offline and then online again, either manually or using a script.
When that is done, they seem to work fine, however, if the SQL service is restarted again, they go back to being inaccessible.
Since this happens with several hundred databases, doing this every time it starts is not really an option, and in any case, when doing this the queries eventually start to fail with "There is insufficient system memory to run this query." even though there is plenty of memory free, and the SQL service must be restarted to make that issue go away, which of course, also requires the script to be run from the start again...
I have run "DBCC CHECKDB" with REPAIR_REBUILD on them, and it reports that there are no errors, but the problem nevertheless persists.
Has anyone got any suggestions as to what might be causing this problem, and how to fix it?
September 1, 2010 at 3:09 am
Have you checked your error log? This normally indicates a possible path problem to the database files.
Please do the following and post results:
use master
select databaseproperty(‘db_name’,'isShutdown’)
Check if your database files are marked read-only.
Please let me know.
Thank you,
Phillip Cox
September 1, 2010 at 4:05 am
kfk (9/1/2010)
I have run "DBCC CHECKDB" with REPAIR_REBUILD on them, and it reports that there are no errors, but the problem nevertheless persists.
You shouldn't really be running CheckDB with REPAIR_REBUILD on a database to check for errors e, Use with ALL_ERRORMSGS to check for errors.
September 1, 2010 at 4:12 am
Phillip Cox (9/1/2010)
Have you checked your error log? This normally indicates a possible path problem to the database files.
I checked the error log after posting, and what I found is that apparently at some point during the startup procedure, while it is brining up the databases, it starts spewing out "There is insufficient system memory to run this query." messages. However, there is plenty of both physical RAM and virtual memory free, so there is really no reason why it should be complaining about memory. It does however seem that this is the real issue.
use master
select databaseproperty(‘db_name’,'isShutdown’)
Check if your database files are marked read-only.
It returns NULL for the troublesome databases (when they are "not accessible" at least), and 0 for the working ones.
steveb. (9/1/2010)
You shouldn't really be running CheckDB with REPAIR_REBUILD on a database to check for errors esp. on a customer site as this can cause data loss, Use with ALL_ERRORMSGS to check for errors.
According to Microsoft, REPAIR_REBUILD "Performs repairs that have no possibility of data loss".
UPDATE:
I tried copying one of the troublesome databases to a local VM running SQL Server Express 2005 and attached it there, then tried to restart the service. It became available fine.
As far as I can tell, that means there isn't anything wrong with the databases, and the problem must be something to do with the server. I'd still like to find out what could be causing it though...
September 1, 2010 at 6:21 am
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?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2010 at 8:51 am
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?
Kyle
September 2, 2010 at 5:09 am
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.
September 2, 2010 at 5:13 am
kfk (9/2/2010)
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.
How much memory does the server have?
Is AWE enabled? (along with /PAE in the boot.ini) If the /3GB switch set?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2010 at 5:50 am
GilaMonster (9/2/2010)
kfk (9/2/2010)
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.
How much memory does the server have?
Is AWE enabled? (along with /PAE in the boot.ini) If the /3GB switch set?
If by AWE you mean the "Use AWE to allocate memory" checkbox in the SQL server memory settings, then it is not enabled. (as by default, presumably, as I don't know what it does and haven't changed it)
The server has 4GB of physical memory, in addition to about 8GB of virtual memory.
PAE appears to be enabled, and "boot.ini" does contain the /3GB switch.
September 2, 2010 at 6:30 am
You do not want SQL using virtual memory. It uses memory to avoid having to go to disk, so using disk that's pretending to be memory is not a good idea.
With those settings, set max memory no higher than 3GB (not that SQL could use more than 3GB anyway)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2010 at 6:39 am
With it being 32bit you will need AWE enabled for SQL to address more then 3GB of memory without that your not using all the memory available also you should also enable lock pages in memory, below is a good link to explain in more detail.
http://msdn.microsoft.com/en-us/library/ms190673.aspx
Warning: This will require a restart of the SQL services to take effect.
EDIT: As gail said virtual memory setup is not good for SQL, best advice is just to get some more RAM is possible.
Kyle
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply