ALTER AUTHORIZATION hangs and causes blocking

Question

Post reply

ALTER AUTHORIZATION hangs and causes blocking

webrunner

SSC-Dedicated

Points: 31863
More actions
December 26, 2012 at 7:41 pm

#266638

Hi,
I'm trying to change the owner of a database using a command like the following, but it doesn't complete and eventually causes blocking on the server. I also saw a deadlock while trying to update statistics on one of the tables in the databases. This didn't happen with any of the other databases on the server.
ALTER AUTHORIZATION ON DATABASE::MyDB TO sa
GO
It is a SQL Server 2008 R2 Enterprise Edition server.
Does anyone know what might cause these kinds of issues, or how I could track down the cause? When I re-ran the update statistics while checking for deadlocks with SQL Profiler, no deadlocks happened. But the change owner command still hangs.
Thanks in advance for any help.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQL Show SSCertifiable Points: 6143 More actions · Answer 1

Did you able to check what session/process is blocking alter statement by running sp_who2?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Check the sql error log too

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

webrunner SSC-Dedicated Points: 31863 More actions · Answer 3

SQL Show (12/26/2012)
Did you able to check what session/process is blocking alter statement by running sp_who2?

Thanks for your response.

Yes, it was a query from the application that uses the database, so I have asked that group to try restarting the application to see if that alleviates the immediate issue. But it's a third-party application so we are going to need to research it or contact the vendor to see what might be going on. I just migrated the database from SQL 2005 Standard Edition to SQL 2008 R2 Enterprise Edition.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31863 More actions · Answer 4

Bhuvnesh (12/27/2012)
Check the sql error log too

Thanks for your response.

I did check the error logs and didn't see anything associated with the blocking. But I tested running the index maintenance and update statistics job and it failed after hitting a deadlock on the server. I tried tracing to reproduce the deadlock but the second time no deadlock happened. Yet still the ALTER AUTHORIZATION command won't complete. As I stated in my previous reply, the issue appears to be with the application using the database, but we may also see if the restart of SQL Services after server patching today fixes it.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

In order to change the owner of a database you must have exclusive access to it. Restarting the SQL Server service will not help if the application immediately reconnects to the database, which will again block you from changing the owner. Try it like this:

USE [YourDatabase]

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- Note: this will forcefully disconnect all connections to the database other than your own so make sure everyone is on board with this action before executing it

ALTER AUTHORIZATION ON DATABASE::[YourDatabase] TO [sa];

ALTER DATABASE [YourDatabase] SET MULTI_USER;

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

webrunner SSC-Dedicated Points: 31863 More actions · Answer 6

opc.three (12/27/2012)
In order to change the owner of a database you must have exclusive access to it. Restarting the SQL Server service will not help if the application immediately reconnects to the database, which will again block you from changing the owner. Try it like this:
USE [YourDatabase]
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- Note: this will forcefully disconnect all connections to the database other than your own so make sure everyone is on board with this action before executing it
ALTER AUTHORIZATION ON DATABASE::[YourDatabase] TO [sa];
ALTER DATABASE [YourDatabase] SET MULTI_USER;
GO

OK, great, thanks. So I guess the reason this was the only database had an issue may be that because we migrated it first and had already connected the application to it? I didn't see this issue with the other databases, but don't have a record of exactly what was connected at the time. I will try to test this out. We do have an approved time when we can restart the application but I can also try this statement at that time to confirm that it works.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html