Tag Archives: Tsql

Standard

Posted by

Posted on

December 19, 2023

Posted under

AlwaysON, Tsql

Comments

Leave a comment

Performing Always On Availability Group Health Check for a Single Database

Introduction šŸŒ

Always On Availability Groups (AG) in SQL Server provide high availability and disaster recovery solutions for databases. Ensuring the health of your AG is crucial to maintaining a resilient database environment. In this blog post, we’ll guide you through a T-SQL script that performs a comprehensive health check for a single database within an AG. This script will be executed on the primary replica and includes valuable information such as synchronization state, database state, and secondary lag in seconds.

Requirements šŸ› ļø

Before diving into the health check script, make sure you meet the following prerequisites:

  1. SQL Server Availability Group: Ensure that your SQL Server instance is configured with an Availability Group containing the target database.
  2. Permission: The account executing the script should have the necessary permissions to query the sys.dm_hadr_database_replica_states dynamic management view.
  3. Primary Replica: Run the script on the primary replica of the Availability Group.

T-SQL Script šŸ“œ

Here’s the T-SQL script that will provide a detailed health check for a specific database within your Availability Group:

select 

    db_name(database_id) as [Database],

    is_primary_replica,

    synchronization_state_desc,

    database_state_desc,

    is_suspended,

    suspend_reason_desc,

    recovery_lsn,

    truncation_lsn,

    last_sent_lsn,

    last_sent_time,

    last_received_lsn,

    last_received_time,

    last_hardened_lsn,

    log_send_queue_size,

    log_send_rate,

    redo_queue_size,

    redo_rate,

    end_of_log_lsn,

    last_commit_lsn,

    last_commit_time,

    secondary_lag_seconds 

from 

    sys.dm_hadr_database_replica_states

This script retrieves essential information about the specified database, such as synchronization state, log queue details, and secondary replica lag in seconds.

Conclusion šŸŽ‰

Regularly monitoring the health of your Always On Availability Group is fundamental to ensuring the stability and reliability of your SQL Server databases. By utilizing the provided T-SQL script, you can quickly assess the state of a single database within the AG, identify any potential issues, and take proactive measures to maintain a robust database infrastructure.

Remember to schedule periodic health checks to catch any anomalies early, minimizing the risk of downtime and data loss in your SQL Server environment. Stay vigilant, and may your databases always be available and resilient! šŸ’Ŗ

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.

Standard

Posted by

Posted on

March 14, 2021

Posted under

Tsql

Comments

Leave a comment

Create HTML from TSQL / SQL Server Queries

  1. 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.