Sql Server Materials

  Dead lock graph query DECLARE @xelfilepath NVARCHAR(260) SELECT @xelfilepath = dosdlc.path FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc; SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'  DROP TABLE IF EXISTS  #TempTable  SELECT CONVERT(XML, event_data) AS EventData         INTO #TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)          WHERE object_name = 'xml_deadlock_report' SELECT EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime,              CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,        EventData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime,              EventData.query('event/data/value/deadlock') AS XmlDeadlockReport      FROM #TempTable      ORDER...

           CREATE PROC [dbo].[sp_longrunningjobs]    (    @hours INT   )      AS   /***********************************************************     DESCRIPTION:    Shows all SQLAgent jobs that have been running longer than the amount of hours specified in   the parameter.     ************************************************************/      DECLARE @report_query VARCHAR(8000);   DECLARE @string VARCHAR(100);   DECLARE @xml NVARCHAR(MAX);   DECLARE @body NVARCHAR(MAX);   DECLARE @currently_running_jobs TABLE (    job_id UNIQUEIDENTIFIER NOT NULL    ,last_run_date INT NOT NULL    ,last_run_time INT NOT NULL    ,next_run_date INT NOT NULL    ,next_run_time INT NOT NULL    ,next_run_schedule_id ...

 One of the most frequent issues DBAs face during SQL Server patching is when the SQL Server service fails to start after applying a patch.  This can be caused by various factors, such as corrupted system databases, incompatible configuration changes, or issues with disk space or permissions. 𝗧𝗵𝗲 𝗜𝘀𝘀𝘂𝗲: After applying Cumulative Updates or Service Packs, many DBAs experience failures where the SQL Server service doesn’t restart or the instance won’t come online.  The root cause could range from corrupted system databases (like master, msdb, or model) to configuration conflicts or permission issues. 𝗖𝗼𝗺𝗺𝗼𝗻 𝗖𝗮𝘂𝘀𝗲𝘀: Corrupted system databases (master, msdb) Incompatible configuration or parameter changes Missing or incorrect permissions for SQL Server service account Disk space issues or problems with SQL Server file access Registry or dependency issues affecting the service 𝗥𝗲𝘀𝗼𝗹𝘂𝘁𝗶𝗼𝗻 𝗦𝘁𝗲𝗽𝘀: 𝗖𝗵𝗲𝗰𝗸 𝗘𝗿𝗿𝗼𝗿 𝗟𝗼𝗴𝘀: Review SQL Serve...

 Below query  will get the table FK reference table list.  DECLARE @InputTableName NVARCHAR(128) = 'company_master';  SELECT DISTINCT      OBJECT_NAME(fk.referenced_object_id) AS referenced_table FROM sys.foreign_keys fk WHERE OBJECT_NAME(fk.parent_object_id) = @InputTableName   AND OBJECT_NAME(fk.referenced_object_id) IS NOT NULL ORDER BY referenced_table;    

 SELECT     db.name AS DatabaseName,     r.command,     r.status,     r.percent_complete,     r.start_time,     r.estimated_completion_time / 1000 / 60 AS EstimatedCompletion_Minutes,     r.total_elapsed_time / 1000 / 60 AS ElapsedTime_Minutes FROM      sys.dm_exec_requests r JOIN      sys.databases db ON r.database_id = db.database_id WHERE      r.command IN ('BACKUP DATABASE', 'BACKUP LOG'); There  are other  ways also. /*================================================================== Script: Monitor Backup Restore Dbcc.sql Description: This script will display estimated completion times and ETAs of Backup, Restore and DBCC operations. Date created: 13.09.2018 (Dominic Wirth) Last change: - Script Version: 1.0 SQL Version: SQL Server 2008 or higher ====================================================================*/ SELECT Req.percent_complete AS Per...

  As a SQL Server DBA, you’ll hear: “The app is slow!” or “Query took 2 seconds, now 2 minutes!” 😬 Before diving in, follow this structured troubleshooting approach—to not just fix, but understand and prevent it. 🔍 Step 1: Immediate Triage: Global or Specific? ✅ Check if slowness is server wide or a few queries Find top resource consumers: SELECT TOP 10 total_elapsed_time/1000 AS ElapsedMS, execution_count, total_logical_reads, (SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS Query FROM sys.dm_exec_query_stats ORDER BY total_elapsed_time DESC; 📈 Step 2: Live Monitoring: What’s Running Now? Use Activity Monitor, sp_whoisactive, or: SELECT * FROM sys.dm_exec_requests WHERE status = 'running'; Watch for: Blocking Wait types (sys.dm_os_waiting_tasks) TempDB usage CPU pressure ⏳ Step 3: Wait Stats: The Bottleneck Clues Get current waits: SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC; Patterns to note: PAGEIOLATCH_* = Disk I/O CXPACKE...