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...