Listener entry in sys.availability_group_listeners exists even after related Availability group is deleted

Environment

Alwayson_Visio.JPG

-> A request comes in to drop the existing Always-on availability group JBDB from above environment.

-> Alwayson Availability group should be deleted/dropped by connecting to Primary Replica JBAG1 using SQL Server Management Studio.

-> But, someone tried deleting the Availability group from SQL Server management studio connected using the LISTENER JBAPP and got below error,

TITLE: Microsoft SQL Server Management Studio
——————————
Drop failed for Availability Group ‘JBDB’. (Microsoft.SqlServer.Management.SDK.TaskForms)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17224.0+((SSMS_Rel_17_4).180212-1043)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+AvailabilityGroup&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
——————————
The network path was not found
——————————
BUTTONS:
OK
——————————

Blog50_1.JPG

-> When “Always On High Availability” folder for SQL Server Instance JBAG1 which is the primary is refreshed from SQL Server management studio, “Availability group” JBDB is not available.

-> But you will see Availability group JBDB “Resolving” at the secondary replica “JBAG2”. At this point you delete the Availability group JBDB from your secondary replica JBAG2 from SSMS.

-> When connected to cluster administrator (cluadmin.msc), you will not see roles related to Availability group JBDB visible.

-> Lets try creating another Availability group with same name JBDB that contains listener JBAPP. It fails with below error,

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Availability Group ‘JBDB’. (Microsoft.SqlServer.Management.HadrModel)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17224.0+((SSMS_Rel_17_4).180212-1043)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroup&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Failed to create availability group ‘JBDB’, because a Windows Server Failover Cluster (WSFC) group with the specified name already exists. The operation has been rolled back successfully. To retry creating an availability group, either remove or rename the existing WSFC group, or retry the operation specifying a different availability group name. (Microsoft SQL Server, Error: 41171)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=41171&LinkId=20476
——————————
BUTTONS:
OK
——————————

Blog50_2.JPG

-> The error advises us that the ROLE JBDB is already exists in cluster administrator. But we have checked it and it is not present.

-> Execute below query on JBAG1 which was acting as the primary replica when Availability group JBDB was deleted from SSMS using Listener name,


select * from sys.availability_group_listeners
go
select * from sys.availability_groups

Blog50_3.JPG

-> You see an orphaned listener entry under system table sys.availability_group_listeners. But no corresponding entries in sys.availability_groups.

-> Lets first try restarting the SQL Services on JBAG1 and then try running the below queries,

 

select * from sys.availability_group_listeners
go
select * from sys.availability_groups 

-> The orphaned entry is no longer present in sys.availability_group_listeners after the SQL Server restart.

-> Lets try creating Availability group with same name JBDB that contains listener JBAPP and it works fine this time.

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.

Identifying databases that are no longer used

-> It becomes mandatory to find which databases are being used on a SQL Server instance when planning for below,

<> SQL Server Migration.
<> Consolidating several SQL Server instances to fewer.
<> Downsizing a Database Server and much more.

->  There are many ways this can be achieved. The best way according to me is to use profiler trace and follow below procedure,

-> Schedule the below script as a SQL agent job around 12:00 AM on each server that requires monitoring,


-- Start trace

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
declare @filename nvarchar(255)
set @filename = '\\SERVER1\JBS$\SERVERNAME\'
declare @filename1 nvarchar(255)
set @filename1 = 'JBS_DB_In_use_Trace'+convert(varchar(25),getdate())
set @filename1 = REPLACE(@filename1,' ','_')
set @filename1 = REPLACE(@filename1,':','_')
set @filename1 = @filename + @filename1

if exists(select * FROM ::fn_trace_getinfo(default) where CONVERT(sysname, value) like '%'+@filename+'%')
begin
goto error
end
else
begin
exec @rc = sp_trace_create @TraceID output, 2, @filename1, @maxfilesize, NULL
if (@rc != 0) goto error
end

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1024
exec sp_trace_setevent @TraceID, 10, 8, @on
--exec sp_trace_setevent @TraceID, 10, 1, @on --Textdata
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
--exec sp_trace_setevent @TraceID, 12, 1, @on --Textdata
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1c3d4c0b-4415-4b1f-9cea-3bff55b961bc'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'master'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'msdb'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'model'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'tempdb'
exec sp_trace_setfilter @TraceID, 35, 0, 1, NULL
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
Go

-> Schedule the below script as a sql agent job at 11:59:59 PM on each server that requires monitoring.


--Stop Trace
declare @TraceID int
declare @filename nvarchar(255)
set @filename = '\\SERVER1\JBS$\SERVERNAME\'
set @TraceID  = (select TraceID FROM ::fn_trace_getinfo(default) where CONVERT(sysname, value) like '%'+@filename+'%')
if (@TraceID IS NOT NULL)
begin
EXEC sp_trace_setstatus @traceid = @TraceID, @status = 0;
EXEC sp_trace_setstatus @traceid = @TraceID, @status = 2;
end

-> Allow this trace to run for appropriate days. In my case it was run for 40 days.

-> Once the traces are run for appropriate days. Disable/Delete the two (2) jobs from all servers that was previously created.

-> Copy created trace files to Test/Development server for processing the trace files.

-> Insert data onto table from the created trace files using below command,


SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename Into  FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_15_2018__12_00AM.trc', default)

insert into  SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_16_2018_12_00AM.trc', default)
insert into  SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_17_2018_12_00AM.trc', default)
insert into  SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_18_2018_12_00AM.trc', default)
	.
	.
	.
insert into  SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceApr_27_2018_12_00AM.trc', default)

-> Once the data is loaded into the table. Use below query to get the contents from the loaded table,


select distinct Applicationname,databasename,loginname from  where loginname'' order by databasename

-> This uses a lightweight server side trace and doesn’t collect unnecessary data.

-> All rows returned indicates in the output indicates what databases are being used on each of the server. Databases not in the output are databases that are not used within the time frame the traces were run.

-> Remember to check the output for Applicationname and Loginname. SQL Server agent job for database maintenance such as backups, Index maintenance and integrity checks should not be considered as user queries.

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.

TSQL query to find Enterprise features availability in a SQL Server Instance

-> Below query can be used to find if a database instance uses Enterprise features or not.


set nocount on
Declare @name varchar(255)
declare @sql varchar(max)
create table #Features(
ServerName varchar(29),
DatabaseName varchar(100),
feature_name varchar(100),
feature_id int)
DECLARE db_cursor CURSOR  FOR  
SELECT name  FROM sys.databases 
OPEN db_cursor   

DECLARE DatabaseBackup_cursor CURSOR FOR
select name from sys.databases where state=0
OPEN DatabaseBackup_cursor
FETCH NEXT FROM DatabaseBackup_cursor into @name
WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'SELECT @@servername,''['+@name+']'' as DatabaseName,* 
    FROM ['+@name+'].sys.dm_db_persisted_sku_features
    ORDER BY feature_name;'
	
insert into #Features
exec (@sql)
FETCH NEXT FROM DatabaseBackup_cursor into @name
END
CLOSE DatabaseBackup_cursor
DEALLOCATE DatabaseBackup_cursor
CLOSE db_cursor   
DEALLOCATE db_cursor
select * from #Features
drop table #Features

-> I use this query predominantly to see if existing Enterprise edition SQL Server instances use Enterprise features or not. This allows me to see if a SQL Server instance can be moved to Standard edition from Enterprise if there are no enterprise features used.

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.