DBA Daily Commands in Handy




sp_who2 active: 


View details of active processes on SQL Server Engine


DBCC INPUTBUFFER(session id): 


View the current query being executed by a specific process



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_lock

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%'

Comments

Post a Comment

Popular posts from this blog

SQL Server – How to re-initialize just a single article in transaction replication

  SQL Server – How to re-initialize just a single article in transaction replication -- Upskill 2020 .  1. Turn off @allow_anonymous and @immediate_sync on the publication. use PUBLICATION_DB_NAME go EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'allow_anonymous', @value = 'false' GO   EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'immediate_sync', @value = 'false' GO The reason we must disable @immediate_sync is because every time you add a new article, and if @immediate_sync is  enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article. 2. Add new article. EXEC sp_addarticle @publication = 'Upskill2020_Test', @article = 'Employee', @source_object = 'Employee', @force_invalidate_snapshot = 1 3. Refresh the subscription EXEC sp_refreshsubscriptions @publication = 'Repl_RBD' GO 4. Check the current sna...
Read more

SQL Deadlock Report - Extended Events

  SQL Deadlock Report - Extended Events Event Creation create EVENT SESSION EE_Deadlock ON SERVER     ADD EVENT sqlserver.xml_deadlock_report     ADD TARGET package0.ring_buffer     (SET max_memory = 4096) WITH (MAX_DISPATCH_LATENCY = 30 SECONDS); create Procedure  DeadlockReport as Begin alter event session EE_Deadlock ON SERVER state=Stop; if not exists (select * from Archival.sys.objects where name like '%Deadlock_Data%') Begin Create TABLE Archival..Deadlock_Data (     Rowid  INT IDENTITY PRIMARY KEY,     event_data XML) End Truncate Table Archival..Deadlock_Data insert into Archival..Deadlock_Data(event_data) ( select cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'EE_Deadlock') AS Data CROSS APPLY TargetData.nodes...
Read more