Top 10 Transact-SQL Statements a SQL Server DBA Should Know

Microsoft SQL Server is a feature rich database management system product. Database administrators cannot keep up with the enormous amount of T-SQL commands and their syntax to get information from SQL Server. Each and every feature supported by SQL Server has its own list of commands and one article cannot cover all of them.

This article lists the top 10 T-SQL statements that are useful for SQL Server database administrators. The T-SQL statements covered in this article are classified into four categories: Server level, Database Level, Backup level and Process level.

Server Level Transact-SQL Statements a SQL Server DBA should know

T-SQL Statement 1

The following T-SQL statement retrieves information such as Hostname, Current instance name, Edition, Server type, ServicePack and version number from current SQL Server connection. 'Edition' will give information on a 32 bit or 64 bit architecture and 'Productlevel' gives information about what service pack your SQL Server is on. It also displays if the current SQL Server is a clustered server. Refer Fig 1.1

SELECT 
          SERVERPROPERTY('MachineName') as Host,
          SERVERPROPERTY('InstanceName') as Instance,
          SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
          SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
          Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
      'STANDALONE' end as ServerType,
          @@VERSION as VersionNumber

Fig 1.1

T-SQL Statement 2

Server level configuration controls some of the features and performance of SQL Server. It is also important for a SQL Server DBA to know the server level configuration information. The following SQL Statement will give all of the information related to Server level configuration. Refer Fig 1.2

SELECT * from sys.configurations order by NAME

If you are using SQL Server 2000, you can execute the following command instead.

SP_CONFIGURE 'show advanced options',1
go
RECONFIGURE with OVERRIDE
go
SP_CONFIGURE
go

Fig 1.2

T-SQL Statement 3

Security is a very important aspect that a DBA should know about. It is also important to know which login has a sysadmin or security admin server level role. The following SQL Command will show information related to the security admin server role and system admin server role. Refer Fig 1.3

SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
  FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1

Fig 1.3

T-SQL Statement 4

Another important bit of information that you need to know as a DBA is all of the traces that are enabled. The following T-SQL statement will list all of the trace flags that are enabled gloabally on the server. Refer Fig 1.4

DBCC TRACESTATUS(-1);

The following T-SQL statement will list all the trace flags that are enabled on the current sql server connection. Refer Fig 1.4

DBCC TRACESTATUS();

Fig 1.4

Database Level Transact-SQL Statements a SQL Server DBA should know

T-SQL Statement 5

Getting Database level information is equally as important as Server level information. The following T-SQL statement gives information on the database names, their compatibility level and also the recovery model and their current status. The result from this T-SQL Statement will help you to determine if there is any compatibility level update necessary. When upgrading from an older version to new version, the compatibility level of the database may not be in the desired level. The following statement will help you to list all of the database names with compatibilty level. It also lists the online/offline status of the database as well as helping the DBA to see if any update to recovery model is necessary. Refer Fig 1.5

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

Fig 1.5

If you are using SQL Server 2000, you could execute the following T-SQL Statement. Refer Fig 1.6

SELECT name,cmptlevel,DATABASEPROPERTYEX(name,'Recovery')AS RecoveryModel, 
DATABASEPROPERTYEX(name,'Status') as Status FROM sysdatabases

Fig 1.6

T-SQL Statement 6

The next level of information related to database that is needed is the location of the database. The following T-SQL Statement provides the logical name and the physical location of the data/log files of all the databases available in the current SQL Server instance. Referg Fig 1.7

SELECT db_name(database_id) as DatabaseName,name,type_desc,physical_name FROM sys.master_files

Fig 1.7

If you are using SQL Server 2000, you could execute the following T-SQL Statement. Refer Fig 1.8

SELECT db_name(dbid) as DatabaseName,name,filename FROM master.dbo.sysaltfiles

Fig 1.8

T-SQL Statement 7

A database may contain filegroups other than the primary file group. The following T-SQL Statement gets executed in each database on the server and displays the file groups related results. Refer Fig 1.9

EXEC master.dbo.sp_MSforeachdb @command1 = 'USE [?] SELECT * FROM sys.filegroups'

Fig 1.9

Backup Level Transact-SQL Statements a SQL Server DBA should know

T-SQL Statement 8

Backup of a database is bread and butter for database administrators. The following T-SQL Statement lists all of the databases in the server and the last day the backup happened. This will help the database administrators to check the backup jobs and also to make sure backups are happening for all the databases. Refer Fig 1.10

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_finish_date
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

Fig 1.10

T-SQL Statement 9

The next level of information that is important for a SQL Server database administrator to know is the location of all the backup files. You don’t want the backups to go to the local drive or to an OS drive. The following T-SQL statement gets all the information related to the current backup location from the msdb database. Refer Fig 1.11

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

Fig 1.11

Process Level Transact-SQL Statements a SQL Server DBA should know

T-SQL Statement 10

Last but not least, is the information related to current processes and connection related information. From the beginning, SQL Server database administrators used sp_who and sp_who2 to check the current users, process and session information. These statements also provided information related to cpu, memory and blocking information related to the sessions. Refer Fig 1.12. Also, search the internet for sp_who3. You can find many articles related to sp_who3.

sp_who
sp_who2

Fig 1.12

Conclusion

As mentioned in the beginning of this article, Microsoft SQL Server is a feature rich database management system product. Each and every feature supported by SQL Server has its own list of commands. This article covered the Top 10 T-SQL statements that are useful for SQL Server DBAs.

» See All Articles by Columnist MAK