Script to get list of databases which are part of a specific availability group in AlwaysOn

How to Know which Database is Local to Which availability Group

=======================================================

Suppose we have 2 Availability group Named   “SQL000000AG01” and  “ SQL000000AG02” under 2 physical node “Node1” and “node2”, where

SQL000000AG01 is a   current owner of  “Node1” and  SQL000000AG02 is  a  current owner of  “Node2”.

And situation is something like AlwaysOn is configured  in read-write on primary and Read-only on secondary.

NP: Please change the Name of availability_groups below marked in Red*

  

This Query will give you a list of databases which are part of  a specific  availability group.

exec sp_executesql N'

      select * into #tmpag_availability_groups from master.sys.availability_groups

      select group_id, replica_id,replica_server_name,availability_mode into #tmpdbr_availability_replicas from master.sys.availability_replicas

      select replica_id,group_database_id,database_name,is_database_joined,is_failover_ready into #tmpdbr_database_replica_cluster_states from master.sys.dm_hadr_database_replica_cluster_states

       select * into #tmpdbr_database_replica_states from master.sys.dm_hadr_database_replica_states

        select replica_id,role,is_local into #tmpdbr_availability_replica_states from master.sys.dm_hadr_availability_replica_states

        select ars.role, drs.database_id, drs.replica_id, drs.last_commit_time into #tmpdbr_database_replica_states_primary_LCT from  #tmpdbr_database_replica_states as drs left join #tmpdbr_availability_replica_states ars on drs.replica_id = ars.replica_id where ars.role = 1

  SELECT

AR.replica_server_name AS [AvailabilityReplicaServerName],

dbcs.database_name AS [AvailabilityDatabaseName],

dbcs.group_database_id AS [AvailabilityDateabaseId],

AR.group_id AS [AvailabilityGroupId],

AG.name AS [AvailabilityGroupName],

AR.replica_id AS [AvailabilityReplicaId],

ISNULL(dbr.database_id, 0) AS [DatabaseId],

ISNULL(dbr.end_of_log_lsn, 0) AS [EndOfLogLSN],

CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END  AS [EstimatedDataLoss],

ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1) AS [EstimatedRecoveryTime],

ISNULL(dbr.filestream_send_rate, -1) AS [FileStreamSendRate],

ISNULL(dbcs.is_failover_ready, 0) AS [IsFailoverReady],

ISNULL(dbcs.is_database_joined, 0) AS [IsJoined],

arstates.is_local AS [IsLocal],

ISNULL(dbr.is_suspended, 0) AS [IsSuspended],

ISNULL(dbr.last_commit_lsn, 0) AS [LastCommitLSN],

ISNULL(dbr.last_commit_time, 0) AS [LastCommitTime],

ISNULL(dbr.last_hardened_lsn, 0) AS [LastHardenedLSN],

ISNULL(dbr.last_hardened_time, 0) AS [LastHardenedTime],

ISNULL(dbr.last_received_lsn, 0) AS [LastReceivedLSN],

ISNULL(dbr.last_received_time, 0) AS [LastReceivedTime],

ISNULL(dbr.last_redone_lsn, 0) AS [LastRedoneLSN],

ISNULL(dbr.last_redone_time, 0) AS [LastRedoneTime],

ISNULL(dbr.last_sent_lsn, 0) AS [LastSentLSN],

ISNULL(dbr.last_sent_time, 0) AS [LastSentTime],

ISNULL(dbr.log_send_queue_size, -1) AS [LogSendQueueSize],

ISNULL(dbr.log_send_rate, -1) AS [LogSendRate],

ISNULL(dbr.recovery_lsn, 0) AS [RecoveryLSN],

ISNULL(dbr.redo_queue_size, -1) AS [RedoQueueSize],

ISNULL(dbr.redo_rate, -1) AS [RedoRate],

ISNULL(AR.availability_mode, 2) AS [ReplicaAvailabilityMode],

ISNULL(arstates.role, 3) AS [ReplicaRole],

ISNULL(dbr.suspend_reason, 7) AS [SuspendReason],

ISNULL(CASE dbr.log_send_rate WHEN 0 THEN -1 ELSE CAST(dbr.log_send_queue_size AS float) / dbr.log_send_rate END, -1) AS [SynchronizationPerformance],

ISNULL(dbr.synchronization_state, 0) AS [SynchronizationState],

ISNULL(dbr.truncation_lsn, 0) AS [TruncationLSN]

FROM

#tmpag_availability_groups AS AG

INNER JOIN #tmpdbr_availability_replicas AS AR ON AR.group_id=AG.group_id

INNER JOIN #tmpdbr_database_replica_cluster_states AS dbcs ON dbcs.replica_id = AR.replica_id

LEFT OUTER JOIN #tmpdbr_database_replica_states AS dbr ON dbcs.replica_id = dbr.replica_id AND dbcs.group_database_id = dbr.group_database_id

LEFT OUTER JOIN #tmpdbr_database_replica_states_primary_LCT AS dbrp ON dbr.database_id = dbrp.database_id

INNER JOIN #tmpdbr_availability_replica_states AS arstates ON arstates.replica_id = AR.replica_id

WHERE

(AG.name=@_msparam_0) and

arstates.is_local=1

ORDER BY

[AvailabilityReplicaServerName] ASC,[AvailabilityDatabaseName] ASC

      DROP TABLE #tmpdbr_availability_replicas

      DROP TABLE #tmpdbr_database_replica_cluster_states

      DROP TABLE #tmpdbr_database_replica_states

      DROP TABLE #tmpdbr_database_replica_states_primary_LCT

      DROP TABLE #tmpdbr_availability_replica_states

      drop table #tmpag_availability_groups

',N'@_msparam_0 nvarchar(4000)',@_msparam_0=SQL000000AG01'

New Dynamic Management Views (DMVs)

New Dynamic Managemtn Views in SQL Server 2012
------------------------------
1)        select * from sys.dm_server_services  

This query fetches information about the current SQL Server instance's Windows services, including information about whether the services are clustered, as shown in the following screenshot:

2)        select * from sys.dm_server_registry 

A. Display the SQL Server services

The following example returns registry key values for the SQL Server and SQL Server Agent services for the current instance of SQL Server.

SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE key_name LIKE N'%ControlSet%'; B. Display the SQL Server Agent registry key values The following example returns the SQL Server Agent registry key values for the current instance of SQL Server.

SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE key_name LIKE N'%SQLAgent%'; C. Display the current version of the instance of SQL Server

The following example returns the version of the current instance of SQL Server.

SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE value_name = N'CurrentVersion';

D. Display the parameters passed to the instance of SQL Server during startup

The following example returns the parameters that are passed to the instance of SQL Server during startup.

SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE registry_key LIKE N'%Parameters'; E.Return network configuration information for the instance of SQL Server The following example returns network configuration values for the current instance of SQL Server.

SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE registry_key LIKE N'%ControlSet%';

The new OS Volume Stats DMV
Finally there is a DMV which was quietly introduced in SQL Server 2008 R2, but is worthy of another mention here, as it returns information about the file system for any given file for any database.
This is particularly useful to the DBA who runs their SQL Server on a Storage Area Network (SAN), which is becoming more commonplace. The following example returns data about the log file (file_id: 2) for the master database (database_id: 1):

SELECT * FROM sys.dm_os_volume_stats (1,2)

Run this query and you will see something similar to the following screenshot, which details the file type, size and how much space is remaining:

Is Unique-NonClustered index also is HEAP Table

Somebody questioned me long back that is Unique-NonClustered Index is also a HEAP Table.

I said yes..

Explanation below:

USE [AdventureWorks2012_old]

GO

/****** Object:  Table [dbo].[Employee]    Script Date: 2/25/2013 11:06:23 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Employee](

       [Empid] [int] IDENTITY(1,1) NOT NULL,

       [EmpFName] [char](10) NULL,

       [EmpLName] [char](10) NULL,

       [Salary] [int] NULL,

CONSTRAINT [IX_Employee] UNIQUE NONCLUSTERED

(

       [Empid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

go

select OBJECT_NAME(object_id),type_desc from sys.indexes where type_desc ='HEAP'

  go

Cannot truncate table 'XXXXXX' because it is being referenced by a FOREIGN KEY constraint.

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'XXXXX' because it is being referenced by a FOREIGN KEY constraint.

This is Automation script, this will
Backing up Foreign Key Definitions then Dropping Foreign Keys then Truncating Tables then Re-creating Foreign Keys

SET NOCOUNT ON

-- GLOBAL VARIABLESDECLARE @i intDECLARE
@Debug bitDECLARE
@Recycle bitDECLARE
@Verbose bitDECLARE
@TableName varchar(80)DECLARE @ColumnName varchar(80)DECLARE @ReferencedTableName varchar(80)DECLARE @ReferencedColumnName varchar(80)DECLARE @ConstraintName varchar(250)DECLARE @CreateStatement varchar(max)DECLARE @DropStatement varchar(max) DECLARE @TruncateStatement varchar(max)DECLARE @CreateStatementTemp varchar(max)DECLARE @DropStatementTemp varchar(max)DECLARE @TruncateStatementTemp varchar(max)DECLARE @Statement varchar(max)-- 1 = Will not execute statements SET @Debug = 0
-- 0 = Will not create or truncate storage table-- 1 = Will create or truncate storage tableSET @Recycle = 0
-- 1 = Will print a message on every stepset @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'-- Drop Temporary tablesDROP TABLE #FKs-- GET FKsSELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,OBJECT_NAME(constraint_object_id) as ConstraintName,OBJECT_NAME(parent_object_id) as TableName,clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,clm2.name as ReferencedColumnNameINTO #FKsFROM sys.foreign_key_columns fkJOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_idJOIN sys.columns clm2ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_idWHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')ORDER BY OBJECT_NAME(parent_object_id)　-- Prepare Storage TableIF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')BEGINIF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'-- CREATE STORAGE TABLE IF IT DOES NOT EXISTSCREATE TABLE [Internal_FK_Definition_Storage] (ID int not null identity(1,1) primary key,FK_Name varchar(250) not null,FK_CreationStatement varchar(max) not null,FK_DestructionStatement varchar(max) not null,Table_TruncationStatement varchar(max) not null)
END ELSEBEGINIF @Recycle = 0
BEGINIF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'-- TRUNCATE TABLE IF IT ALREADY EXISTSTRUNCATE TABLE [Internal_FK_Definition_Storage]
ENDELSEPRINT '1. Process specific table will be recycled from previous execution...'ENDIF @Recycle = 0
BEGINIF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'-- Fetch and persist FKs WHILE (@i <= (SELECT MAX(ID) FROM #FKs))BEGINSET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTempSET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'ENDEND
ELSE
PRINT '2. Backup up was recycled from previous execution...'IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'-- DROP FOREING KEYSSET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))BEGINSET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)IF @Debug = 1
PRINT @StatementELSEEXEC(@Statement)SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'END
IF @Verbose = 1
PRINT '4. Truncating Tables...'-- TRUNCATE TABLESSET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))BEGINSET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)IF @Debug = 1
PRINT @StatementELSEEXEC(@Statement)SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @StatementENDIF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'-- CREATE FOREING KEYSSET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))BEGINSET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)IF @Debug = 1
PRINT @StatementELSEEXEC(@Statement)SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'ENDIF @Verbose = 1
PRINT '6. Process Completed'