Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Friday, March 15, 2019 6:51 AM
Hello,
We have recently moved to Azure SQL Managed instance, and we have been having some performance issues with it. Due to poor performance, the application stopped running.
We could neither take the database offline or set it to a single user mode. This caused us a lot of time. It would be great if there was a feature to take the DB offline, which drops all the sessions and quickly bring it back again, similar to on premise environment.
Please advise if there are alternatives to this
Thanks
Aloysius
All replies (10)
Friday, March 15, 2019 8:46 AM
Hi,
Thanks for your feedback. Database can be set to single user mode. You can refer below document for the same.
Set a Database to Single-user Mode
Hope it helps.
Friday, March 15, 2019 9:03 AM
Hi Angoyal,
This MS docs says that single_user and Offline cannot be changed does that still means you can put database in single_user. Also the link you shared in your answer above applies *only* to SQl Server and not to anything in Azure. Can you please confirm this.
Mahesh it would be better if you add error you get while putting in single_user mode
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Friday, March 15, 2019 9:05 AM
If our aim is drop all sessions on database, just run below script on database which db session need to drop\kill instead of put the DB into offline or single user mode.
This script to designed to kill all connections for all users to a specified database.
Declare @tblConnectedUsers Table (SPID int )
Declare @vcSQLText varchar(200),
@iSPID int
--Get the currently connected users
Insert into @tblConnectedUsers
Select p.spid
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'EBN_QA' --> database name here
--Loop though the connected users and kill their connections
While 1 = 1
Begin
Select top 1 @iSPID = SPID
From @tblConnectedUsers
Where SPID > IsNull(@iSPID, 0)
order by SPID asc
-- break when there are no more SPIDs
If @@RowCount = 0
Break
--Build the SQL string
Set @vcSQLText = 'Kill ' + Convert(varchar(10), @iSPID)
Exec( @vcSQLText )
End
===================================================================
Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.
Friday, March 15, 2019 9:19 AM
Hi, this article refers to SQL Server not Azure SQL Databases. Also, Alter statement is not supported here.
Friday, March 15, 2019 9:20 AM
Hi Shashank,
Correct, if i try to run an alter statement for making it single user, i get the error "This ``ALTER DATABASE statement ``is not supported. "
Thanks,
Friday, March 15, 2019 9:30 AM
Hi Shashank,
Correct, if i try to run an alter statement for making it single user, i get the error "
This ``ALTERDATABASEstatement ``isnotsupported. "
This means it is not supported. I am not sure why Angoyal-msft said it can be done and this is why I have asked him for clarification. Let him come back and reply but AFAIK, with my limited knowledge this is as of now not possible.
My question is, what is that is causing you to take database in single user mode, what kind of issue you are seeing.
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Saturday, March 16, 2019 8:36 AM
Hello all,
We have recently migrated our On-prem SQL DBs to Azure SQL Managed instance. Ever since we have moved the DB's to Azure SQL, we have been having multiple performance issues. Last week, one of the queries in one of the DB's took way too long and thereby blocked all the other DB's. None of the applications connecting to other DB's were able to connect. We were stuck for almost 2 hours with none of the DB's responding.
Having know the issue with the problematic DB, we wanted to either kill all the sessions or take the DB offline. Killing all the sessions were a riskier approach, so wanted to see if we could atleast take the DB offline as it was not a critical DB as the others.
Also, the Query store in some of the DB's don't work and the product team seems to be working on it. There seems to be a bug in the migration.
I'm seriously having to have second thoughts about the decision to move to Azure SQL MI.
Please advice.
Thanks,
Sunday, March 17, 2019 11:38 PM
Hello all,
We have recently migrated our On-prem SQL DBs to Azure SQL Managed instance. Ever since we have moved the DB's to Azure SQL, we have been having multiple performance issues. Last week, one of the queries in one of the DB's took way too long and thereby blocked all the other DB's. None of the applications connecting to other DB's were able to connect. We were stuck for almost 2 hours with none of the DB's responding.
Having know the issue with the problematic DB, we wanted to either kill all the sessions or take the DB offline. Killing all the sessions were a riskier approach, so wanted to see if we could at least take the DB offline as it was not a critical DB as the others.
Also, the Query store in some of the DB's don't work and the product team seems to be working on it. There seems to be a bug in the migration.
I'm seriously having to have second thoughts about the decision to move to Azure SQL MI.
Please advice.
Thanks,
Good day Aloysius,
BEFORE ANYTHING ELSE!
Make sure that you have a full backup of the database(s)
>> I'm seriously having to have second thoughts about the decision to move to Azure SQL MI.
This sound like doing 1+1 and getting 3
I read your description and according to information you provided, I don't see how you came to the conclusion that a single mode has anything to do with your issue. I am getting the feeling (which might be wrong) that you used to restart the server too often for any unrelated problem.
>> Last week, one of the queries in one of the DB's took way too long and thereby blocked all the other DB's.
1. Did you confirm that this query block the queries in the other databases? Is this related to resources? please elaborate how you came to the conclusions
2. We might be able to help improve the query if you will provide us the tools to reproduce the execution: queries to create the relevant table(s) and insert some data + the query which you use.
>> take the DB offline as it was not a critical DB as the others.
Take the db offline to where ;-)
You do not manage the host of the server
SET OFFLINE is not supported in Azure SQL Database Managed Instance
>> Killing all the sessions
Can be a solution and usually should be less risky! Killing a session impacts single session mostly while SET OFFLINE impact all sessions. You just need to find the right session's id
>> Also, the Query store in some of the DB's don't work and the product team seems to be working on it. There seems to be a bug in the migration.
Since the product team already involve and since you think that there is a bug in the migration then it is best to continue working with the product's team and if needed open another support ticket. They have the option to examine your specific entities while in the forums we can only discuss in general
| Ronen Ariely [Personal Site] [Blog] [Facebook] [Linkedin] |
Wednesday, March 20, 2019 4:51 AM
Ronen its incredible that someone from Microsoft/product group gave some answer on forum and when asked for clarification he is not replying. The main question is can database on Azure MI be taken offline or in single user mode I do not think so to which MS person replied yes you can, may be he has misread the question. I would appreciate he comes here and give explanation to what he has written(or correct me if I am wrong) instead of proposing an answer. BTW I agree to your answer given and tried asking the same thing from OP
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Friday, March 22, 2019 8:22 AM
Ronen its incredible that someone from Microsoft/product group gave some answer on forum and when asked for clarification he is not replying. The main question is can database on Azure MI be taken offline or in single user mode I do not think so to which MS person replied yes you can, may be he has misread the question. I would appreciate he comes here and give explanation to what he has written(or correct me if I am wrong) instead of proposing an answer. BTW I agree to your answer given and tried asking the same thing from OP
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Hi,
>> The main question is can database on Azure MI be taken offline or in single user mode I do not think so to which MS person replied yes you can, may be he has misread the question. I would appreciate he comes here and give explanation to what he has written(or correct me if I am wrong) instead of proposing an answer.
you are right,
You cannot SET SINGLE_USER.
In fact this is well documented twice in the link I gave in my previous response, once MULTI_USER is the list of "options are set by default and can't be changed", and second time explicitly SINGLE_USER is in the list of "options can't be modified"
| Ronen Ariely [Personal Site] [Blog] [Facebook] [Linkedin] |