SQL Server: useful query for DBAs

Tweet

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 machine

The first query allow us to know the server status:

SELECT
  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

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.
It basically gives us a full view of the SQL Server configuration.

Security status

In 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 state

If we want to check the various database for compatibility level, recovery model and current status, we need to query the sys.databases table:

SELECT name,compatibility_level,recovery_model_desc,state_desc 
FROM sys.databases

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:

SELECT db.name,
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

At the same time, we might need to know where those backups are stored. To do so we need to query the backupmediafamily table:

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.