The case of the obnoxious locks value

Yesterday I worked with Argenis [Blog | Twitter] regarding an interesting startup failure for a SQL Server standalone instance.

The error in the SQL Errorlog was:

2011-03-30 14:43:18.46 Server      Large Page Extensions enabled.
2011-03-30 14:43:18.46 Server      Large Page Granularity: 2097152
2011-03-30 14:43:18.46 Server      Large Page Allocated: 32MB
2011-03-30 14:43:18.46 Server      Large Page Allocated: 32MB
2011-03-30 14:43:18.55 Server      Large Page Allocated: 32MB
2011-03-30 14:43:18.56 Server      Large Page Allocated: 32MB
2011-03-30 14:43:38.07 Server      Error allocating 16777216 lock owner blocks on startup

After trying the removal of the trace flags that were enabled, we still couldn’t get the SQL instance to startup. The next thing we did was to start the SQL instance from command prompt using the following parameters:

sqlservr.exe –c –m -T3608 -f

The above started the SQL Server instance in minimal configuration mode without any errors. Now the above lock block allocation seemed a bit too large. So we checked the sp_configure output for the configured value of the “locks” configuration option. And voila, we had our answer! The value of “locks” was set to 16777216. We then changed this value to 0 (ZERO) which is the default using sp_configure and restarted SQL instance. This time the SQL instance came online!

Takeaway:

If the SQL instance fails to start, try starting the SQL instance using minimal configuration. If SQL comes online, check the configuration settings for any untoward configuration values.

Reference:

Wiki: SQL Database Engine Startup Failures

https://troubleshootingsql.com/2010/11/23/wiki-sql-database-engine-startup-failures/