SQLDBA- SQL Server Wait Events

 

To solve slowness of SQL Server database, you should find which wait events exists in the database.

You can find wait events of database with following query.

with waits as
(select
wait_type,
wait_time_ms / 1000.0 as waits,
(wait_time_ms - signal_wait_time_ms) / 1000.0 as resources,
signal_wait_time_ms / 1000.0 as signals,
waiting_tasks_count as waitcount,
100.0 * wait_time_ms / sum (COALESCE (wait_time_ms,1)) over() as percentage,
row_number() over(order by wait_time_ms desc) as rownum
from sys.dm_os_wait_stats
where wait_type not in (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
select
w1.wait_type as waittype, 
cast (w1.waits as decimal(14, 2)) wait_s,
cast (w1.resources as decimal(14, 2)) resource_s,
cast (w1.signals as decimal(14, 2)) signal_s,
w1.waitcount wait_count,
cast (w1.percentage as decimal(4, 2)) percentage,
cast ((w1.waits / w1.waitcount) as decimal (14, 4)) avgWait_s,
cast ((w1.resources / w1.waitcount) as decimal (14, 4)) avgResource_s,
cast ((w1.signals / w1.waitcount) as decimal (14, 4)) avgSignal_s
from waits as w1
inner join waits as w2 on w2.rownum <= w1.rownum
group by w1.rownum, w1.wait_type, w1.waits, w1.resources, w1.signals, w1.waitcount, w1.percentage
having sum (w2.percentage) - w1.percentage < 95; -- percentage threshold

You can get list and status of currently waiting tasks with following script.

select wt.session_id, wt.exec_context_id, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_address, wt.resource_description, s.program_name, st.text, sp.query_plan, s.cpu_time cpu_time_ms, s.memory_usage*8 memory_usage_kb
from sys.dm_os_waiting_tasks wt
join sys.dm_exec_sessions s on s.session_id=wt.session_id
join sys.dm_exec_requests r on r.session_id=s.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_query_plan(r.plan_handle) sp
where s.is_user_process=1
order by wt.session_id, wt.exec_context_id

You can find wait events of database with following query.

SELECT getdate() as 'Run_Time' --script running time
    , wait_type --wait type
    ,waiting_tasks_count
, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamaný
, CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye oraný
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR',
      'BROKER_TRANSMITTER','CHECKPOINT_QUEUE','CHKPT,CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP',
      'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
      'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK',
      'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE','WAIT_FOR_RESULTS','WAITFOR_TASKSHUTDOWN',
       'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR')
ORDER BY 4 DESC

SQLDBA- Generate the ALTER script for set compatibility for all databases

 

-- Alter  script set  compatibility y to sql server  2022

DECLARE @DBName SYSNAME;

DECLARE @SQL NVARCHAR(MAX);

-- Table variable to hold database names

DECLARE @DBList TABLE (DBName SYSNAME);

-- Insert all user databases except system ones

INSERT INTO @DBList (DBName)

SELECT name FROM sys.databases

WHERE database_id > 4 -- Exclude system DBs

AND state_desc = 'ONLINE';

-- Initialize loop

WHILE EXISTS (SELECT 1 FROM @DBList)

BEGIN

    -- Get one database name

    SELECT TOP 1 @DBName = DBName FROM @DBList;

    -- Build and execute the ALTER statement

    SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 160;';

    PRINT 'Updating compatibility for: ' + @DBName;

PRINT @SQL

    --EXEC sp_executesql @SQL;

    -- Remove processed DB

    DELETE FROM @DBList WHERE DBName = @DBName;

END

--Set  compatability  to  SQL server  2022

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = 160

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

-- Roll back script

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = '+CONVERT(VARCHAR(5),compatibility_level) +'

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

SQLDBA - Script to Loop through all the transaction log file and restore them automatically.

 

Script to Loop through all the transaction log file and restore them automatically.

Below script is generating the restoring the  transactional log backups.  Initially  we have inserted transaction files into  temp table and then we have generating restoring the tractional log backup.

Method1:

USE master;

DECLARE @t AS TABLE (fname VARCHAR(max));

INSERT @t EXEC xp_cmdshell 'dir /b /S E:\dirname\*.trn';

SELECT 'RESTORE LOG [DBNAME] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL

Method2:

Powershell  Script;

To automatically loop through and restore all SQL Server transaction log files (.trn), you can use a PowerShell script or T-SQL script depending on your environment and preferences. Here's a PowerShell script that reads all .trn files from a folder and restores them in sequence.

# Define variables
$serverName = "YourSQLServerInstance"  
$databaseName = "YourDatabase"  
$backupFolder = "C:\Backups\Logs"
$restoreDatabase = $false  # Set to $true if you want to restore the full DB first

# Optional: Restore full backup if needed
if ($restoreDatabase) {
    $fullBackupFile = "C:\Backups\YourDatabase.bak"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE DATABASE [$databaseName]
        FROM DISK = N'$fullBackupFile'
        WITH NORECOVERY, REPLACE"
}

# Get all .trn files sorted by creation time
$logFiles = Get-ChildItem -Path $backupFolder -Filter *.trn | Sort-Object LastWriteTime

# Loop through and restore each log file
foreach ($file in $logFiles) {
    Write-Host "Restoring log file: $($file.Name)"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE LOG [$databaseName]
        FROM DISK = N'$($file.FullName)'
        WITH NORECOVERY"
}

# Finally, recover the database
Invoke-Sqlcmd -ServerInstance $serverName -Query "
    RESTORE DATABASE [$databaseName] WITH RECOVERY"

Write-Host "Transaction log restore completed."

Method3:

# Define parameters
$serverInstance = "localhost\SQLEXPRESS"  # Change to your SQL Server instance
$databaseName = "YourDatabaseName"        # Change to your target database
$logBackupFolder = "C:\LogBackups"        # Folder containing .trn files

# Load SQL Server SMO
Add-Type -AssemblyName "Microsoft.SqlServer.SMO"
Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended"

# Connect to SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance

# Get all .trn files sorted by name (assuming naming reflects order)
$logFiles = Get-ChildItem -Path $logBackupFolder -Filter *.trn | Sort-Object Name

foreach ($logFile in $logFiles) {
    Write-Host "Restoring log file: $($logFile.Name)"

    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $restore.Action = "Log"
    $restore.Database = $databaseName
    $restore.Devices.AddDevice($logFile.FullName, "File")
    $restore.NoRecovery = $true  # Keep in restoring state for next log

    try {
        $restore.SqlRestore($server)
        Write-Host "Successfully restored: $($logFile.Name)"
    } catch {
        Write-Host "Failed to restore: $($logFile.Name)"
        Write-Host $_.Exception.Message
    }
}

Write-Host "All log files processed."

SQL DBA - Find the job names with user name with running as job owner.

 

-- Find the job names with user name with running as job owner. 

SELECT j.name AS JobName,

       j.owner_sid,

       s.name AS JobOwner,

       a.start_execution_date

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobactivity AS a

    ON j.job_id = a.job_id

INNER JOIN sys.server_principals AS s

    ON j.owner_sid = s.sid

WHERE a.run_requested_date IS NOT NULL

  AND a.stop_execution_date IS NULL  -- Job is still running

  AND s.name LIKE  '%SVC_%';       -- Replace with the specific user

-- Find the job name, with command context . 

SELECT j.name AS JobName,

       js.step_id,

       js.step_name,

       js.database_name,

       js.command,

       js.subsystem

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobsteps AS js

    ON j.job_id = js.job_id

WHERE js.command LIKE '%SVC%';

SQLDBA- How to verify the CDC is enabled data bases or tables

How to  verify the CDC is enabled data bases or tables

The following query  will get list  of  CDC enabled data bases 

SELECT

    name AS DatabaseName,

    is_cdc_enabled

FROM sys.databases

WHERE is_cdc_enabled = 1;

The following query  will get list  of  CDC enabled  tables  with in the  database. 

USE <DB Name>;  -- Replace with actual DB name

GO

SELECT 

    s.name AS SchemaName,

    t.name AS TableName,

    c.capture_instance,

    c.supports_net_changes,

    c.start_lsn,

    c.end_lsn

FROM 

    cdc.change_tables c

JOIN 

    sys.tables t ON c.source_object_id = t.object_id

JOIN 

    sys.schemas s ON t.schema_id = s.schema_id;

Below will get the list of tables which  are enabled CDC from all data bases. 

DECLARE @dbName NVARCHAR(128)

DECLARE @sql NVARCHAR(MAX)

-- Cursor to loop through all CDC-enabled databases

DECLARE db_cursor CURSOR FOR

SELECT name FROM sys.databases WHERE is_cdc_enabled = 1

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT 'Checking CDC tables in database: ' + @dbName

    SET @sql = '

    USE [' + @dbName + '];

    IF EXISTS (SELECT 1 FROM sys.tables WHERE is_tracked_by_cdc = 1)

    BEGIN

        SELECT 

            ''' + @dbName + ''' AS DatabaseName,

            s.name AS SchemaName,

            t.name AS TableName,

            c.capture_instance,

            c.supports_net_changes,

            c.start_lsn,

            c.end_lsn

        FROM 

            cdc.change_tables c

        JOIN 

            sys.tables t ON c.source_object_id = t.object_id

        JOIN 

            sys.schemas s ON t.schema_id = s.schema_id;

    END

    ELSE

    BEGIN

        PRINT ''No CDC-enabled tables found in database: ' + @dbName + '''

    END

    '

    EXEC sp_executesql @sql

    FETCH NEXT FROM db_cursor INTO @dbName

END

CLOSE db_cursor

DEALLOCATE db_cursor

DBA- Server Login name , DB login name along with their roles/memebers in sql server

 

Below Query  help  bring the  server  login name , data base level login name  along with their roles/members. 

This query will help us to validate the roles/ members are mapped correctly or not. 

USE master 

GO

CREATE TABLE #temp

(

id INTEGER  IDENTITY(1,1),

DatabasseName VARCHAR(100),

login_name VARCHAR(500),

database_user VARCHAR(500),

role_name VARCHAR(500),

user_type VARCHAR(500),

authentication_type_desc VARCHAR(500)

)

EXEC sp_foreachdb 

'

USE ?

INSERT INTO #temp (DatabasseName,login_name,database_user,role_name,user_type,authentication_type_desc)

SELECT

''?'' AS DatabasseName,

    sp.name AS login_name,

    dp.name AS database_user,

    dr.name AS role_name,

    dp.type_desc AS user_type,

    dp.authentication_type_desc

FROM 

    sys.server_principals sp

 JOIN  

    sys.database_principals dp ON sp.sid = dp.sid

LEFT JOIN 

    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

LEFT JOIN 

    sys.database_principals dr ON drm.role_principal_id = dr.principal_id

WHERE 

    sp.type IN (''S'',''U'', ''G'') AND dp.type IN (''S'', ''U'', ''G'')

ORDER BY 

    sp.name, dr.name 

'

SELECT  *  FROM #temp

WHERE login_name ='svcTMWorks-trng' 

--- DB Login names with their roles

SELECT

    sp.name AS login_name,

    dp.name AS database_user,

    dr.name AS role_name,

    dp.type_desc AS user_type,

    dp.authentication_type_desc

FROM 

    sys.server_principals sp

LEFT JOIN  

    sys.database_principals dp ON sp.sid = dp.sid

LEFT JOIN 

    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

LEFT JOIN 

    sys.database_principals dr ON drm.role_principal_id = dr.principal_id

WHERE 

    sp.type IN ('S', 'U', 'G') AND dp.type IN ('S', 'U', 'G')

AND sp.name  ='svcTMWorks-trng'

ORDER BY 

    sp.name, dr.name;

SQLDBA- how to know the created index file group name with T-SQL

 Hello, 

To determine the filegroup where an index is created in SQL Server, you can use the following methods:

1. Using sys.indexes and sys.filegroups System Views

You can query the system views to find the filegroup of an index:

Sql
SELECT 
    i.name AS IndexName,
    o.name AS TableName,
    f.name AS FileGroupName
FROM 
    sys.indexes i
JOIN 
    sys.objects o ON i.object_id = o.object_id
JOIN 
    sys.data_spaces f ON i.data_space_id = f.data_space_id
WHERE 
    o.type = 'U'; -- Only user tables

This query will list all indexes, their associated tables, and the filegroups they belong to.

2. Using SQL Server Management Studio (SSMS)

1. Open SSMS and connect to your database.
2. Navigate to the table where the index is created.
3. Expand the Indexes folder under the table.
4. Right-click the index and select Properties.
5. In the Storage section, you can see the filegroup where the index is stored.

3. Using sp_helpindex

You can use the sp_helpindex system stored procedure to get details about indexes on a specific table:

Sql
EXEC sp_helpindex 'YourTableName';

This will return information about the indexes on the table, including their names and types. However, it does not directly show the filegroup. For filegroup details, use the first method.

SQLDBA- Get the list of indices on table with columns and include columns

 

Below help us to get the list of indices  on  table with columns and include columns also.

/*

Explanation:

sys.indexes: Contains information about all indexes in the database.

sys.index_columns: Links indexes to their columns and indicates whether a column is included (is_included_column = 1).

sys.columns: Provides column names for the table.

OBJECT_ID('YourTableName'): Filters the query to only the specified table.

Replace 'YourTableName' with the name of your table to get the desired results. This query will list all indexes, their columns, and whether each column is an included column.

*/

USE <Db Name>

GO

SELECT 

   i.name AS IndexName,

    i.type_desc AS IndexType,

    c.name AS ColumnName,

    ic.is_included_column AS IsIncludedColumn

    --ic.*

FROM 

    sys.indexes i

INNER JOIN 

    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

INNER JOIN 

    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE 

    i.object_id = OBJECT_ID('sls_order_master_extension_closed_order') -- Replace 'YourTableName' with your table name

ORDER BY 

    i.name, ic.is_included_column DESC, ic.index_column_id;

    ----ix_sls_order_master_extension_closed_order_4

    --SELECT TOP(1)* FROM reference.dbo.company_master 

    --SELECT * FROM  sys.index_columns

SQL DBA - Find the no of databases base, no of tables each database and no of column each table

We got requirement  how many no of database in each instance ,  how many  no of tables each database and how many  no of columns in each table. 

Below queries  useful to get the above information. 

1. Below queries will return with multiple result set  with above information. 

--Number of Databases in the Cluster (Production Only)

SELECT COUNT(*) AS NumberOfDatabases

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' );  -- Adjust this filter based on your naming convention

--SELECT * FROM sys.databases

--2. Number of Tables Within Each Database

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

SELECT ''' + name + ''' AS DatabaseName, COUNT(*) AS TableCount

FROM sys.tables;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;

--3. Number of Columns Within Each Table

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

SELECT ''' + name + ''' AS DatabaseName, 

       t.name AS TableName, 

       COUNT(c.column_id) AS ColumnCount

FROM sys.tables t

JOIN sys.columns c ON t.object_id = c.object_id

GROUP BY t.name;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;

2. Below Query  will give the same information in single result set. 

DECLARE @sql NVARCHAR(MAX) = '';

-- Create temp table to store results

SET @sql = '

IF OBJECT_ID(''tempdb..#DBTableColumnInfo'') IS NOT NULL DROP TABLE #DBTableColumnInfo;

CREATE TABLE #DBTableColumnInfo (

    DatabaseName SYSNAME,

    TableName SYSNAME,

    ColumnCount INT

);';

-- Append dynamic SQL for each database

SELECT @sql += '

USE [' + name + '];

INSERT INTO #DBTableColumnInfo (DatabaseName, TableName, ColumnCount)

SELECT 

    ''' + name + ''' AS DatabaseName,

    t.name AS TableName,

    COUNT(c.column_id) AS ColumnCount

FROM sys.tables t

JOIN sys.columns c ON t.object_id = c.object_id

GROUP BY t.name;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

-- Final select

SET @sql += '

SELECT 

    DatabaseName,

    COUNT(DISTINCT TableName) AS TableCount,

    SUM(ColumnCount) AS TotalColumns

FROM #DBTableColumnInfo

GROUP BY DatabaseName;

SELECT * FROM #DBTableColumnInfo;

SELECT COUNT(DISTINCT DatabaseName) AS TotalProductionDatabases FROM #DBTableColumnInfo;

';

EXEC sp_executesql @sql;

SQLDBA- Deadlock graph query

 

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 BY UtcTime DESC;

SQLDBA - ADM - Long Running Jobs

 

  

  

  

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 INT NOT NULL  

 ,requested_to_run INT NOT NULL  

 ,-- BOOL  

 request_source INT NOT NULL  

 ,request_source_id SYSNAME COLLATE database_default NULL  

 ,running INT NOT NULL  

 ,-- BOOL  

 current_step INT NOT NULL  

 ,current_retry_attempt INT NOT NULL  

 ,job_state INT NOT NULL  

 )  

  

--Capture Jobs currently working  

INSERT INTO @currently_running_jobs  

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1  

 ,''  

  

SET @string = 'Long Running SQLAgent Jobs on ' + @@servername  

SET @xml = CAST((  

   SELECT DISTINCT jobs.[name] AS 'td'  

    ,''  

    ,ja.run_requested_date AS 'td'  

    ,''  

    ,DATEDIFF(hour, ja.run_requested_date, getdate()) AS 'td'  

   FROM @currently_running_jobs AS crj  

   INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id  

   INNER JOIN msdb..sysjobactivity AS ja ON ja.job_id = crj.job_id  

   WHERE crj.running = 1  

    AND ja.run_requested_date = (  

     SELECT max(ja1.run_requested_date)  

     FROM msdb..sysjobactivity AS ja1  

     WHERE ja1.job_id = crj.job_id  

     )  

    AND DATEDIFF(hour, ja.run_requested_date, getdate()) >= 5--@hours

    AND jobs.[name] NOT LIKE ('%Database Consistency%')  

   ORDER BY jobs.[name] DESC  

   FOR XML PATH('tr')  

    ,ELEMENTS  

   ) AS NVARCHAR(MAX))  

SET @body = '<html><body><H3>The following jobs have been running for more than 5 hours</H3>  

<table border = 1>   

<tr>  

<th> Job Name </th> <th> Job Started </th> <th> Hours Run </th></tr>'  

SET @body = @body + @xml + '</table></body></html>'  

  

--check if the there is any data available before sending email.  

IF EXISTS (  

  SELECT 1  

  FROM @currently_running_jobs AS crj  

  INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id  

  INNER JOIN msdb..sysjobactivity AS ja ON ja.job_id = crj.job_id  

  WHERE crj.running = 1  

   AND ja.run_requested_date = (  

    SELECT max(ja1.run_requested_date)  

    FROM msdb..sysjobactivity AS ja1  

    WHERE ja1.job_id = crj.job_id  

    )  

   AND DATEDIFF(hour, ja.run_requested_date, getdate()) >= 5

    AND jobs.[name] NOT LIKE ('%Database Consistency%')  

  )  

BEGIN  

 EXEC msdb.dbo.sp_send_dbmail @recipients = 'ksubbareddy.sap@gmail.com'  

  ,@subject = @string  

  ,@body = @body  

  ,@body_format = 'HTML'  

  ,@importance = 'High'  

END  

ELSE  

 RETURN (0)  

  ---------------------------------------------------------------------------------------------------

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[On Demand]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[On Demand]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ADM - Long Running Jobs', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=2, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'No description available.', 

@category_name=N'[On Demand]', 

@owner_login_name=N'sa', 

@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Long Running Jobs]    Script Date: 8/18/2025 6:54:54 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Long Running Jobs', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'EXEC sp_longrunningjobs 8', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Long Running Jobs', 

@enabled=1, 

@freq_type=4, 

@freq_interval=1, 

@freq_subday_type=4, 

@freq_subday_interval=30, 

@freq_relative_interval=0, 

@freq_recurrence_factor=0, 

@active_start_date=20160810, 

@active_end_date=99991231, 

@active_start_time=0, 

@active_end_time=235959, 

@schedule_uid=NULL;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

During SQL Server patching is when the SQL Server service fails to start after applying a patch

 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 Server Error Logs and Windows Event Logs for specific errors.

𝗥𝗲𝘀𝘁𝗼𝗿𝗲 𝗦𝘆𝘀𝘁𝗲𝗺 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲𝘀: If corruption is detected, restore from a known good backup of system databases.

𝗖𝗵𝗲𝗰𝗸 𝗖𝗼𝗻𝗳𝗶𝗴𝘂𝗿𝗮𝘁𝗶𝗼𝗻: Ensure no conflicts with new settings introduced by the patch. Verify memory settings, tempdb, and other configurations.

𝗩𝗲𝗿𝗶𝗳𝘆 𝗣𝗲𝗿𝗺𝗶𝘀𝘀𝗶𝗼𝗻𝘀: Ensure the SQL Server service account has the necessary access to files and directories.

𝗗𝗶𝘀𝗸 𝗦𝗽𝗮𝗰𝗲: Confirm sufficient disk space, especially for tempdb and transaction logs.

𝗥𝗲𝗮𝗽𝗽𝗹𝘆 𝗣𝗮𝘁𝗰𝗵 𝗼𝗿 𝗥𝗲𝗽𝗮𝗶𝗿 𝗜𝗻𝘀𝘁𝗮𝗹𝗹𝗮𝘁𝗶𝗼𝗻: If needed, reapply the patch or use SQL Server Setup to perform a repair.

𝗥𝗼𝗹𝗹𝗯𝗮𝗰𝗸 𝗣𝗮𝘁𝗰𝗵 (𝗶𝗳 𝗻𝗲𝗲𝗱𝗲𝗱): If the issue persists, rollback the patch or restore from backup.

𝗣𝗿𝗲𝘃𝗲𝗻𝘁𝗶𝗼𝗻 𝗧𝗶𝗽𝘀:

Always test patches in a non-prod environment first.

Backup critical system databases, especially master, before applying patches.

Document configurations before patching and monitor disk space and logs to detect issues early.

Patching is critical for SQL Server security and performance, but it’s vital to ensure a smooth patching process with proactive steps. Let’s keep SQL environments healthy and running!

Below query will get the table FK reference table list.

 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;

 

 

SQLDBA- verify the backup percentage on prem sql server

 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 PercentComplete

,CONVERT(NUMERIC(6,2),Req.estimated_completion_time/1000.0/60.0) AS MinutesUntilFinish

,DB_NAME(Req.database_id) AS DbName,

Req.session_id AS SPID, Txt.text AS Query,

Req.command AS SubQuery,

Req.start_time AS StartTime

,(CASE WHEN Req.estimated_completion_time < 1

THEN NULL

ELSE DATEADD(SECOND, Req.estimated_completion_time / 1000, GETDATE())

END) AS EstimatedFinishDate

,Req.[status] AS QueryState, Req.wait_type AS BlockingType,

Req.blocking_session_id AS BlockingSPID

FROM sys.dm_exec_requests AS Req

CROSS APPLY sys.dm_exec_sql_text(Req.[sql_handle]) AS Txt

WHERE Req.command IN ('BACKUP DATABASE','RESTORE DATABASE') OR Req.command LIKE 'DBCC%';
















-- Restoring percenatge




SELECT
session_id,
command,
percent_complete,
start_time,
total_elapsed_time / 1000.0 / 60.0 AS elapsed_minutes,
estimated_completion_time / 1000.0 / 60.0 AS estimated_remaining_minutes
FROM
sys.dm_exec_requests
WHERE
command IN ('RESTORE DATABASE', 'RESTORE LOG');