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

Directions Training’s Microsoft SQL Server training experts wanted to share this helpful “Top 10” Microsoft SQL Server tip sheet.

The top 10 T-SQL statements that are useful for Microsoft SQL Server database administrators (DBA) 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, Service Pack and version number from current SQL Server connection. ‘Edition’ will give information on a 32 bit or64 bit architecture and ‘Product level’ 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

Directions Training Microsoft SQL Server Figure 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

Directions Training Microsoft SQL Server Figure 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

Directions Training Microsoft SQL Server Figure 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 globally on the server. Refer Fig 1.4

Directions Training Microsoft SQL Server Figure 1.4

 

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

Directions Training Microsoft SQL Server Figure 1.4b

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

Directions Training Microsoft SQL Server Figure 1.5

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

Directions Training Microsoft SQL Server Figure 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. Refer Fig 1.7

Directions Training Microsoft SQL Server Figure 1.7

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

Directions Training Microsoft SQL Server Figure 1.8

T-SQL Statement 7

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

Directions Training Microsoft SQL Server Figure 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

Directions Training Microsoft SQL Server Figure 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

Directions Training Microsoft SQL Server Figure 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.

Directions Training Microsoft SQL Server Figure 1.12

For more Microsoft SQL Server training course information visit Directions Training or call toll-free 1-855-575-8900.

Directions Twitter
Directions RSS
Directions LinkedIn
Directions Facebook
Directions Training

 

Classroom IT Training & IT Certification Courses

Directions Training offers classroom environment IT training and IT Certification courses across the United States. Click on one of the links below for more information about a specific location.
Birmingham Alabama IT Training | Tempe Arizona IT Training | Glendale California IT Training | San Francisco California IT Training | Santa Ana California IT Training | Santa Clara California IT Training | Washington District of Columbia IT Training | Ft. Lauderdale Florida IT Training | Marietta Georgia IT Training | Addison Illinois IT Training | Chicago Illinois – LaSalle Street IT Training IT Training | Chicago Illinois – Michigan Ave IT Training IT Training | Chicago Illinois – Wacker Drive IT Training | Oak Brook Illinois IT Training | Rosemont Illinois IT Training | Springfield Illinois IT Training | Indianapolis Indiana IT Training | Baltimore Maryland IT Training | Columbia Maryland – Bridge IT Training | Columbia Maryland – Riverwood IT Training | Columbia Maryland – Think Big IT Training | Havre de Grace Maryland IT Training | Hunt Valley Maryland IT Training | Boston Massachusetts IT Training | Chelmsford Massachusetts IT Training | Woburn Massachusetts IT Training | Eagan Minnesota IT Training | St. Charles Missouri IT Training | Iselin New Jersey IT Training | Monmouth Junction New Jersey IT Training | Raleigh North Carolina IT Training | Brentwood Tennessee IT Training | Memphis Tennessee IT Training | Vienna Virginia IT Training | Bellevue Washington IT Training | Seattle Washington IT Training