Sql-server – Can’t create database in SQL Server Express 2005

sql serversql-server-2005t-sql

One of our clients is running Windows Small Business Server 2011, and they have a SQL 2005 Server Express instance (and several other versions also installed).

Using Management Studio, running as administrator, I cannot seem to create any databases. The error I get is:

Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

The first time this happened, there happened to be a backup task running which had a lock, so I tried again later, and still had the issue. Running EXEC sp_who2 shows only my own user with 2 locks on the model db, both are "sleeping", and seem to come from the Management Studio (.Net SQL Client, same last batch as when I started the app).

Unfortunately I mostly work with MySQL, so all this MS stuff is over my head.

Edit:

Here is the result of SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'model'

Result: 

+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+
| spid |         lastwaittype         | dbid | uid | physical_io | login_time | open_tran |   status    | hostname | hostprocess |       cmd        |       loginame       |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+
|    1 | SLEEP_TASK                   |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | RESOURCE MONITOR | sa                   |
|    2 | LAZYWRITER_SLEEP             |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | LAZY WRITER      | sa                   |
|    3 | LOGMGR_QUEUE                 |    0 |   1 |           0 | 12:45.8    |         0 | suspended   |          |             | LOG WRITER       | sa                   |
|    4 | REQUEST_FOR_DEADLOCK_SEARCH  |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | LOCK MONITOR     | sa                   |
|   52 | MISCELLANEOUS                |    3 |   1 |           0 | 05:14.5    |         0 | sleeping    | BH-SBS   |        6976 | AWAITING COMMAND | BH\nuser             |
|   62 | MISCELLANEOUS                |    3 |   1 |           0 | 04:13.0    |         0 | sleeping    | BH-SBS   |        6976 | AWAITING COMMAND | BH\nuser             |
|   74 | MISCELLANEOUS                |    3 |   1 |           5 | 02:23.1    |         0 | runnable    | BH-SBS   |       22436 | SELECT           | BH\nuser             |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+

Best Answer

I don't really think the problem is creating the database with model as active database as Reaces mentioned in this answer.

It is possible to create a new database using 

USE Model
GO
CREATE DATABASE TEST;
GO

The issue might be some process or session holding lock on the model database and the CREATE Database statement not able to acquire the exclusive lock required.

Check what processes are currently using the model database, you can use

SELECT  *
FROM    sys.sysprocesses
WHERE   DB_NAME(dbid) = 'model'

Depending on what sessions are running, you can probably issue a KILL <SPID>, where <SPID> is the session_id of the session holding a lock on model, and try creating the database again.

Update: Here is a query to find what sessions are currently running, the locks requested and additional information: (Run on model database)

SELECT  resource_type ,
    request_session_id ,
    t.TEXT AS Command,
    request_mode ,
    request_type, request_status,
    s.program_name, s.status
FROM    sys.dm_tran_locks l JOIN sys.dm_exec_sessions s 
ON l.request_session_id = s.session_id
    JOIN sys.dm_exec_requests r ON r.session_id = l.request_session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE   resource_database_id = DB_ID()
Related Question