Azure SQL DB Managed Instance - sp_readmierrorlog

Reviewed by: Kun Cheng, Borko Novakovic, Arvind Shyamsundar, Mike Weiner

Azure SQL Database Managed Instance is a new offering that provides an instance-based SQL PaaS service in Azure. If you are not yet familiar with this new Azure SQL Database capability, you can start with the What is Managed Instance documentation topic. Since early private preview of Managed Instance (MI), SQLCAT has been working with early adopter customers to help them evaluate MI as a new platform for their applications, gather their feedback, and improve the offering for everyone. Azure SQL Database Managed Instance is now available in public preview.

SQL error log is available on MI

The primary goal of MI is close compatibility with the traditional SQL Server, to help facilitate migrations from on-premises environments to the Azure SQL Database PaaS service. In this article, we will discuss one MI capability that it shares with SQL Server, namely the ability to see the instance error log. For SQL Server DBAs, the error log is one of the first things to check when troubleshooting application issues, and that is still the case when using a managed service such as MI. The availability of the instance error log highlights the MI focus on compatibility with SQL Server; by comparison, in Azure SQL Database, diagnostics and error information are exposed in different ways, i.e. using the sys.event_log DMV and diagnostics logging.

When you connect to an MI instance, you can right away look at its error log, using either Log File Viewer in SSMS (latest SSMS is strongly recommended), or the sp_readerrorlog stored procedure. If you do that on an instance of SQL Server that has just started, you will typically see 100-200 lines of output, depending on server configuration, the number of databases, etc. But when you look at the MI error log, you may be surprised by the large volume of messages. For example, in the first minute after instance startup, more than 2500 messages are logged. While some of them are the familiar messages you would find in a SQL Server error log, many others might look a bit cryptic, and are not actionable from an end-user standpoint.

Why is all this information in the MI error log? This diagnostic data is needed for Microsoft engineers to manage the service and troubleshoot any problems efficiently. As an aside, the MI error log is so detailed, that a curious user with relatively advanced knowledge of SQL Server internals can glean many interesting details about internal workings of MI, even without full knowledge of the MI architecture.

An important note is that in current MI preview, error logs are not persisted across instance restarts and failovers. Therefore, when looking at older logs, gaps are possible.

A new way to look at the MI error log

But for most customers, the high volume of debug-level messages in the log just makes it harder to see the messages relevant to their applications/databases, which are lost in the noise, as it were. This is the reason why we wrote sp_readmierrorlog. This is a simple stored procedure that returns the contents of the instance error log, filtering out messages that are unlikely to be useful to an MI customer.

sp_readmierrorlog has the same familiar parameters and result set that sp_readerrorlog has. It can be created in the master database of each MI instance you use, making it easy to call the procedure from the context of any database on the instance. As an example, on a mostly idle MI instance, sp_readmierrorlog reduced a 150,000-line error log, generated over one day, to 760 lines. This is much more manageable, while still providing useful diagnostic information to MI customers. For customers requiring a more in-depth look into instance behavior and diagnostics, access to the unfiltered log is still available using the built-in sp_readerrorlog procedure.

The procedure is open source and is hosted on GitHub. The filtering logic used by the procedure can be easily customized to include/exclude specific messages from the result set. If you find that the procedure is filtering out too much or too little, we would welcome pull requests with changes that make the stored procedure better for all MI customers.