If you are a Database Administrator and you work with MS SQL Server, there are some useful queries you could use to check how things are going with your databases. Being always aware of what's going on is very important, and in some cases a good control panel with appropriate queries can be developed just to quickly display the databases status.
We are going to explore some interesting administration queries. Just select those that you might be interested in and add them to your personal control panel.
Check your machineThe first query allow us to know the server status:
With the above query, we get the Server Name, the Instance, the Edition, Service Pack version, the Server Type (clustered or standalone) and the Version.
SERVERPROPERTY('MachineName') as Server_Name,
SERVERPROPERTY('InstanceName') as Instance,
SERVERPROPERTY('Edition') as Edition,
SERVERPROPERTY('ProductLevel') as ServicePack,
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STANDALONE' end as Server_Type,
@@VERSION as Version
It basically gives us a full view of the SQL Server configuration.
Security statusIn order to get the Database security status we can use the following query:
SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1
Compatibility, recovery and stateIf we want to check the various database for compatibility level, recovery model and current status, we need to query the sys.databases table:
Last backup date and its physical path
Backups are an important element for DBAs. If we would like to know when the last backup was performed, we can use the following query:
At the same time, we might need to know where those backups are stored. To do so we need to query the backupmediafamily table:
case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),
MAX(b.backup_finish_date)) end AS last_backup
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name
AND b.type = 'D'
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC
SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily
I believe that a good control panel can be easily developed using the above queries. We can create a simple ASP page, and place it in the Database Administration menu. That way, we could have a quick overview and keep things under good control.