LyonsIT
asked on
Strange SQL Query Running as .NET SQLClient Data Provider Locking Model Database
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 rl.name 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?
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?
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.
You can also run the stored procedure sp_who2 to verify the loginname and hostname from the locking processes.
ASKER
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.
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.
I ran a full AV scan on 2 of the serversHave you excluded the data and log files from AV? It's recommended so you can avoid locks on the databases.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the update.
ASKER
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
Could it be a trigger that makes a change that triggers it back again?