Menu
I have a SQL Server 2008 R2 Enterprise database with a view on it called vw_Users.
-Running (Select * from vw_users) takes less than a second to complete.
-Running the SQL inside of the view takes less than a second to complete.
-Running (drop view vw_Users) just hangs and never actually completes. I let it run for about 10 minutes before I cancelled it.
I restarted the SQL Server Agent, then tried again, but it's still occurring.
This is a brand new issue, this server and this database have been running fine for over a year.
There are no indices on the view. I'm not sure what the problem is, but any help would be very appreciated.
Thanks
289
When SQL Server processes a SELECT from a view, it evaluates the code in the view BEFORE it deals with the WHERE clause or any join in the outer query. With more tables joined, it will be slow compared to a SELECT from base tables with the same results.
When you drop a view, the definition of the view and other information about the view is deleted from the system catalog. All permissions for the view are also deleted. Any view on a table that is dropped by using DROP TABLE must be dropped explicitly by using DROP VIEW.
The SQL DROP command may be one that asks for caution, since it removes data. However, dropping a View does not delete the source data.
The syntax for the SQL DROP VIEW Statement is: DROP VIEW view_name; view_name. The name of the view that you wish to drop.
Someone or something has an open connection accessing that view and you are being blocked.
You can check this by starting your DROP, then in another window in SSMS running:
sp_who2 active
You should see a row with your spid, and the blocked_by field will have another spid number in it. Find that spid to see what is blocking you.
If it can be safely terminated, either close the process manually or from within SSMS run:
kill x
...where x is the spid of the blocking process.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
