[go: up one dir, main page]
Scribd
Upload
62 views4 pages

DAILY DBA SCRIPTS

Uploaded by

Pranab Knj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
62 views4 pages

DAILY DBA SCRIPTS

Uploaded by

Pranab Knj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

DAILY USAGE COMMANDS FOR DBA’S

Locate Database Files:

Use SQL command: SELECT * FROM sysaltfiles WHERE filename LIKE 'd%'

Shrink a Full Log File:

Commands: BACKUP LOG dbname WITH NO_LOG and DBCC SHRINKFILE(2)

Check Open Transactions:

Use: DBCC OPENTRAN

Detect Blocked Process:

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

Buffer Cache Hit Ratio < 90:

Explanation: Indicates potential performance issues and reduced cache


effectiveness due to bottlenecks.

View Database Status:

Command: DBCC SHOWFILESTATS

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

Query: SELECT * FROM sys.sysprocesses ORDER BY cpu ASC

Get Table Information:

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

Retrieve Data from Linked Server:

Query: SELECT * FROM LinkedServer.DBName.SchemaName.TableName

Find Service Pack Version:

Query: SELECT SERVERPROPERTY('PRODUCTLEVEL')


View Server Collation Setting:

Command: SP_HELPSORT

Check Log Usage:

Query: SELECT * FROM sys.databases WHERE name LIKE '%log_reuse_wait_desc%'

See Connection Information with DMVs:

Query: SELECT * FROM sys.dm_exec_sessions

View Transaction Isolation Level:

Command: DBCC USEROPTIONS

Change Database Compatibility Level (SQL 2016 to 2019):

Execute: EXEC sp_dbcmptlevel AdventureWorks, 150

Check Query Memory Usage:

Query: SELECT * FROM sys.dm_exec_query_memory_grants

Clear Buffer Cache Data:

Command: DBCC DROPCLEANBUFFERS

Monitor Tempdb Space Allocation:

Query: SELECT * FROM sys.dm_db_session_space_usage or


sys.dm_db_task_space_usage

Get Index Usage Stats:

Query: SELECT * FROM sys.dm_db_index_usage_stats ORDER BY object_id,


index_id

List Schedulers and Runnable Tasks:

Query: SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255

Identify High CPU Queries:

Query: SELECT * FROM sys.dm_exec_query_stats


Check for Active Parallel Requests:

Query: SELECT * FROM sys.dm_exec_requests

View Lock Information:

Query: SELECT * FROM sys.dm_tran_locks

Open Cluster Administrator

Command: cluadmin.msc

sp_readerrorlog:

Purpose: To view the error log.

sp_helpdb:

Purpose: To view information about databases.

sp_helplogins 'Loginname':

Purpose: To verify the permissions of a specific login.

sp_helpuser:

Purpose: To verify the users in the database.

xp_logininfo 'Loginname':

Purpose: To verify Active Directory login group information for a login.

sp_who2:

Purpose: To view the processes running on SQL Server.

sp_who2 active:

Purpose: To view details of processes running on SQL Server.

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

Purpose: To view processes that are currently blocked.


DBCC OPENTRAN:

Purpose: To get information about open transactions in the database.

DBCC INPUTBUFFER(spid):

Purpose: To view the current query being executed by a specific process (identified by its
SPID).

DBCC SQLPERF(logspace):

Purpose: To view information about the log space usage.

You might also like