-> Consider you are working on a blocking issue where the application team is advising that the blocking is highly intermittent.
-> Application team is advising that they have a third party monitoring solution and that shows that the blocking is happening on a page and below is the detail,
-> We need to find what Table this Page belongs to. We can utilize DMV sys.dm_db_page_info to get this info. Below query can exactly provide tis,
select DB_NAME(database_id) DBName,page_id,OBJECT_NAME(object_id) TableName from sys.dm_db_page_info (8,1,10029,DEFAULT)
--select DB_NAME(database_id) DBName,page_id,OBJECT_NAME(object_id) TableName from sys.dm_db_page_info (DBID,FileID,PAGEID,MODE)
-> Please note that DMV sys.dm_db_page_info is only available in SQL Server 2019 and above.
-> If you are below SQL Server 2019, then you will need to use DBCC PAGE to get the details of table name from Page id. But DBCC PAGE is an undocumented command.
Hope this helps!!!
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
Below code can be used to create HTML output as a file from TSQL / SQL Server queries,
/*
Script : TSQL_Using_HtmlFile.sql
Version : 1.0
Date : 14/03/2021
Tested : SQL server 2008 and above
Requirement : User running the script should be a SYSADMIN.
sp_configure 'xp_cmdshell' and 'Ole Automation Procedures' should be enabled.
Errors or BUGS : Please send an email to Vivek Janakiraman (jvivek2k1@yahoo.com)
Author : Vivek Janakiraman
*/
use tempdb
go
Set NoCount On;
SET concat_null_yields_null OFF;
Declare @Body nvarchar(max)
, @TableHead varchar(max)
, @TableTail varchar(max)
Declare @file nvarchar(max)
Declare @filelocation nvarchar(max)
Declare @filename nvarchar(max)
Declare @Final_filename nvarchar(max)
Declare @Source nvarchar(max)
DECLARE @OLE INT
DECLARE @FileID INT
DECLARE @file_temp varchar(max)
exec xp_cmdshell 'md c:\temp'
set @filelocation = 'c:\temp\'
set @Final_filename = @filelocation + 'counter.txt'
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {align=center;padding-left:5px;padding-right:5px;padding-top:3px;padding-bottom:3px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0 align=center WIDTH=1700>' +
'<tr bgcolor=C6CFFF><td align=center><b>SQL REPORT</b></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center><u><b>Object Details:</b></u></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'</tr></table>';
Select @Body = '<table style=\"font-family: Calibri; font-size:85%; \" ALIGN=center BORDER=1 CELLPADDING=1 CELLSPACING=1 WIDTH=1700 WIDTH=1700>
<tr bgcolor=FFEFD8>
<td><b>ObjectName</b></td>
<td><b>type_desc</b></td>
</tr>'
Select @Body = @Body + (select top 10
name As [TD]
,type_desc As [TD]
from sys.objects
For XML raw('tr'), Elements)
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @TableHead + @Body + @TableTail
set @Final_filename = @filelocation + 'Vivek.html'
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @Final_filename, 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Body
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
Below file can be viewed from location C:\temp.
2. Below code can be used to create HTML output as an email from TSQL / SQL Server queries,
/*
Script : TSQL_Using_Html_Email.sql
Version : 1.0
Date : 14/03/2021
Tested : SQL server 2008 and above
Requirement : User running the script should be a SYSADMIN.
Errors or BUGS : Please send an email to Vivek Janakiraman (jvivek2k1@yahoo.com)
Author : Vivek Janakiraman
*/
use tempdb
go
Set NoCount On;
SET concat_null_yields_null OFF;
Declare @Body nvarchar(max)
, @TableHead varchar(max)
, @TableTail varchar(max)
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {align=center;padding-left:5px;padding-right:5px;padding-top:3px;padding-bottom:3px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0 align=center WIDTH=1700>' +
'<tr bgcolor=C6CFFF><td align=center><b>SQL REPORT</b></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center><u><b>Object Details:</b></u></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'</tr></table>';
Select @Body = '<table style=\"font-family: Calibri; font-size:85%; \" ALIGN=center BORDER=1 CELLPADDING=1 CELLSPACING=1 WIDTH=1700 WIDTH=1700>
<tr bgcolor=FFEFD8>
<td><b>ObjectName</b></td>
<td><b>type_desc</b></td>
</tr>'
Select @Body = @Body + (select top 10
name As [TD]
,type_desc As [TD]
from sys.objects
For XML raw('tr'), Elements)
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @TableHead + @Body + @TableTail
EXEC msdb.dbo.sp_send_dbmail @body = @Body
,@body_format = 'HTML'
,@profile_name = N'DBMAIL'
,@recipients = N'jvivek2k1@yahoo.com'
,@Subject = N'Object Details'
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
-> I had a requirement to execute jobs only in primary replica.
-> To all jobs that should only be executed on Always On primary replica. I added 1 step as the first step of the job and another step as last step of that job.
-> 1st step of these jobs should execute below TSQL Query,
if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = '<AGNAME>'
and ars.is_local = 1) = 'PRIMARY'
begin
Print 'This is Primary Replica'
end
else
begin
RAISERROR('This job should not run in Secondary',16,1)
end
-> For 1st step,
On success action : Go to the next step On failure action: Go to step: [n] Last_Step ( It should basically go to the last step)
-> Last step of the job should execute the below TSQL,
print 'Job_Completion'
-> For Last step,
On success action : Quit the job reporting Success On failure action: Quit the job reporting failure
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
