If the SQL Server service does not start in a VM, check this:
When there is a large discrepancy between the amount of memory available in the virtualization host and what is allocated to the VM you are on, you may have a situation where the SQL server service simply does not start....more like, tries to start, hangs in there for several seconds and then gives up.
This applies to SQL Server of all versions since 2005, but only applicable to the form of virtualization where the host machine's installed memory is visible to the guest, as in the DataCenter edition of Windows Server 2008 R2 for example. This means that if you are using Hyper-V or VMware ESX/i, you won't have to worry about any of the issue discussed here. However, it's fairly typical in commercial private virtual server services that offer Windows hosting. If you get any Windows hosting service out there that is under $100 a month, the chances are pretty good this situatino applies, and that's a lot of hosting services out there, making this scenario not a fringe case.
Fig.1 - 6GB allocated out of 130GB
Long story short, what happens is that SQL server sees the large amount of installed memory, and start allocating things accordingly. In the example above, it thinks it has 130GB of memory - so it wants 30GB for this buffer, 40GB for that buffer and so on. The end result: SQL Server service will not start, or starts, but blows up on accepting first connection.
To see if you have this issue, check the Task Manager. Upon booting, the memory consumption shoots up to near the limit of what's allocated to the VM - in this case, 6GB. Attepting to establish just one connection will push the whole virtual machine to its limit, SQL Server Management Studio crashes, SQL Server service stops and memory usage goes down to near idle. Start the SQL Server Service, connect, and the same pattern repeats.
This, in our humble opinion, is a bug. SQL Server should be looking at the available memory to the VM, not what the host has. But no matter, we the mere morals need a workaround fast. Fortunately there is one.
== Workaround ==
1. First, go to SQL Server Configuration Manager in the affected VM.
Add "-f" to specify that you want the SQL Server booted in minimal configuration.
Fig.2 "-f" parameter added
2. Stop and Start the SQL Server Service. Now your SQL Server is in minimal configuration, single user mode. You will notice it starts much faster and consumes practically no memory.
3. Go to notepad (or text editor of your choice), and write a script to limit the memory usage. Copy and Paste the following.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 2048; GO RECONFIGURE; GO
save it as "LimitTo4GB.sql" and save it somewhere near the root directory (you will be typing this).
4. Go to command prompt, and type in:
osql -S localhost -E -i "C:\SCRIPT\LimitTo2GB.sql"
where C:\SCRIPT is the folder you specified in step 3.
The result may look like this.
Fig.3 max server memory changed
You have successfully limited the amount of SQL server memory.
5. Go back to the SQL Server Configration Manager and remove the -f you added.
6. Stop and Restart the SQL Server.
