Create database creates an exclusive lock on model

Creating databases is a common task for a DBA. I don’t usually reflect on it anymore since I’ve been in the SQL Server DBA business for at least 1,5 decade, but today I got an unusual error:  the wizard for Performance Datawarehouse db couldn’t create a new database for a new SQL Server instance setup (the Performance Datawarehouse database deserves a blog post on it’s own so I don’t describe that any further right now).

This was a newly created instance and I’m the only user so it didn’t take long to realize who the offender was – myself :). I was about to write a script to move tempdb to the designated disk and was writing that script, but had before that extended the model db autogrowth to 10% instead of the default 1 MB with the SSMS tool, so it was the model db that was in focus when I pushed the new script button. Therefore I had a shared lock on model db when, trying to be effective (didn’t work as well as I expected), and simultaneously starting the wizard that creates the Performance DW db, while finsishing up the add tempdb files script.  Apparently the create database process is using an exclusive lock on the model database. That was new to me. Not a biggy to solve: I shifted the scripts focus to master db (where I eventually was going to execute the alter tempdb script when it was deployable) and then the Performance DW database wizard succeeded and the db could be created.  

I’m still not entirely sure why it would need an exclusive lock on model db to create a new db, even if the model db is used as a template, but if I stumble on that information I will share it with you. Jonas Bergström