I was struggling today with customer’s SharePoint environment. I tried to add PowerPivot to his already configured farm and it just didn’t work. I got error: “Could not obtain exclusive lock on database ‘model’. Retry the operation later.” from PowerPivot configuration tool when it tried to create PowerPivot Service Application and its database.
First I thought that it has to be me because I just recently did a health check for that server. Perhaps I still had some management studio open somewhere on my workstation… Checked that and it wasn’t me. Ok. Let’s check who’s the bad guy? I started to look for the issue on SQL Server.
I don’t ever remember the db-id’s for system databases so first looked from DMV sys.databases what is the id of the Model database (it seemed to be 3).
I ran: “exec sp_lock” and looked who had locks for model database. In my case it was session #82. After that I looked for “exec sp_who2” to look who is session number #82? It was “NT AUTHORITY\SYSTEM” coming from local system. It was sleeping and client information was “.Net SqlClient Data Provider”. After doing some research on which services on this machine are running as “local system”, I found that it was the “System Center management” – service that was idling on my “Model” database. I’m not an expert of System Center but if this is a normal scenario I wouldn’t recommend running system center on SQL Server machines.
I managed to install my PowerPivot by shutting down the “System Center management” service temporarily and disabling it. System Center notices that service has been disabled and activates it again and starts the service so I needed to refresh services list quite often to see when the service was restarted. After system center had restarted the service I shat that down and disabled the service. I did this quite a many times before I got successful for PowerPivot installer and after that I let the service to start and work normally.
Keeping this in mind it is not always good to have monitoring solutions in place. In this particular example I had changed the model database to be quite a big so duplicating pages from model-database took 40 seconds.
