Video

Nice video

Hi harsha can u give me classes

Hello Sir, This is awesome, can you please help if I want to apply this on insert and Update also I want to capture if admin run 'Select * From table' query to view the data.

good Harsha

create database test use test Go CREATE TABLE Testtable ( ID int IDENTITY(1,1) PRIMARY KEY, Name nvarchar(100) NOT NULL ); INSERT into Testtable VALUES ('Harsha kalki') DBCC IND('Test','Testtable',-1) DBCC IND('Test','Testtable',-2) BACKUP DATABASE [test] TO DISK = N'C:\test.bak' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO DBCC TRACEON(3604) GO DBCC PAGE('Test',1,352,3) --352 -- Understanding the Print Levels: --0: Only page header information is displayed. --1: Page header plus per-row information (limited details). --2: Page header plus full row information. --3: Page header, full row information, and page data (detailed information). select 352*8192--2883584 use test Go select * from Testtable

Nice one bro and keep sharing your knowledge and skills to the DBA community...

select sp.database_id "Database ID", d.name "Database", sp.file_id "File ID", mf.physical_name "File", sp.page_id "Page ID", case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page' when sp.event_type = 2 then 'Bad checksum' when sp.event_type = 3 then 'Torn Page' when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)' when sp.event_type = 5 then 'Repaired (DBCC repaired the page)' when sp.event_type = 7 then 'Deallocated by DBCC' end as "Event Desc", sp.error_count "Error Count", sp.last_update_date "Last Updated" from msdb.dbo.suspect_pages sp inner join sys.databases d on d.database_id=sp.database_id inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id ======================================== select DB_NAME(database_id) AS DBNAME, * from msdb.dbo.suspect_pages Delete from msdb.dbo.suspect_pages where last_update_date < getdate()-30 ======================================== Declare @count integer Declare @tableHTML nvarchar(MAX); Declare @subj nvarchar(100) select @count=count(1) from msdb.dbo.suspect_pages; set @subj = 'Suspect Pages Found in ' + @@SERVERNAME; set @tableHTML = N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' + N'<table border="1" bgcolor="#FFC0CB">' + N'<tr><th>Database ID</th><th>Database</th>' + N'<th>File ID</th><th>File</th><th>Page ID</th>' + N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' + cast ( ( select td = sp.database_id, '', td = d.name, '', td = sp.file_id, '', td = mf.physical_name, '', td = sp.page_id, '', td = case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page' when sp.event_type = 2 then 'Bad checksum' when sp.event_type = 3 then 'Torn Page' when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)' when sp.event_type = 5 then 'Repaired (DBCC repaired the page)' when sp.event_type = 7 then 'Deallocated by DBCC' end, '', td = sp.error_count, '', td = sp.last_update_date from msdb.dbo.suspect_pages sp inner join sys.databases d on d.database_id=sp.database_id inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id for xml path('tr'), TYPE ) as nvarchar(max) ) + N'</table>' ; IF @count > 0 exec msdb.dbo.sp_send_dbmail @profile_name ='DBATEAM', @recipients=N'xxxxx@gmail.com', @body= @tableHTML, @subject = @subj, @body_format = 'HTML'

Great demo The "Oh shitt" moment got me lol @9:20

Thanks for sharing your knowledge🙏

Thanks for sharing such a nice way to make our job easier, I Agree, some of the small companies can't afford money for the monitoring, You are helping them , Such a great idea. Keep rocking.

Hi sir will i get cluster configuration docs..

Microsoft SQL Server ODBC and OLE DB Driver for SQL Server Multiple Vulnerabilities --who will fix these vulns

Very useful concept bro

Hi @harsha..can it be possible to get healthcheck script for multiple servers in one consolidated report?

Thanks for sharing

Nice! Can you please share the script?

create database test GO USE [master] GO ALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAIT GO ALTER DATABASE [test] MODIFY FILE ( NAME = N'test_log', FILEGROWTH = 0) GO BACKUP DATABASE [test] TO DISK = N'C:\test.bak' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO use test GO create table test(id int,name varchar(100),location varchar(100)) insert into test values(1,'harsha','india') GO 2000 -- Query to get detailed information about database files SELECT db.name AS [Database Name], mf.name AS [File Name], mf.physical_name AS [Physical File Name], mf.type_desc AS [File Type], mf.state_desc AS [File State], CAST(mf.size AS BIGINT) * 8 / 1024 AS [Size (MB)], CASE WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS NVARCHAR(20)) + ' %' ELSE CAST(mf.growth * 8 / 1024 AS NVARCHAR(20)) + ' MB' END AS [Autogrowth], mf.max_size AS [Max Size] FROM sys.master_files mf JOIN sys.databases db ON mf.database_id = db.database_id WHERE db.name = 'TEST' ORDER BY db.name, mf.type_desc; DECLARE @dbname VARCHAR(100) = NULL, @SpaceUsed FLOAT = NULL DECLARE @LOGSPACE TABLE( dbName VARCHAR(100), LogSizeMB FLOAT, [LogSpaceUsed%] FLOAT, [Status] INT ) INSERT @LOGSPACE EXEC ('DBCC SQLPERF(''logspace'')') SELECT dbName, LogSizeMB, [LogSpaceUsed%], [Status] FROM @LOGSPACE where [dbName] = 'TEST' select * from sys.sysmessages where msglangid = 1033 and severity = 17

create database test GO USE [master] GO ALTER DATABASE [test] SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE [test] MODIFY FILE ( NAME = N'test', FILEGROWTH = 0) GO BACKUP DATABASE [test] TO DISK = N'C:\test.bak' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO use test GO create table test(id int,name varchar(100),location varchar(100)) insert into test values(1,'harsha','india') GO 50000 SELECT COUNT(*) FROM test -- Query to get detailed information about database files SELECT db.name AS [Database Name], mf.name AS [File Name], mf.physical_name AS [Physical File Name], mf.type_desc AS [File Type], mf.state_desc AS [File State], CAST(mf.size AS BIGINT) * 8 / 1024 AS [Size (MB)], CASE WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS NVARCHAR(20)) + ' %' ELSE CAST(mf.growth * 8 / 1024 AS NVARCHAR(20)) + ' MB' END AS [Autogrowth], mf.max_size AS [Max Size] FROM sys.master_files mf JOIN sys.databases db ON mf.database_id = db.database_id WHERE db.name = 'TEST' ORDER BY db.name, mf.type_desc; DECLARE @dbname VARCHAR(100) = NULL, @SpaceUsed FLOAT = NULL DECLARE @LOGSPACE TABLE( dbName VARCHAR(100), LogSizeMB FLOAT, [LogSpaceUsed%] FLOAT, [Status] INT ) INSERT @LOGSPACE EXEC ('DBCC SQLPERF(''logspace'')') SELECT dbName, LogSizeMB, [LogSpaceUsed%], [Status] FROM @LOGSPACE where [dbName] = 'TEST' select * from sys.sysmessages where msglangid = 1033 and severity = 17

Nice info bro...

Nice one...

DECLARE @NonSaDBs TABLE ( DBName NVARCHAR(128), DBOwner NVARCHAR(128) ); INSERT INTO @NonSaDBs (DBName, DBOwner) select Name,suser_sname(owner_sid) AS DbOwner from sys.databases WHERE suser_sname(owner_sid) <> 'sa' Declare @subject Varchar(200) Set @subject = 'SQL Server DB Owner Alert Of' + @@SERVERNAME IF EXISTS (SELECT 1 FROM @NonSaDBs) BEGIN DECLARE @DBList NVARCHAR(MAX) = ''; SELECT @DBList = @DBList + '<tr><td>' + DBName + '</td><td>' + DBOwner + '</td></tr>' FROM @NonSaDBs; DECLARE @body NVARCHAR(MAX); SET @body = '<html>' + '<head>' + '<style>' + 'table {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse; font-size: 12px; width: auto;}' + 'th {border-width: 1px; padding: 2px; border-style: solid; border-color: black; background-color: Pink; font-size: 12px; white-space: nowrap;}' + 'td {border-width: 1px; padding: 2px; border-style: solid; border-color: black; font-size: 12px; white-space: nowrap;}' + 'h1 {font-size: 16px;}' + '</style>' + '</head>' + '<body>' + '<p>The following Databases are Not Owned by sa:</p>' + '<table>' + '<tr><th>DB Name</th><th>Job Owner</th></tr>' + @DBList + '</table>' + '</body>' + '</html>'; -- Send alert email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBATEAM', -- Change to your Database Mail profile name @recipients = 'xxxxx@gmail.com', -- Change to your alert email address @subject = @subject, @body = @body, @body_format = 'HTML'; -- Specify that the body is HTML END;

Thanks a lot.

Thank you

Thanks for sharing 🤝 Nice explanation 👍

Where to find that script in your shared google drive ?

Use AdventureWorks2019 Go CREATE TABLE TableStructureSnapshot ( TableName VARCHAR(255), ColumnName VARCHAR(255), DataType VARCHAR(255), CHARACTER_MAXIMUM_LENGTH VARCHAR(max), IsNullable VARCHAR(3), TableType VARCHAR(50), Insertedate DATETIME DEFAULT GETDATE(), PRIMARY KEY (TableName, ColumnName) ); INSERT INTO TableStructureSnapshot (TableName, ColumnName, DataType,CHARACTER_MAXIMUM_LENGTH, IsNullable, TableType) SELECT c.table_name, c.column_name, c.data_type, C.CHARACTER_MAXIMUM_LENGTH, c.is_nullable, t.table_type FROM information_schema.columns c JOIN information_schema.tables t ON c.table_name = t.table_name --select * from TableStructureSnapshot where tablename = 'errorlog' --drop table TableStructureSnapshot --Truncate table TableStructureSnapshot --Made Changes Create table testing (id int) Go Create table Testing1 (id int) Go Alter table testing Add names varchar(10) Go Alter Table errorlog add rollno varchar(10) Go ALTER TABLE errorlog ALTER COLUMN ErrorProcedure VARCHAR(100); --new table created SELECT Currents.TableName FROM (SELECT table_name AS TableName FROM information_schema.tables WHERE table_type = 'BASE TABLE') AS Currents LEFT JOIN TableStructureSnapshot AS Snapshots ON Currents.TableName = Snapshots.TableName WHERE Snapshots.TableName IS NULL; -- Old Table Deleted: SELECT Distinct(Snapshots.TableName) FROM TableStructureSnapshot AS Snapshots LEFT JOIN information_schema.tables AS Currents ON Snapshots.TableName = Currents.table_name WHERE Currents.table_name IS NULL; ------------------------------------------- --New Coulumns Detected.. IF OBJECT_ID('tempdb..#ComparisonResults') IS NOT NULL DROP TABLE #ComparisonResults; -- Create the temporary table CREATE TABLE #ComparisonResults ( TableName VARCHAR(255), ColumnName VARCHAR(255), DataType VARCHAR(255), IsNullable VARCHAR(3) ); -- Insert the comparison results into the temporary table INSERT INTO #ComparisonResults (TableName, ColumnName, DataType, IsNullable) SELECT curr.TableName, curr.ColumnName, curr.DataType, curr.IsNullable FROM (SELECT table_name AS TableName, column_name AS ColumnName, data_type AS DataType, is_nullable AS IsNullable FROM information_schema.columns) AS curr FULL OUTER JOIN TableStructureSnapshot AS snap ON curr.TableName = snap.TableName AND curr.ColumnName = snap.ColumnName WHERE curr.TableName IS NULL OR snap.TableName IS NULL OR curr.ColumnName IS NULL OR snap.ColumnName IS NULL OR curr.DataType <> snap.DataType OR curr.IsNullable <> snap.IsNullable; -- Select from the temporary table to view the results SELECT * FROM #ComparisonResults where TableName is not null -- Optionally, drop the temporary table when done DROP TABLE #ComparisonResults; -- Columns Deleted Deteted. SELECT TableName, ColumnName, 'Columns deleted or Renamed' AS Status FROM TableStructureSnapshot tss WHERE NOT EXISTS ( SELECT * FROM information_schema.columns ic WHERE tss.TableName = ic.TABLE_NAME AND tss.ColumnName = ic.COLUMN_NAME ) AND EXISTS ( SELECT * FROM information_schema.tables it WHERE it.TABLE_NAME = tss.TableName ); ----New Coulmns added SELECT ic.TABLE_NAME AS TableName, ic.COLUMN_NAME AS ColumnName, 'New Columns Added' AS Status FROM information_schema.columns ic WHERE EXISTS ( SELECT 1 FROM TableStructureSnapshot tss WHERE tss.TableName = ic.TABLE_NAME ) AND NOT EXISTS ( SELECT 1 FROM TableStructureSnapshot tss WHERE tss.TableName = ic.TABLE_NAME AND tss.ColumnName = ic.COLUMN_NAME ); ---Detect datatype change SELECT tss.TableName, tss.ColumnName, tss.DataType AS OldDataType, ic.DATA_TYPE AS NewDataType FROM TableStructureSnapshot tss JOIN information_schema.columns ic ON tss.TableName = ic.TABLE_NAME AND tss.ColumnName = ic.COLUMN_NAME JOIN information_schema.tables it ON it.TABLE_NAME = tss.TableName WHERE tss.DataType <> ic.DATA_TYPE AND it.TABLE_TYPE = 'BASE TABLE'; --------------- ---Detect datatype size change SELECT tss.TableName, tss.ColumnName, tss.CHARACTER_MAXIMUM_LENGTH AS OldMaxLength, ic.CHARACTER_MAXIMUM_LENGTH AS NewMaxLength FROM TableStructureSnapshot tss JOIN information_schema.columns ic ON tss.TableName = ic.TABLE_NAME AND tss.ColumnName = ic.COLUMN_NAME JOIN information_schema.tables it ON it.TABLE_NAME = tss.TableName WHERE tss.CHARACTER_MAXIMUM_LENGTH <> ic.CHARACTER_MAXIMUM_LENGTH AND it.TABLE_TYPE = 'BASE TABLE';

Nice explanation...it would be great if you could add the user details as well to know who changed

Hello Harsha, Which edition setup you download?

Sir , i have a doubt ,in my organization when we click on product key the keys values get automatically generated ,so what is process behind it . Does the clients but setup file along with the key integrated within the setup file ?

Nice bro...one request from my end can u plzzz make one video on how to configure distributed AG

Knowledgeable

DECLARE @NonSaJobs TABLE ( JobName NVARCHAR(128), JobOwner NVARCHAR(128) ); INSERT INTO @NonSaJobs (JobName, JobOwner) SELECT name AS JobName, suser_sname(owner_sid) AS JobOwner FROM msdb.dbo.sysjobs WHERE suser_sname(owner_sid) <> 'sa'; Declare @subject Varchar(200) Set @subject = 'SQL Server Job Owner Alert Of' + @@SERVERNAME IF EXISTS (SELECT 1 FROM @NonSaJobs) BEGIN DECLARE @JobList NVARCHAR(MAX) = ''; SELECT @JobList = @JobList + '<tr><td>' + JobName + '</td><td>' + JobOwner + '</td></tr>' FROM @NonSaJobs; DECLARE @body NVARCHAR(MAX); SET @body = '<html>' + '<head>' + '<style>' + 'table {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse; font-size: 12px; width: auto;}' + 'th {border-width: 1px; padding: 2px; border-style: solid; border-color: black; background-color: Pink; font-size: 12px; white-space: nowrap;}' + 'td {border-width: 1px; padding: 2px; border-style: solid; border-color: black; font-size: 12px; white-space: nowrap;}' + 'h1 {font-size: 16px;}' + '</style>' + '</head>' + '<body>' + '<p>The following Jobs are Not Owned by sa:</p>' + '<table>' + '<tr><th>Job Name</th><th>Job Owner</th></tr>' + @JobList + '</table>' + '</body>' + '</html>'; -- Send alert email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'outlook', -- Change to your Database Mail profile name @recipients = 'musi@gmail.com', -- Change to your alert email address @subject = @subject, @body = @body, @body_format = 'HTML'; -- Specify that the body is HTML END;

Ty Harsha

Here is the google drive link for the excel file docs.google.com/spreadsheets/d/1EfNtWuv4308QezPd2zhwchrl0YT_6tdn/edit?usp=sharing&ouid=104453716623177612842&rtpof=true&sd=true

Thank you harsh very usefull sessions.❤❤

Roster In Our organization we used to send any planned leaves 10 days before it self via email. We need to see the current month Roaster last week and need to Plan the Roster for next month. We are having 3 shifts Shifts Starts Ends First Shift 6.30 AM 3.30 PM General Shift 9 Am 6 PM Second Shift 1.30 PM 10 PM Evening shift 6 PM 3.30 AM Night Shit 10 PM 7 AM • Generally, we keep one lead…two senior data admins in Shift. Depending the workload adjustment will happen. • If no work is there in First shift, then they may ask to come in General shift so that the resource will be available up to 6 PM clock. • We used to send the roster 2 days before of month end.so adjustments can be done further if any.

Nice Info Harsha

Thank you

Good Bro...

Well said sir

Thank you for this.

##################### #Daily Job Moniter Report Status #Indivial HTML files will Get Generated per server ###################### $starttime = Get-Date $formattedDate = $starttime.ToString("-yyyy-MM-dd_HH-mm-ss") $servernames = Get-Content -Path "C:\HealthcheckReports\serverlist.txt" foreach ($servername in $servernames) { Write-Host "Starting Server $servername" $dataSource = $servername $database = "master" $path = "C:\HealthcheckReports" $name = $dataSource -replace "\\","_" $OutputFile_new = Join-Path -Path $path -ChildPath ($name + $formattedDate + '.html') $style = @" <style> BODY { background-color: #e0e0e0; height: 100%; width: 100%; transform: scale(0.8); transform-origin: top left; } TABLE { border-width: 1px; border: 2px solid black; } TH { border-width: 2px; padding: 5px; border: 2px solid black;background-color: pink; font-family: 'Arial Narrow', Arial, sans-serif; } TD { border-width: 2px; padding: 5px; border: 2px solid black; BGCOLOR = LIGHTGREEN } tr:nth-child(even) { background-color: #f2f2f2 } .ENABLED { color: green; font-weight: bold; } .DISABLED { color: red; font-weight: bold; } .SUCCEEDED { color: green; font-weight: bold; } .FAILED { color: red; font-weight: bold; } .NEVER { color: Orange; font-weight: bold; } </style> "@ $connectionDetails = "Provider=sqloledb; Data Source=$dataSource; Initial Catalog=$database; Integrated Security=SSPI;" $Jobreport = @" SELECT @@SERVERNAME as SERVERNAME, [SJOB].[NAME] AS [JOBNAME], CASE WHEN [SJOBH].[RUN_DATE] IS NULL OR [SJOBH].[RUN_TIME] IS NULL THEN NULL ELSE CAST( CAST([SJOBH].[RUN_DATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_TIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LASTRUNDATETIME], CASE [SJOBH].[RUN_STATUS] WHEN 0 THEN 'FAILED' WHEN 1 THEN 'SUCCEEDED' WHEN 2 THEN 'RETRY' WHEN 3 THEN 'CANCELLED' WHEN 4 THEN 'RUNNING' END AS [LASTRUNSTATUS], STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_DURATION] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LASTRUNDURATION], CASE WHEN ENABLED = 1 THEN 'ENABLED' ELSE 'DISABLED' END AS [JOBSTATUS], CASE [SJOBSCH].[NEXTRUNDATE] WHEN 0 THEN NULL ELSE CAST( CAST([SJOBSCH].[NEXTRUNDATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBSCH].[NEXTRUNTIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NEXTRUNDATETIME] INTO #AGENT_JOBSTAUS FROM [MSDB].[DBO].[SYSJOBS] AS [SJOB] LEFT JOIN ( SELECT [JOB_ID], MIN([NEXT_RUN_DATE]) AS [NEXTRUNDATE], MIN([NEXT_RUN_TIME]) AS [NEXTRUNTIME] FROM [MSDB].[DBO].[SYSJOBSCHEDULES] GROUP BY [JOB_ID] ) AS [SJOBSCH] ON [SJOB].[JOB_ID] = [SJOBSCH].[JOB_ID] LEFT JOIN ( SELECT [JOB_ID], [RUN_DATE], [RUN_TIME], [RUN_STATUS], [RUN_DURATION], [MESSAGE], ROW_NUMBER() OVER ( PARTITION BY [JOB_ID] ORDER BY [RUN_DATE] DESC, [RUN_TIME] DESC ) AS ROWNUMBER FROM [MSDB].[DBO].[SYSJOBHISTORY] WHERE [STEP_ID] = 0 ) AS [SJOBH] ON [SJOB].[JOB_ID] = [SJOBH].[JOB_ID] AND [SJOBH].[ROWNUMBER] = 1 ORDER BY [JOBNAME]; SELECT SERVERNAME, JOBNAME, COALESCE(CONVERT(VARCHAR(20), LASTRUNDATETIME, 120), 'NEVER') AS LASTRUNDATETIME, COALESCE(CONVERT(VARCHAR(20), LASTRUNSTATUS, 120), 'NEVER') AS LASTRUNSTATUS, COALESCE(CONVERT(VARCHAR(20), LASTRUNDURATION, 120), 'NEVER') AS LASTRUNDURATION, COALESCE(CONVERT(VARCHAR(20), JOBSTATUS, 120), 'NEVER') AS JOBSTATUS, COALESCE(CONVERT(VARCHAR(20), NEXTRUNDATETIME, 120), 'NEVER') AS NEXTRUNDATETIME FROM #AGENT_JOBSTAUS; DROP TABLE #AGENT_JOBSTAUS; "@ $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command = New-Object System.Data.OleDb.OleDbCommand $Jobreport, $connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command $dataSet = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet) | Out-Null $connection.Close() $rows = foreach ($row in $dataSet.Tables[0].Rows) { $lastRunStatus = $row.LASTRUNSTATUS $statusClass = switch ($lastRunStatus) { 'NEVER' { 'class="NEVER"' } 'FAILED' { 'class="FAILED"' } 'SUCCEEDED' { 'class="SUCCEEDED"' } default { '' } } $JOBSTATUS = $row.JOBSTATUS $JOBSTATUSClass = switch ($JOBSTATUS) { 'ENABLED' { 'class="ENABLED"' } 'DISABLED' { 'class="DISABLED"' } default { '' } } $LASTRUNDURATION = $row.LASTRUNDURATION $lastSTATUSClass = switch ($LASTRUNDURATION) { 'NEVER' { 'class="NEVER"' } default { '' } } $LASTRUNDATETIME = $row.LASTRUNDATETIME $lastLASTRUNDATETIMEClass = switch ($LASTRUNDATETIME) { 'NEVER' { 'class="NEVER"' } default { '' } } "<tr> <td>$($row.SERVERNAME)</td> <td>$($row.JOBNAME)</td> <td $lastLASTRUNDATETIMEClass>$LASTRUNDATETIME</td> <td $statusClass>$lastRunStatus</td> <td $lastSTATUSClass>$LASTRUNDURATION</td> <td $JOBSTATUSClass>$JOBSTATUS</td> <td>$($row.NEXTRUNDATETIME)</td> </tr>" } $htmlContent = @" <html> <head> $style </head> <body> <h1 align='Center'>Daily Job Monitor Report</h1> <table> <tr><th>SERVERNAME</th><th>JOBNAME</th><th>LASTRUNDATETIME</th><th>LASTRUNSTATUS</th><th>LASTRUNDURATION</th><th>JOBSTATUS</th><th>NEXTRUNDATETIME</th></tr> $($rows -join "`r`n") </table> </body> </html> "@ $htmlContent | Out-File $OutputFile_new Write-Host "Report generated: $OutputFile_new" } $Stoptime = Get-Date Write-Host "Script completed at $Stoptime" ================================================

Thanks for the detailed explanation Harsha about the SPN issues and how to fix it.. Keep Going..

Is there any way without Admin access ?

Thanks for sharing knowledge

www.alexandreviot.net/2014/09/30/sql-server-could-not-register-the-service-principal-name-spn/ www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/questions-about-kerberos-and-sql-server-that-you-were-too-shy-to-ask/ blog.coeo.com/configure-kerberos-for-availability-groups learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver16 www.sqlshack.com/overview-of-service-principal-name-and-kerberos-authentication-sql-server/ documentation.help/sql.2005.setupsql9/982076a5-d7ec-478e-b86c-64fdbb85f36d.htm help.deepsecurity.trendmicro.com/12_0/aws/kerberos-mssql.html mssqlwiki.com/tag/failure-to-register-a-spn-might-cause-integrated-authentication-to-use-ntlm-instead-of-kerberos/ 4sysops.com/archives/setspn-manage-service-principal-names-in-active-directory-from-the-command-line/ In a distributed environment where components are installed on more than one server, Kerberos security must be configured. One of the components of Kerberos is the Service Principal Name (SPN). Whenever user credentials must be passed from one system to another, the system that is attempting to pass the credentials must be trusted for delegation. For this step to take place successfully, Kerberos delegation must be configured. SQL - When SQL Server authentication is used NTLM - When NTLM authentication is used KERBEROS - When KERBEROS authentication is used Prerequisites when configuring SQL Server to use Kerberos Authentication • All client and servers should be joined to a domain. • If the clients and servers are in different domains then a two-way trust must be setup between domains. • SPN must be successfully registered for the SQL Server Service to be identified on the network. Main Points: • If you connect to the SQL Server instance from the same host where SQL Server is installed then you may see connections using NTLM. • Internal connections and DAC, as we mentioned before, won't use the Kerberos either. They will be connected using Shared memory and NTLM. • Depending on your Availability Group setup you may need to register SPNs for both - Listener and instance name (for example, if a secondary replica is readable). Note, that highlighted TCP ports on the screenshot below are different. The reason is that a DBA was connected with SQL Server Management Studio (SSMS) directly to the SQL Server instance name instead of the Availability Group Listener name: • Virtual accounts and MSAs can register SPNs automatically. • Dynamic ports don't work well with SPNs. • Cross-domain connections require special configuration in order for Kerberos to work. Otherwise, connections will use NTLM. • We should keep in mind that Dedicated Admin Connection (DAC) only uses NTLM and some of the local (internal) connections will use the "Shared memory" net_transport. • The "net_transport" column will always return "Session" when a connection has both multiple active result sets (MARS) enabled, and connection pooling enabled USE [master] GO SELECT COUNT(auth_scheme) as sessions_count, net_transport, auth_scheme FROM sys.dm_exec_connections GROUP BY net_transport, auth_scheme setspn -S MSSQLSvc/node1.abc.com abc\administrator setspn -S MSSQLSvc/node1.abc.com:1433 abc\administrator MSSQLSvc/node2.abc.com MSSQLSvc/node2.abc.com:1433 setspn -S MSSQLSvc/node1.abc.com abc\testuser04 setspn -S MSSQLSvc/node1.abc.com:1433 abc\testuser04 Setspn -X to look for duplicates Setspn -D to delete an spn Setspn -L to list all spns Open a command prompt on a server that has the Windows Support Tools installed, and execute the following commands: setspn -A MSSQLSvc/MachineName:port domain\SQL Server Account setspn -A MSSQLSvc/MachineName.FQDN:port domain\SQL Server Account After the commands have successfully executed, you can verify the SPNs were set by executing the following command: setspn -L domain\SQL Server Account

Thanks for Sharing such a nice script ❤

Thank you for sharing

USE [MASTER] GO /****** OBJECT: STOREDPROCEDURE [DBO].[JOBSTATUS_REPORT] SCRIPT DATE: 5/4/2023 5:21:11 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE or Alter PROCEDURE [DBO].[JOBSTATUS_REPORT] AS BEGIN SET NOCOUNT ON SELECT @@SERVERNAME as SERVERNAME, --[SJOB].[JOB_ID] AS [JOBID], [SJOB].[NAME] AS [JOBNAME] , CASE WHEN [SJOBH].[RUN_DATE] IS NULL OR [SJOBH].[RUN_TIME] IS NULL THEN NULL ELSE CAST( CAST([SJOBH].[RUN_DATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_TIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LASTRUNDATETIME] , CASE [SJOBH].[RUN_STATUS] WHEN 0 THEN 'FAILED' WHEN 1 THEN 'SUCCEEDED' WHEN 2 THEN 'RETRY' WHEN 3 THEN 'CANCELLED' WHEN 4 THEN 'RUNNING' -- IN PROGRESS END AS [LASTRUNSTATUS] , STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_DURATION] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LASTRUNDURATION],-- (HH:MM:SS)], -- [SJOBH].[MESSAGE] AS [LASTRUNSTATUSMESSAGE], CASE WHEN ENABLED = 1 THEN 'ENABLED' ELSE 'DISABLED' END AS [JOBSTATUS] , CASE [SJOBSCH].[NEXTRUNDATE] WHEN 0 THEN NULL ELSE CAST( CAST([SJOBSCH].[NEXTRUNDATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBSCH].[NEXTRUNTIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NEXTRUNDATETIME] --DROP TABLE #AGENT_JOBSTAUS INTO #AGENT_JOBSTAUS FROM [MSDB].[DBO].[SYSJOBS] AS [SJOB] LEFT JOIN ( SELECT [JOB_ID] , MIN([NEXT_RUN_DATE]) AS [NEXTRUNDATE] , MIN([NEXT_RUN_TIME]) AS [NEXTRUNTIME] FROM [MSDB].[DBO].[SYSJOBSCHEDULES] GROUP BY [JOB_ID] ) AS [SJOBSCH] ON [SJOB].[JOB_ID] = [SJOBSCH].[JOB_ID] LEFT JOIN ( SELECT [JOB_ID] , [RUN_DATE] , [RUN_TIME] , [RUN_STATUS] , [RUN_DURATION] , [MESSAGE] , ROW_NUMBER() OVER ( PARTITION BY [JOB_ID] ORDER BY [RUN_DATE] DESC, [RUN_TIME] DESC ) AS ROWNUMBER FROM [MSDB].[DBO].[SYSJOBHISTORY] WHERE [STEP_ID] = 0 ) AS [SJOBH] ON [SJOB].[JOB_ID] = [SJOBH].[JOB_ID] AND [SJOBH].[ROWNUMBER] = 1 ORDER BY [JOBNAME] DECLARE @HTML NVARCHAR(MAX) SET @HTML = '' SET @HTML = '<HTML><BODY> <TABLE BORDER = 2 CELLPADDING =5> <TR BGCOLOR = PINK> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">SERVERNAME </FONT></TH> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">JOBNAME </FONT></TH> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">LASTRUNDATETIME</FONT></TH> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">LASTRUNSTATUS</FONT></TH> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">LASTRUNDURATION</FONT></TH> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">JOBSTATUS</FONT></TH> <TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">NEXTRUNDATETIME</FONT></TH> </TR> ' --+ @HTML SELECT @HTML = @HTML + '<TR> <TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),SERVERNAME ))) + '</FONT></TD>' + '<TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBNAME ))) + '</FONT></TD>' + '<TD >' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDATETIME ))),'NEVER') + '</FONT></TD>'+ CASE WHEN LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS)))='FAILED' THEN '<TD VALIGN="TOP" BGCOLOR = RED><B>'+'FAILED'+' </B></TD>' ELSE '<TD VALIGN="TOP" BGCOLOR =LIGHTGREEN ><B><FONT SIZE=2 FACE="CALIBRI">'+ ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS))),'NEVER') + '</FONT></TD>' END + '<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDURATION ))),'NEVER') + '</FONT></TD>'+ '<TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBSTATUS ))) + '</FONT></TD>'+ '<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),NEXTRUNDATETIME ))),'NEVER') + '</FONT></TD></TR>' FROM #AGENT_JOBSTAUS WHERE LASTRUNSTATUS = 'FAILED' AND JOBSTATUS NOT IN ('DISABLED') ORDER BY JOBSTATUS ASC ,LASTRUNDATETIME SELECT @HTML = @HTML + '<TR> <TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),SERVERNAME ))) + '</FONT></TD>' + '<TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBNAME ))) + '</FONT></TD>' + '<TD >' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDATETIME ))),'NEVER') + '</FONT></TD>'+ CASE WHEN LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS)))='FAILED' THEN '<TD VALIGN="TOP" BGCOLOR = RED><B>'+'FAILED'+' </B></TD>' ELSE '<TD VALIGN="TOP" BGCOLOR =LIGHTGREEN ><B><FONT SIZE=2 FACE="CALIBRI">'+ ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS))),'NEVER') + '</FONT></TD>' END + '<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDURATION ))),'NEVER') + '</FONT></TD>'+ '<TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBSTATUS ))) + '</FONT></TD>'+ '<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),NEXTRUNDATETIME ))),'NEVER') + '</FONT></TD></TR>' FROM #AGENT_JOBSTAUS WHERE LASTRUNSTATUS <> 'FAILED' OR LASTRUNSTATUS IS NULL AND JOBSTATUS NOT IN ('DISABLED') ORDER BY JOBSTATUS DESC, LASTRUNSTATUS DESC,LASTRUNDATETIME SELECT @HTML = @HTML + ' </TABLE></BODY></HTML>' PRINT @HTML --DECLARE @CC AS NVARCHAR(256) DECLARE @@SUBJECT AS NVARCHAR(256) Set @@SUBJECT = 'Daily_ job_Monitor report of_' + @@SERVERNAME --SET @CC = MUSICANDR@GMAIL.COM' IF EXISTS(SELECT TOP 1 * FROM #AGENT_JOBSTAUS) --SET @CC = 'MUSCICANDR@GMAIL.COM;MUSICNADR@GMAIL.COM' EXEC MSDB.DBO.SP_SEND_DBMAIL @PROFILE_NAME = 'OUTLOOK' ,@RECIPIENTS = 'MUSIC@GMAIL.COM' ,@BODY = @HTML ,@SUBJECT = @@SUBJECT ,@BODY_FORMAT = 'HTML' DROP TABLE #AGENT_JOBSTAUS END GO

This is a good script, however the report doesn't show the servername. This will be helpful in the event you have mutliple SQL Servers. How do we add the servername to this report please?