SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 - Databases "not accessible" when service starts


SQL Server 2005 - Databases "not accessible" when service starts

Author
Message
kfk
kfk
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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?
Big Data Man
Big Data Man
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2446 Visits: 750
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
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4470 Visits: 7195
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.
kfk
kfk
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88957 Visits: 45284
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>Wink 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

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


kyleheath33
kyleheath33
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 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 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
kfk
kfk
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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>Wink 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88957 Visits: 45284
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

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


kfk
kfk
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88957 Visits: 45284
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search