Daily command list for DBA usage purposes in SQL Server

Find the version of the SQL Server

select @@version

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5021126) - 14.0.3460.9 (X64)   Jan 25 2023 08:42:43   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22621: ) (Hypervisor) 

Find the service pack version and edition

select SERVERPROPERTY('PRODUCTLEVEL')

select SERVERPROPERTY('EDITION');

Check the open transaction in SQL Server:

DBCC OPENTRAN

Check the blocked process:

SELECT * FROM sysprocesses where blocked > 0 and spid = 23;

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

Check the lock information:

SELECT * from sys.dm_tran_locks

Check the active user and process running in SQL Server

sp_who2

sp_who2 active

Check the connection information from view in SQL Server:

SELECT * FROM sys.dm_exec_sessions;

Check the database files location, size, and max size in SQL Server

SELECT * FROM sysaltfiles

Check the collation Setting on the SQL Server

SP_HELPSORT

Check the log Usage on SQL Server databases

select * from sys.databases where name like '%log_reuse_wait_desc%'

Shrink the full log file in SQL Server

1. Backup the log file with No_LOG option
2. Use the DBCC_SHRINKFILE for shrink purpose
For more detail follow the link:

https://smarttechways.com/tag/shrink-transactional-log-file/

Clean the buffer cache in SQL Server

DBCC DROPCLEANBUFFERS

Check the index usage stats in SQL Server

SELECT * FROM sys.dm_db_index_usage_stats ORDER BY object_id;

Check the high CPU-consuming query in the SQL Server

SELECT * FROM sys.dm_exec_query_stats

Check the temp database space allocation in the SQL Server

select * from sys.dm_db_Session_space_usage

select * from sys.dm_db_task_space_usage

Check the transaction isolation level in SQL Server

DBCC USEROPTIONS

Change the database compatible level on SQL Server

exec sp_dbcmplevel adventureworks, 150