Back in April I posted a query that shows instance information.  This post is the next level down in the hierarchy that I collect on my database platforms each week…  The databases in an instance.

This query is certainly one of the more straight forward queries to capture database data.  It probably doesn’t really need to be executed / collected weekly, but I do it anyway to be consistent with my collection process.

If you plug this query into a SQL Server Central Management server you’ll have a snapshot of all your instances that are part of the CMS.

This query will return the database name, the overall size of the database including all data and log files, the create date, owner, compatibility level, online vs offline, and update stats info.

It’s interesting to find databases owned by staff that are no longer around, compatibility levels to old versions for no obvious reason, and create and update stats that are turned off.

Like they say on the History Channel’s Pawn Star show… you never know WHAT will be coming through that door.

So here’s the query:

SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , d.database_id
        , d.[name]
        , CAST(SUM(mf.size * 8096.0 / 1000000000) AS NUMERIC(18, 2)) AS [db_size (G)]
        , d.recovery_model_desc AS [Recovery Model]
        , d.create_date
        , suser_sname(d.owner_sid) AS [Owner]
        , d.[compatibility_level]
        , d.state_desc AS [State]
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on
FROM        sys.databases d
        INNER JOIN sys.master_files mf
            ON    mf.database_id = d.database_id
WHERE    d.database_id > 4    — Exclude the system databases.
GROUP BY d.database_id
        , d.[name]
        , d.recovery_model_desc
        , d.create_date
        , d.owner_sid
        , d.[compatibility_level]
        , d.state_desc
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on

My next post in this series will be on database backups, which is a much more interesting query AND really does need to be monitored weekly, minimum.

Stay curious… keep learning…