Export (0) Print
Expand All
Expand Minimize

sys.database_connection_stats

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see sys.database_connection_stats.

Contains statistics for Azure SQL Database database connectivity events, providing an overview of database connection successes and failures. For more information about connectivity events, see Event Types in sys.event_log.

The sys.database_connection_stats view contains the following columns:

 

Column Type Description

database_name

sysname

Name of the database.

start_time

datetime2

UTC date and time of the start of the aggregation interval. The time is always a multiple of 5 minutes. For example:

'2011-09-28 16:00:00'
'2011-09-28 16:05:00'
'2011-09-28 16:10:00'

end_time

datetime2

UTC date and time of the end of the aggregation interval. End_time is always exactly 5 minutes later than the corresponding start_time in the same row.

success_count

int

Number of successful connections.

total_failure_count

int

Total number of failed connections. This is the sum of connection_failure_count, terminated_connection_count, and throttled_connection_count, and does not include deadlock events.

connection_failure_count

int

Number of login failures.

terminated_connection_count

int

Number of terminated connections.

throttled_connection_count

int

Number of throttled connections.

Users with permission to access the master database have read-only access to this view.

The following example shows a query of sys.database_connection_stats to return a summary of the database connections that occurred between noon on 9/25/2011 and noon on 9/28/2011 (UTC). By default, the query results are sorted by start_time (ascending order).

select * from sys.database_connection_stats where start_time>='2011-09-25:12:00:00' and end_time<='2011-09-28 12:00:00'

Event Aggregation

Event information for this view is collected and aggregated within 5-minute intervals. The count columns represent the number of times a particular connectivity event occurred for a specific database within a given time interval.

For example, if a user fails to connect to database Database1 seven times between 11:00 and 11:05 on 2/5/2012 (UTC), this information is available in a single row in this view:

 

database_name start_time end_time success_count total_failure_count connection_failure_count terminated_connection_count throttled_connection_count

Database1

2012-02-05 11:00:00

2012-02-05 11:05:00

0

7

7

0

0

Interval start_time and end_time

An event is included in an aggregation interval when the event occurs on or afterstart_time and beforeend_time for that interval. For example, an event occurring exactly at 2012-10-30 19:25:00.0000000 would be included only in the second interval shown below:


start_time                    end_time
2012-10-30 19:20:00.0000000   2012-10-30 19:25:00.0000000
2012-10-30 19:25:00.0000000   2012-10-30 19:30:00.0000000

Data Updates

Data in this view is accumulated over time. Typically, the data is accumulated within an hour of the start of the aggregation interval, but it may take up to a maximum of 24 hours for all the data to appear in the view. During that time, the information within a single row may be updated periodically.

Data Retention

The data in this view is retained for a maximum of 30 days, or possibly less depending on the number of databases in the logical server and the number of unique events each database generates. To retain this information for a longer period, copy the data to a separate database. After you make an initial copy of the view, the rows in the view may be updated as data is accumulated. To keep your copy of the data up-to-date, periodically do a table scan of the rows to look for an increase in the event count of existing rows and to identify new rows (you can identify unique rows by using the start and end times), then update your copy of the data with these changes.

Errors Not Included

This view may not include all connection and error information:

  • This view does not include all Azure SQL Database database errors that could occur, only those specified in Event Types in sys.event_log.

  • If there is a computer failure within the Azure SQL Database datacenter, a small amount of data for your logical server may be missing from the event table.

  • If an IP address has been blocked through DoSGuard, connection attempt events from that IP address cannot be collected and will not appear in this view.

Community Additions

ADD
Show:
© 2014 Microsoft