Posts

MSSQL Find WHO Deleted_Created Table

 use Test01 GO --select * from fn_dblog(null,null) --Who Created Table select [Begin Time],[Transaction Name],[Transaction SID] from  fn_dblog(null,null) where [Transaction Name] = 'CREATE TABLE' --who Dropped Table select [Begin Time],[Transaction Name],[Transaction SID] from  fn_dblog(null,null) where [Transaction Name] = 'DROPOBJ' --who inserted Data select [Begin Time],[Transaction Name],[Transaction SID] from  fn_dblog(null,null) where [Transaction Name] = 'INSERT' --who Deleted Data select [Begin Time],[Transaction Name],[Transaction SID] from  fn_dblog(null,null) where [Transaction Name] = 'DELETE' --who Updated Data select [Begin Time],[Transaction Name],[Transaction SID] from  fn_dblog(null,null) where [Transaction Name] = 'UPDAT Once got [Transaction SID] Now RUN following Query: use master GO select SUSER_SNAME(Transaction SID) Example: use master GO select SUSER_SNAME(0x0105000000000005150000007CEB240DDA16EB2307E53B2BF0BC0100)
Post a Comment
Read more

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...
Post a Comment
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...
Post a Comment
Read more

SQL - Backup for all databases

 --------------------------------------------------- Backup for all databases in single script ---------------------------------------------------- DECLARE @name VARCHAR(50) -- database name   DECLARE @path VARCHAR(256) -- path for backup files   DECLARE @fileName VARCHAR(256) -- filename for backup   DECLARE @fileDate VARCHAR(20) -- used for file name  SET @path = 'G:\test\'   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  DECLARE db_cursor CURSOR FOR   SELECT name  FROM master.dbo.sysdatabases  WHERE name NOT IN ('master','model','msdb','tempdb')   OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @name    WHILE @@FETCH_STATUS = 0    BEGIN           SET @fileName = @path + @name + '_' + @fileDate + '.BAK'          BACKUP DATABASE @name TO DISK = @fileName          FETCH NEXT F...
Post a Comment
Read more

AlwaysON Setup

  Links: Distributed Availability Groups in SQL Server | by Alpay Kurbaloğlu | Trendyol Tech | Medium
Post a Comment
Read more

Distributed AG setup with 4 Nodes

DAG SETUP: Below links very useful to configure DAG setup. Distributed Availability Groups: Availability groupception! - SQL Stijn (sql-stijn.com) https://www.mssqltips.com/sqlservertip/6987/distributed-availability-groups-for-sql-server-disaster-recovery/ https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/database-engine/availability-groups/windows/distributed-availability-groups.md Issues: ALTER  AVAILABILITY  GROUP  [AGNAME]  GRANT  CREATE  ANY  DATABASE we should enable the permission before configuring the Always on setup.  If you are facing any issue with secondary cluster for dropping the database need to remove the DAG setup. Then remove the replica from secondary cluster. Datafile and Log file folder should be same in all four nodes Alter availibility group  group_name  REMOVE DATABASE  availability_database_name
Post a Comment
Read more

DBA Daily Commands in Handy

Image
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 proc ess 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%'
Post a Comment
Read more