Optober – SQL sp_configure options – Allow Updates

Option Name:  allow updates

Usage:  sp_configure ‘allow updates’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  Once upon a time if you wanted to update system tables in your database you could do so, and this was the magic switch you needed to throw to do it.  In the current enlightened times people realize that updating system tables is generally speaking not a good idea, and if you really need to do it to (for example) fix an allocation error corruption, there is now emergency mode repair which can be used.

When should I use it:  Never after SQL 2000, and even in SQL 2000 only if you are very clear on what you are doing.  Books Online is pretty clear on this.  If you’ve updated system tables then your database is not in a supported state.  This feature currently has no effect and will be removed in a future version of SQL server.

What else should you know:  There are other ways to update system tables if there is corruption within your database.  But you need to be aware that if you change an underlying system table the date of the last change is logged in the header of the database.  It will be there for everyone who cares to look(ie Microsoft Support) to see.  I’m not going to go into details on that accept to point you over to Paul Randal’s blogs on database corruption.  Though if you are looking at this stuff god knows how you would have found my blog before his.

Leave a Comment

Your email address will not be published.