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/

Advertisements

3 thoughts on “The case of the obnoxious locks value

  1. Pingback: Wiki: SQL Database Engine Startup Failures « TroubleshootingSQL

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s