Daily Commands for Database Administrators

As a database administrator (DBA), navigating the vast landscape of database management can be challenging and rewarding. It’s important to be able to execute the commands necessary to simplify day-to-day tasks and ensure that your SQL Server performs smoothly. In this guide, we’ll go through some everyday commands that every DBA should have in their toolkit.

1. Locate Database Files:

Use the SQL command below to locate database files starting with the letter ‘c’:

SELECT * FROM sysaltfiles WHERE filename LIKE 'c%'

2. Shrink a Full Log File:

Execute the following commands to shrink a full log file:

BACKUP LOG dbname WITH NO_LOG
DBCC SHRINKFILE(2) --(2), is specifically targeting a file with a file ID of 2.

3. Check Open Transactions:

Use the following command to check for open transactions:

DBCC OPENTRAN

4. Detect Blocked Process:

Identify blocked processes with the query below:

   SELECT * FROM sysprocesses WHERE blocked > 0 AND spid = 57

5. Buffer Cache Hit Ratio < 90:

Analyze potential performance issues using:

   DBCC SHOWFILESTATS

6. View Database Status:

Check the status of your database with:

   DBCC SHOWFILESTATS

7. Check CPU Usage (when buffer cache hit ratio < 90):

Monitor CPU usage with:

   SELECT * FROM sys.sysprocesses ORDER BY cpu ASC

8. Get Table Information:

Retrieve table information using:

   SELECT * FROM sysobjects WHERE name LIKE '%table_name%'

9. Retrieve Data from Linked Server:

Access data from a linked server with:

   SELECT * FROM LinkedServer.DBName.SchemaName.TableName

10. Find Service Pack Version:

-- Determine the service pack version with:
SELECT SERVERPROPERTY('PRODUCTLEVEL')

11. View Server Collation Setting:

--Check the server collation setting using:
SP_HELPSORT

12. Check Log Usage:

--Monitor log usage with:
SELECT * FROM sys.databases WHERE name LIKE '%log_reuse_wait_desc%'

13. See Connection Information with DMVs:

--Retrieve connection information using:
SELECT * FROM sys.dm_exec_sessions

14. View Transaction Isolation Level:

--Check transaction isolation level with:
DBCC USEROPTIONS

15. Change Database Compatibility Level (SQL 2016 to 2019):

--Execute the command to change the compatibility level:
EXEC sp_dbcmptlevel AdventureWorks, 150

16. Check Query Memory Usage:

--Monitor query memory usage with:
SELECT * FROM sys.dm_exec_query_memory_grants

17. Clear Buffer Cache Data:

--Clear buffer cache data using:
DBCC DROPCLEANBUFFERS

18. Monitor Tempdb Space Allocation:

--Keep an eye on tempdb space allocation with:
SELECT * FROM sys.dm_db_session_space_usage

19. Get Index Usage Stats:

--Retrieve index usage statistics with:
SELECT * FROM sys.dm_db_index_usage_stats ORDER BY object_id, index_id

20. List Schedulers and Runnable Tasks:

--View schedulers and tasks with:
SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255

21. Identify High CPU Queries:

--Identify high CPU queries using:
SELECT * FROM sys.dm_exec_query_stats

22. Check for Active Parallel Requests:

--Identify active parallel requests with:
SELECT * FROM sys.dm_exec_requests

23. View Lock Information:

--Check lock information with:
SELECT * FROM sys.dm_tran_locks

Additional Handy Commands:

Open Cluster Administrator:

   cluadmin.msc

sp_readerrorlog:

View the error log with:

   sp_readerrorlog

sp_helpdb:

View information about databases with:

   sp_helpdb

sp_helplogins ‘Loginname’:

Verify login permissions with:

   sp_helplogins 'Loginname'

sp_helpuser:

Verify users in the database with:

   sp_helpuser

xp_logininfo ‘Loginname’:

Verify Active Directory login group information with:

   xp_logininfo 'Loginname'

sp_who2:

View running processes on SQL Server with:

   sp_who2

sp_who2 active:

View details of active processes on SQL Server with:

   sp_who2 active

select * from sys.sysprocesses where blocked != 0:

View currently blocked processes with:

   select * from sys.sysprocesses where blocked != 0

DBCC INPUTBUFFER(spid):

View the current query being executed by a specific process with:

   DBCC INPUTBUFFER(spid)

DBCC SQLPERF(logspace):

View information about log space usage with:

   DBCC SQLPERF(logspace)

These commands, when used judiciously, can empower DBAs to efficiently manage and optimize SQL Server performance on a daily basis. Remember, each command serves a specific purpose, contributing to the overall health and robustness of your database system.