March Madness – SQL Azure – sys.databases

Yesterday we talked about the user information you can get from SQL Azure. Today we are going to focus on the details you can gather about the databases for your SQL Azure instance. You can run the following code to view details about the databases for your SQL Azure server instance:

SELECT *
FROM sys.databases

What is this system view for?

This view will have one row for every database created on a SQL Azure server. It is essentially the same as the sys.databases system view in SQL 2012. I did notice a few things that were different:

• You can’t use all the ALTER DATABASE statements in SQL Azure that are available in the on-premise versions. So, things like compatibility mode are just not changeable. You can find a list of the acceptable actions here. That mean despite the table being defined as if you had the option to change things, you really don’t. ‘
• Since you can’t change things like collation, that means you are stuck with SQL_Latin1_General_CP1_CI_AS as your default setting unless you create your database with your desired collation right from the start.
• SQL Azure databases have fewer states defined (online, restoring, suspect, copying) than on-premise versions, and the state_desc field shows you that the states are all about database copies.
• The READ COMMITTED SNAPSHOT isolation level is your default isolation level. At this time you cannot change this.
• The log_reuse_wait column will always be zero, which equates to NOTHING. That’s right, in SQL Azure your logs will wait for nothing!
• My current SQL Azure sys.database view has more columns than what is documented, so it is likely that the MSDN entry needs to be updated. I can guess as to what target_recovery_time_is for, but it isn’t documented (yet). I suppose I should file a Connect item for that.

Why should you care?

The main reason listed in the MSDN entry for sys.databases is focused on database copies. They are suggesting that you would use sys.databases to monitor the progress of a database copy in progress along with the sys.dm_database_copies view on the master database of the destination server.

Personally I would use the sys.databases view to monitor my databases daily. Keeping track of the count is an obvious use, but I would also be looking for little things like a different create data that would indicate something happened and caused a databases to be dropped and recreated. Tomorrow I will show you how to get more details about your SQL Azure databases.