Can't create database in SQL Server Express 2005

Question

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 |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+

Answer 1

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()

• sql_handle: Max Vernon, Thanks for the edit.
• QuickDanger: See edit to question. I trimmed down the columns and posted the results of the 2nd query you have in your answer. The 1st query still gave the same error I had before.
• sql_handle: Did you try restarting SSMS from which you are running the queries?
• QuickDanger: Yup. Many times. :/
• sql_handle: @QuickDanger I updated my answer with a new query, run that on the model database and add all the results to the question.