I have said this many time earlier at various forums and I can still say that – “with my current consulting work, I am learning at least one new thing every day.” This is one of the reason why I love my work! In this blog I am going to share an interesting fact which was shared with me by my customer – How to Restart SQL Azure database?
My customer contacted me some performance tuning related consultancy. While we were doing some serious work, someone said why don’t we restart SQL Service and all the issues with be resolved. I told them that restart might work but do you really want to take downtime to fix this performance issue? Restart is not a fix but a band-aid. Then I told example of Azure database which is a Platform as a Service (PaaS) offering of Microsoft SQL Server. In this you would get a database and a logical server to manager. This is a multi-tenant system where there is no option to restart the server. But my client said that it is possible do restart SQL in Azure Database!
I became curious and asked – How can we restart SQL Azure database and why would someone do that?
He told that once they had severe blocking in SQL Azure database and they were killing the processes by using KILL commands. They knew that it was an issue with application where the transactions were not getting closed properly and application team was working on it.
I became curious and asked – So, how did we restart SQL Azure database?
He informed me that there are two ways to restart SQL Azure database. The right way to do that is by changing the pricing tier of the database. Basically, scale the database to another tier and then scale it back!
Other option which they told me was kind of unusual. They told that restart can also be performed by running below command
DBCC STACKDUMP('Manual Failover – Reason: something')I became curious about this command and searched more and found here: How can I create a dump of SQL Server?. When I ran the same command in my local SQL Server, it generated a dump and ERRORLOG shows below.
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
*****************************************************************************
* BEGIN STACK DUMP:
* 12/21/17 13:18:50 spid 24
* StackDump (all) : Manual Failover – Reason: something
But there was NO restart of SQL Service on my lab server. At this point I had no idea whether this would work in Azure.
CONCLUSION
When I asked with my friends who know Azure better than me and they told that none of above the methods would actually restart SQL Server service in Azure, but it would perform failover of database to another server which is hosting copy of the database. The end effect would be that the connection would now go to new server and would give you a feeling of restart.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Happy New Year Pinal !!
Wish you the same.
Good article. I had the exact same scenario yesterday.
Thanks for this article.
We were having a ton of wait timeouts on our Azure database, and found this post—THANK YOU! I ran the DDBC STACKDUMP command you gave, and it appears that it activated a “health event” in the log (for us it appeared to be “transient login failures”), and some self-recovery actions. So while it may not have triggered a “restart”, I feel like it DID trigger some corrective actions.
Just as a warning – I tried the DBCC STACKDUMP command on an Azure SQL database that somehow was not responding properly (VERY slow but nothing really running on it), resulting in the database being inaccessible on the server for 1 hour. Might have been something very wrong in my specific case.