ALTER AUTHORIZATION hangs and causes blocking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » ALTER AUTHORIZATION hangs and causes blocking...

ALTER AUTHORIZATION hangs and causes blocking

Rate Topic

Display Mode

Topic Options

Author

Message

webrunner

Posted Wednesday, December 26, 2012 7:41 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,117, Visits: 2,211

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

-------------------
"The chemistry must be respected." - Walter White

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

Post #1400432

Posted Wednesday, December 26, 2012 11:11 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 303, Visits: 490

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

Post #1400467

Bhuvnesh

Posted Thursday, December 27, 2012 2:49 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Check the sql error log too

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1400521

webrunner

Posted Thursday, December 27, 2012 7:12 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,117, Visits: 2,211

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

-------------------
"The chemistry must be respected." - Walter White

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

Post #1400612

webrunner

Posted Thursday, December 27, 2012 7:15 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,117, Visits: 2,211

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

-------------------
"The chemistry must be respected." - Walter White

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

Post #1400616

opc.three

Posted Thursday, December 27, 2012 9:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 6,722, Visits: 11,766

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1400667

webrunner

Posted Thursday, December 27, 2012 9:17 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,117, Visits: 2,211

opc.three (12/27/2012)

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

-------------------
"The chemistry must be respected." - Walter White

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

Post #1400669

« Prev Topic | Next Topic »

Permissions