Query never finishes when executing ALTER DATABASE sql

Question

Or at least, it hasn't finished as I'm writing this and after about 5 minutes.

Executing this SQL: "ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130, ANSI_NULLS ON, ANSI_PADDING ON, ANSI_WARNINGS ON, ARITHABORT ON, CONCAT_NULL_YIELDS_NULL ON"

works fine against an Azure SQL database that was provisioned about a year ago. I created a new database on a new server about a month ago, and from day one that same SQL would never work. Thinking the problem may have been because I used the same sa credentials from the previous server, I deleted the server and recreated it today. But the problem still exists.

Any thoughts?

Answer 1

Finally got this error from SSMS:

Msg 42008, Level 16, State 120, Line 1
ODBC error: State: 08S01: Error: 10054 Message:'[Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.
'.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Answer 2

Hello,

Please run the following statement to verify the operation is really still on progress:

SELECT * FROM sys.dm_ operation_status  ORDER BY start_time DESC;

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 3

My query shows up as state 2=COMPLETED. start_time was 2017-07-08 20:27:06.903 and last_modify_time was 2017-07-08 21:59:11.753.

Answer 4

Hello,

Then it finished. You can examine database related DMVs or Azure portal to examine database properties are as expected.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 5

I agree that it finally finished, but if I understand dm_operation_status correctly it looks like that query took about an hour and a half to execute. That doesn't seem right. And to add to the weirdness, I tried that query one more time last night from SSMS and canceled the query after about 5 minutes. This morning, I tried the query again, and it now executes instantly. And does dm_operation_status get purged? The only operations showing up in that table this morning are a CREATE DATABASE COPY and TERMINATE CONTINUOUS DATABASE COPY. CREATE DATABASE COPY went from 2017-07-08 19:37:18.770 to 2017-07-09 11:08:24.153 and TERMINATE CONTINUOUS COPY went from 2017-07-08 19:56:47.817 to 2017-07-09 10:57:06.040. I did copy the database yesterday, but it didn't take overight. And where did the query operations from yesterday go?

Answer 6

Yes it gets purged after 48 hours, I think.

Sometimes database-level operations do take a long time in Azure, they like to copy your database to do them, though I don't know why they would for a compatibility level change.

And what normally takes five to ten minutes can go for hours sometimes, no reason given.  Or just issue cryptic messages, whether or not the operation was successful.

Usually it clears up one way or the other and it just doesn't pay to inquire much more closely.

Josh

Answer 7

OK, thanks. I still wonder if my re-using the sa credentials was the initial problem and my re-provision with different sa credentials did actually fix the problem but I wasn't patient enough to allow the Azure behind-the-scenes magic to finish which is why it didn't actually start working until the following day.