Could not obtain exclusive lock on database 'model'? Here’s how you find the smoking gun..

If your team owns a process that provision SQL Server databases in masses, you probably encountered the below exception in one of your test clusters, or even in production:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for Database '***'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: 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.

This happens because when your process attempted to create a database, some other process was using the ‘model’ database. It’s possible that someone started a session with ‘model’ via SQL Server Management Studio, or maybe a SCOM process was using it.

Anyways, the important thing is that you find the smoking gun to unlock your process and make sure that this doesn’t happen again...

If you can repro the problem, you can find the culprit simply by running: ‘EXEC sp_who2’, you will get a list of all the processes/users that use any database in your server, including the ‘model’ database.

Or, you can run the following query in order to get info on the processes that use the ‘model’ database:

DECLARE @Table TABLE
(    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT) 
INSERT INTO @Table EXEC sp_who2 
SELECT * FROM  @Table WHERE DBName='model'

Alternatively, you can use:

SELECT nt_domain, nt_username, program_name, cmd, status 
FROM sys.sysprocesses 
WHERE DB_NAME(dbid)='model'

I prefer the last option. The results will look something like this:

However, if your process is running somewhere in the cloud, it might be helpfully to get the blame list programmatically. Here’s a sample code that detects ‘model lock’ type error and adds details on the culprit processes to the exception.

    class Program
    {
        static void Main(string[] args)
        {
            var builder = new SqlConnectionStringBuilder()
            {
                DataSource = "localhost",
                IntegratedSecurity = true
            };

            var sqlConnection = new SqlConnection(builder.ConnectionString);
            sqlConnection.Open();
            try
            {
                Server server = new Server(new ServerConnection(sqlConnection));

                string name = "test" + Guid.NewGuid().ToString("N");
                Console.WriteLine("Creating db " + name);
                Database database = new Database(server, name);
                try
                {
                    database.Create();
                    Console.WriteLine("No Failure");
                }
                catch (FailedOperationException e)
                {
                    HandleModelLockException(server.Databases, e);
                    throw;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally 
            {
                sqlConnection.Close();
                Console.Read();
            }
        }

        private static void HandleModelLockException(DatabaseCollection databases, FailedOperationException e)
        {
            bool modelLockException = ModelDatabaseLockedAnalysis.IsModelLockException(e);

            if (!modelLockException) return;

            string message = ModelDatabaseLockedAnalysis.Analyze(databases);
            
            var s = FormatErrorMessage(message);
            throw new ModelDatabaseLockedException(s, e);
        }

        private static string FormatErrorMessage(string message)
        {
            var builder = new StringBuilder();
            builder.AppendLine();
            builder.AppendLine("Failed to create database since the system database 'model' is locked. " +
                               "Here's a list of processes that currently use the 'model database:");
            builder.AppendLine(message.Replace(" ", string.Empty));
            string s = builder.ToString();
            return s;
        }
    }

The code above creates a database, incase of an error it calls HandleModelLockException. The latter check if the exception (or one of its InnerExceptions) indicates ‘model locked’ type of error. In that case, it calls ModelDatabaseLockedAnalysis.Analyze that queries for the processes that use the ‘model’ database. Than, it simply add the list to the exception.

Here’s the implementation of ModelDatabaseLockedAnalysis:

    public class ModelDatabaseLockedAnalysis
    {
        private const string query =
            "SELECT nt_domain, nt_username, program_name, cmd, status FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'";

        public static bool IsModelLockException(FailedOperationException e)
        {
            const string messageIndication = "lock on database 'model'";

            Exception exception = e;
            while (exception != null)
            {
                bool modelLockException = exception.Message.ToLower().Contains(messageIndication);
                if (modelLockException)
                {
                    return true;
                }
                exception = exception.InnerException;
            }
            return false;
        }

        public static string Analyze(DatabaseCollection databaseCollection)
        {
            var masterDb = databaseCollection["master"];
            DataSet dataSet = masterDb.ExecuteWithResults(query);
            IEnumerable<LockingProcessInfo> processInfos = Parse(dataSet);
            var builder = new StringBuilder();
            foreach (var processInfo in processInfos)
            {
                builder.AppendLine(processInfo.ToString());
            }
            return builder.ToString();
        }

        private static IEnumerable<LockingProcessInfo> Parse(DataSet dataSet)
        {
            DataTable dataTable = dataSet.Tables[0];
            var list = new List<LockingProcessInfo>();
            foreach (DataRow row in dataTable.Rows)
            {
                string domain = (string) row["nt_domain"];
                string username = (string) row["nt_username"];
                string programname = (string) row["program_name"];
                string cmd = (string) row["cmd"];
                string status = (string) row["status"];
                list.Add(new LockingProcessInfo(domain, username, programname, cmd, status));
            }
            return list;
        }
    }
    
    public class LockingProcessInfo
    {
        public LockingProcessInfo(string domain, string username, string programname, string cmd, string status)
        {
            this.Domain = domain;
            this.Username = username;
            this.Programname = programname;
            this.Cmd = cmd;
            this.Status = status;
        }

        public string Domain { get; private set; }

        public string Username { get; private set; }

        public string Programname { get; private set; }

        public string Cmd { get; private set; }

        public string Status { get; private set; }

        public override string ToString()
        {
            return string.Format("Domain: {0}, Username: {1}, Programname: {2}, Cmd: {3}, Status: {4}", Domain, Username, Programname, Cmd, Status);
        }
    }

    internal class ModelDatabaseLockedException : Exception
    {
        public ModelDatabaseLockedException(string message, Exception exception)
            : base(message, exception)
        {
        }
    }

In order to test the code, open SQL Server Management Studio, create a new query on the ‘master’ database, and run:

use model
go

That would lock the ‘model’ database until you close the query window.

Now, run the program and you will get something like:

You can see clearly that the user ‘avezra’ is using the database using SQL Server Management Studio.