Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2005 - Databases "not accessible" when service starts Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 2:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 5:57 AM
Points: 4, Visits: 13
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?
Post #978559
Posted Wednesday, September 1, 2010 3:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:19 PM
Points: 2,153, Visits: 647
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
Post #978570
Posted Wednesday, September 1, 2010 4:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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.
Post #978606
Posted Wednesday, September 1, 2010 4:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 5:57 AM
Points: 4, Visits: 13
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...
Post #978613
Posted Wednesday, September 1, 2010 6:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 39,976, Visits: 36,337
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #978691
Posted Wednesday, September 1, 2010 8:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:40 AM
Points: 201, Visits: 811
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 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
Post #978855
Posted Thursday, September 2, 2010 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 5:57 AM
Points: 4, Visits: 13
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 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.
Post #979425
Posted Thursday, September 2, 2010 5:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 39,976, Visits: 36,337
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #979428
Posted Thursday, September 2, 2010 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 5:57 AM
Points: 4, Visits: 13
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.
Post #979442
Posted Thursday, September 2, 2010 6:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 39,976, Visits: 36,337
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #979459
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse