Strange SQL Query Running as .NET SQLClient Data Provider Locking Model Database

Posted on 2015-01-30
Last Modified: 2015-02-09
I'm having an issue creating new databases on several of our SQL servers (SQL Server 2008 R2 SP1). When I look at Activity Monitor there are several (anywhere from 2 to 9) different processes all running the same SQL statement (SELECT AS [Name] FROM sys.database_principals AS rl WHERE (rl.type = 'R') ORDER BY [Name] ASC) that is locking the Model database. When I attempt to create a new DB, I get Error: 1807, could not obtain an exclusive lock on the databse 'model'.

These show as coming from Applicaton .Net SqlClient Data Provider and from the hostname of the SQL server. They are all running as my windows domain account as well. I do not have any additional SQL Management Studio sessions open or running any processes against the DB that I know of. I did the initial install of SQL, but none of the service accounts run as my username.

I can manually kill the sessions that are locking, but they start right back up. Sometimes within seconds, and sometimes a few minutes. I can keep killing them off and eventually get a database created, but it's very stange these processes continue to run all day long. They run and drop off and then start up again in varying numers.

Has anyone seen this before, or know how to trace what is generating it?
Question by:LyonsIT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 40
ID: 40581485
Have you run a complete anti-virus scan on that system. Could be a bad one that is trying to get info about your server?

Could it be a trigger that makes a change that triggers it back again?
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40583511
First of all you should update your SQL Server instance to SP3 since is the last service pack and correct a lot of bug and we don't know if you are facing a known bug.

You can also run the stored procedure sp_who2 to verify the loginname and hostname from the locking processes.

Author Comment

ID: 40584367
We have roughly 10 SQL servers spread out between Production, Test, and Dev. This issue is happening on at least 4 of them, but not all.  

I ran a full AV scan on 2 of the servers it's happening on, and they both came back clean.

I will not be able to update to SP3 on any of the production servers right away, but I can update one of our Dev boxes sometime today and let you know if it makes a difference.

Running sp_who2 is showing the same username and hostname as originally stated, they are all coming from my own windows domain account, and show as running on the local SQL server.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40585661
I ran a full AV scan on 2 of the servers
Have you excluded the data and log files from AV? It's recommended so you can avoid locks on the databases.

Author Comment

ID: 40589701
LDF and MDF's were being exlcuded, I even completely unininstalled AV from one of the Dev boxes, as well as updated to SP3; no change. After digging around some more I noticed that this SQL process/command was running on all the databases on the server, not just the model db (it was just noticeable when trying to create a new database as this was when I got the SQL error stating the model was locked).

So next we completely removed my account from SQL and also made sure I wasn't left on any of the individual databases, processes kept showing up.

Finally, it occured to me to stop the backup agent..we use Dell AppAssure, and sure enough, as soon as the service stopped, all the SQL processes dropped of. The agent was not running as my username, but the credentials it uses to authenticate with SQL was using my account. As soon as I changed AppAssure to use sa, then sa showed up as locking the model DB. The strange thing was that these SQL statements happened all day long no matter if backups were running or not. I opened a case with Dell and will post there response in a another comment incase anyone else is curious.

Accepted Solution

LyonsIT earned 0 total points
ID: 40589706
What is taking place is the agent is set to gather the metadata which is then reported to the core and the info is then displayed in the console and also is directly related to the core displaying the SQL options such as attachability tests and log truncation requests. This process is always taking place when the agent service is running and is by design in order for the SQL server protection to report that this agent is indeed a SQL server in order for the nightly job tasks to be available and completed every night.

There is something that can be done to disable the metadata gathering process which will keep the agent from hitting the db's and causing the issues you are seeing when trying to create a new db or process an existing db via native SQL features but this will remove the ability for the agent and core to see this server as a SQL box and will be backed up like any other server protected that does not include Exchange or SQL DB's so you will not have the attachability and log truncation options available. If you are using SQL to keep track of the MDF / LDF and these db's are using Full recovery models then losing the agents metadata discovery / gathering process will not affect you and backups and restores would be the same as a protected file server or any other server without db's. I've included the steps below that are required to remove the metadata discovery / gathering process and this is to be implemented on the agent / sql server registry.

Open regedit
If it does not exist, please create it as a DWORD value

Please set it to a value of 1 which means that SQL metadata gathering is disabled.

Stop and Start the Appassure Agent service on the protected agent / sql server

On the core

Close and Open the console so that it can refresh and display the new agent configuration

Monitor to make sure that the same issues are not present with the DB's running on the SQL server once the steps above have been completed.
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40590496
Thanks for the update.

Author Closing Comment

ID: 40597972
I discovered the issue on my own and neither of the expert comments pointed me in the direction of what was the ultimate problme was

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question