Sort failed. Out of space or locks in database '%.*ls'.
This error occurs when you attempt to create an index and there is not enough space in the database to complete the operation or no more locks are currently available.
Creating indexes can require 1.2 times the original table size when building a clustered index (this amount is in addition to the table size–but just during the time that CREATE INDEX is processing). This space must be available in the indicated database or in the segment on which you were attempting to create the index.
When there is not enough space in the database, you may be able to select a specific filegroup on which to place the index. To locate a specific filegroup and to check the size available on the filegroup, execute sp_helpfilegroup:
sp_helpfilegroup 'PRIMARY'
Or use the ALTER DATABASE statement to increase the overall database size. Note that after you increase the size of the database, you may not be allowed to decrease the size. For more information about creating and extending files or filegroups, and altering or moving databases, see ALTER DATABASE.
If your database has no more locks available, execute sp_configure to increase the number of locks. This example increases the amount of locks to 10,000:
sp_configure 'locks', 10000
GO
RECONFIGURE
GO
For the configuration option to take effect, stop and restart the Microsoft® SQL Server™ service. For user convenience, you may want to pause the service and allow current user activity to gracefully finish before officially stopping the service.For more information about starting, pausing, and stopping the SQL Server service, see Starting, Pausing, and Stopping SQL Server.
| CREATE INDEX | sp_configure |
| Errors 1000 - 1999 | sp_helpfilegroup |
| Setting Configuration Options |