Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -6 down vote favorite

I have a SQL Server Express instance with 20,000 databases (twenty thousand databases).. and the service won't start.

I get error message 1053:

The service did not respond to the start or control request in a timely fashion.

I'm just doing some testing of sharding, trying out some ideas. Apparently, I shouldn't leave this many databases attached at the same time... I can work around this... I just wish I knew more how to predict / forecast regarding the real limits of SQL Server 2012 Express.

Error 1

There is insufficient system memory in resource pool 'internal' to run this query.

Error 2

Script level upgrade for database master failed because upgrade step msdb110_upgrade.sql encountered error 701, state 123, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the master database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Error 3

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

I assume I should uninstall and reinstall, and then attach half as many databases and split the load between two database servers?

share|improve this question
9  
If you want to do enterprise level things, then use enterprise level versions. You get what you pay for. This sounds like you're trying to "roll your own" scalability solution using free SQL Server Express. How much is your time costing and do you expect this to be reliable? – gbn Oct 29 '12 at 10:16
3  
One day, a few years from now, when you've moved on from the current company and your replacement starts looking at things...the foul things that will come out of his mouth when he sees this... – rfusca Oct 29 '12 at 12:03
8  
We're mostly DBAs too here: we say it's madness. Join us in chat if you want chat.stackexchange.com/rooms/179/the-heap – gbn Oct 29 '12 at 15:36
4  
"and other freeware databases SUCK", speaking as an Oracle DBA, I think postgres is great (MySQL and SQL Server is like apples and oranges, not so postgres) – Jack Douglas♦ Oct 29 '12 at 15:46
3  
Wow...I don't think anyone on here is going to have an answer that works for you that isn't. Rethink your design to something that is feasible. Sorry chief but what you are doing attempting is just asking for trouble. – Zane Oct 29 '12 at 16:21
show 17 more comments

migrated from stackoverflow.com Oct 29 '12 at 9:37

3 Answers

up vote 11 down vote accepted

Ok, here's the deal:

a) this is a sucky question, because people got busy discussing the business aspect of things, when you needed a technical answer. We've discussed some ways to clean that up, but the comment conversation provides good answers that you didn't include in your question. Add those in and we'll clean the comment stream up.

b) the solution that should work for you:

You need something that tells the db server to attach and detach dbs. That particular something, which I shall call the db-manager, needs to have it's own state, that at the very end of quitting writes out that everything is shut down neatly. If not, you need to keep track of what all db's were attached at the last open time, and you need to move all those files. during startup.

So let me break this down again a different way:

Every (manually initiated - as far as services is concerned, as opposed to disabled or automatic) startup of the SqlExpress needs to come up with ZERO attached databases. db-manager has a flat-file record system and db-manager is responsible for starting the SqlExpress service.

This keeps the SqlExpress instance startup time incredibly low, because it's not attaching anything. After SqlExpress is running, db-manager then attaches the necessary instances one at a time (so, in sequence, not in parallel) to the instance, and it ensures that it knows what is currently loaded (with a file written to keep things managed in case of failure) and you have to unload them from the instance when done. Then when you finally let db-manager shut down SqlExpress, you end up with a no-dbs-attached SqlExpress, which starts up again almost immediately.

The issue is when the database gets shutdown incorrectly, and it still has stuff attached to it. That's why you keep the state consistency checked in the db-manager app. This app should move files when the service wasn't shut down correctly, while the service is off. Then when the SqlExpress opens, it finds that the files can't be found, and ignores them, and you can remove them then.

This is how you will have to work with this solution that you're devising, as otherwise you're asking SqlExpress to do something it wasn't designed to do, as far as I can tell. This should let you attack all 32k databases if you like.

share|improve this answer
1  
Have a look at trace flag 3608 to start up the server with only master database: msdn.microsoft.com/en-us/library/ms188396.aspx – Cade Roux Oct 29 '12 at 16:33
Oooh, ok, that saves moving the files, but still requires you to manage state to know that you need to do that, right? Or do you just always start it with flag 3608? Seems like trace flags are heavy – jcolebrand♦ Oct 29 '12 at 16:35
1  
@jcolebrand Exactly, let me sprinkle a little pixie dust on that for you... – Cade Roux Oct 29 '12 at 16:43
1  
I don't have SE installed to test with, so I can't exactly do this. You're in a great position to test this specific thing. – jcolebrand♦ Oct 29 '12 at 16:48
1  
"This should let you attack all 32k databases if you like." I like that. I hope you did it on purpose. This question needs an attack answer. – 孔夫子 Nov 14 '12 at 1:44
show 7 more comments
up vote 5 down vote

This sounds like mind-flaying insanity, both for you and the poor DBA/maintenance programmer that happens across it later. I'm sure the total cost of labor and support would outpace licensing for Standard Edition. You should be able to get a per-server license for a few thousand (with a single four-core CPU). Do that from the start, before you suddenly find yourself needing to migrate data from thousands of databases into a single database.

(I'm pretty sure there's a way to increase the service start timeout duration, but I don't remember it off the top of my head, and... don't do it.)

share|improve this answer
It's not just a question of one or two database servers. I'm doing this with a couple of machines. I've heard of Oracle servers running on tape libraries.. I don't think that it's that ridiculous to attach / detach a database whenever I want to do a query. It's not like I'm going to use MS Access as a database store, and I can easily detach and reattach these databases at will. I just won't keep more than ~1,000 databases online on the server at the same time. – Aaron Kempf Oct 29 '12 at 14:56
and correct me if I'm wrong.. but I can use more than 4 cores on SQL express? Or has that been verified? I thought it was a SINGLE CPU limit. – Aaron Kempf Oct 29 '12 at 15:05
1  
@AaronKempf "Lesser of 1 socket or 4 cores" – swasheck Oct 29 '12 at 15:45
so I'd probably be better off on SQL 2008 R2 Express, that's what you're telling me. – Aaron Kempf Oct 29 '12 at 16:12
1  
@AaronKempf you'd probably be better off using SQL Server Developer Edition. – swasheck Oct 29 '12 at 16:26
show 1 more comment
up vote 3 down vote

If you simply need a longer timeout value for starting the service, this might help:

To increase the service startup time yourself, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
Name:   ServicesPipeTimeout
Type:   REG_DWORD
Data:   The number of milliseconds before timeout occurs during service startup

For example, to wait 60 seconds before the service times out, type 60000.

Remember, quit Registry Editor, and then restart the computer for the change to take effect.

In this case, setting the value to 2,147,483,647 (the largest number possible) (0x7FFFFFFF) should be almost long enough.

share|improve this answer
+1 This seems a useful suggestion, and much better than starting with -T3608. – Paul White Dec 1 '12 at 4:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.