Export (0) Print
Expand All
2 out of 4 rated this helpful - Rate this topic

Troubleshooting Windows Azure SQL Database

This document contains information about troubleshooting issues that pertain specifically to Microsoft Windows Azure SQL Database. 

SQL Server Connectivity Event Tables

To assist in tracking and troubleshooting database activity, Windows Azure SQL Database provides a set of dynamic management views (DMV) that record database connectivity-related events, including:

  • successful connections

  • failed connections

  • terminated connections

  • throttling

  • deadlocks

The events are collected and aggregated in two catalog views that reside in the logical master database: sys.database_connection_stats and sys.event_log. You can use these views together to troubleshoot database activity. For example, you can use the sys.database_connection_stats view to identify the time intervals in which errors occur, then use the sys.event_log view to display the details for those errors.

sys.database_connection_stats

This view provides a summary of database connection successes and failures. The columns contained in this view, and the events it collects, are described in sys.database_connection_stats.

For example, the following query displays all the connection statistics for the database "my_user_db":

select * from sys.database_connection_stats
where database_name = 'my_user_db'
order by start_time desc

The following example query produces the output shown (not all columns are displayed):


start_time                  end_time                    success_count total_failure_count connection_failure_count
 --------------------------- --------------------------- ------------- ------------------- ------------------------
 2012-09-27 14:20:00.0000000 2012-09-27 14:25:00.0000000 20            0                   0 
 2012-09-27 14:25:00.0000000 2012-09-27 14:30:00.0000000 2             0                   0 
 2012-09-27 14:30:00.0000000 2012-09-27 14:35:00.0000000 7             0                   0 
 2012-09-27 14:35:00.0000000 2012-09-27 14:40:00.0000000 14            0                   0 
 2012-09-27 14:40:00.0000000 2012-09-27 14:45:00.0000000 26            120                 120 
 2012-09-27 14:50:00.0000000 2012-09-27 14:55:00.0000000 9             0                   0 


sys.event_log

This view contains the details of connectivity-related events. The columns contained in this view, and the types of events it collects, are described in sys.event_log.

As an example, the following query displays all the unsuccessful connections for the database "my_user_db":

select * from sys.event_log 
where database_name ='my_user_db'
and event_type <> 'connection_successful'
order by start_time desc

This query produces the following output (not all columns are displayed):


event_type              event_subtype_desc       event_count description                                            
----------------------- ------------------------ ----------- -------------------------------------------------------
connection_failed       client_close             1           Client may have timed out when establishing connection.
connection_failed       client_close             1           Client may have timed out when establishing connection.
connection_terminated   idle_connection_timeout  1           Connection has been idle for longer than system defined
connection_terminated   idle_connection_timeout  1           Connection has been idle for longer than system defined
connection_failed       login_failed_for_user    90          Login failed for user.                                 
connection_terminated   idle_connection_timeout  1           Connection has been idle for longer than system defined
connection_failed       login_failed_for_user    110         Login failed for user.                                 
connection_failed       client_close             125         Client may have timed out when establishing connection.
connection_terminated   idle_connection_timeout  1           Connection has been idle for longer than system defined
connection_terminated   idle_connection_timeout  2           Connection has been idle for longer than system defined


The following example query displays all the deadlock instances for the same database (again, not all columns are shown in the example output):


select * from sys.event_log 
where database_name = 'my_user_db'
and event_type='deadlock'
order by start_time desc

event_category  event_type              event_subtype_desc       event_count description        
--------------- ----------------------- ------------------------ ----------- ------------------
engine          deadlock                deadlock                 1           Deadlock occurred. 
engine          deadlock                deadlock                 1           Deadlock occurred.


Running this query in grid mode will give you the deadlock graph in the additional_data columns.

In the following example, the query finds any throttling events that have occurred on the database:


select * from sys.event_log 
where database_name = 'my_user_db'
and event_type like 'throttling%'
order by start_time desc

event_type                   event_count description
---------------------------- ----------- ----------------
throttling_long_transaction  2           The session has been terminated because of excessive TEMPDB usage. Try modifying your query to reduce the temporary table space usage.


Login Failure

Windows Azure SQL Database requires the use of SQL Authentication. If you cannot successfully log in, either your credentials are not valid or the database you requested is not available.

If you need to recover login names or passwords, contact your service administrator, who can grant you proper access to the server and database. Service administrators can also reset their own passwords using the Windows Azure Platform Management Portal.

Service Unavailable or Does Not Exist

If the server is already provisioned and the Windows Azure SQL Database service is available (you can check this using the Azure Health Status page), the likely cause is configuration issues in your on-site installation. For instance, you may be unable to resolve the name (which can be tested with tools such as tracert), you may have a firewall blocking port 1433 that is used by SQL Database, or you may be using a proxy server that is not configured properly. Use the same techniques to troubleshoot these difficulties that you would for SQL Server.

noteNote
To see the status of all Windows Azure platform services, use the Azure Health Status page. The status page contains current service status information and historical service data. To connect to the status page and check Windows Azure SQL Database service availability in real time, click the Service Dashboard button on the Windows Azure Support page or click Azure Health Status to go directly to the status page.

General Network Errors

You may receive general network errors because Windows Azure SQL Database might disconnect users in these circumstances:

  • When a connection is idle for an extended period of time.

  • When a connection consumes an excessive amount of resources or holds onto a transaction for an extended period of time.

  • If the server is too busy.

A tabular data stream (TDS) error token is sent prior to disconnecting users, when possible. To improve application experience, we recommend that you implement the retry logic in your SQL Database applications to catch these errors. When an error occurs, re-establish the connection, and then re-execute the failed commands or the query.

noteNote
Maximum allowable durations are subject to change depending on the resource usage. A logged-in session that has been idle for 30 minutes will be terminated automatically.

For more information on how to handle the connection-loss errors, see Connection Management in Windows Azure SQL Database article in the TechNet Wiki. This article lists the reasons for connection-losses and provides guidelines and best coding practices on how to manage the connection life cycle in SQL Database.

Query Optimization Performance

To improve performance in SQL Database, use the same techniques you would use with SQL Server. For more information, see Troubleshooting Queries in SQL Server Books Online.

SQL Server Errors

Windows Azure SQL Database uses a subset of SQL Server error messages. For more information about SQL Server errors, see Errors and Events Reference (Database Engine) in SQL Server Books Online.

See Also

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft. All rights reserved.