1

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

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()
  • 1
    Max Vernon, Thanks for the edit. – sql_handle Nov 2 '15 at 20:45
  • 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. – QuickDanger Nov 3 '15 at 0:13
  • Did you try restarting SSMS from which you are running the queries? – sql_handle Nov 3 '15 at 0:17
  • Yup. Many times. :/ – QuickDanger Nov 3 '15 at 0:29
  • @QuickDanger I updated my answer with a new query, run that on the model database and add all the results to the question. – sql_handle Nov 3 '15 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.