AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserName varchar(128)

DECLARE @sql varchar(8000), @TABLE_NAME sysname
/*DB Cursor to select all tables */
SELECT table_schema + '.'+ TABLE_NAME 
AND TABLE_NAME!= 'sysdiagrams'

OPEN db_cursor 


EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = 
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000), 
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = ''' + @TABLE_NAME+ '''
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
/*Action */
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
select @Type = ''I''
select @Type = ''D''
/*get list of columns */
select * into #ins from inserted
select * into #del from deleted
/*Get primary key columns for full outer join */
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
where pk.TABLE_NAME = @TableName
/*Get primary key fields select for insert */
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' 
where pk.TABLE_NAME = @TableName
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
where pk.TABLE_NAME = @TableName 
if @PKCols is null
raiserror(''no PK on table %s'', 16, -1, @TableName)
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)'' 
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + '' is null)'' 
exec (@sql)

CLOSE db_cursor 
DEALLOCATE db_cursor

Replication may fail due to faulty distribution agent jobs. When we try to start the Distribution Agent job; the distribution agent job will fail and and job history details the error as follows.

Error Message:

Agent message code 21036. Another distribution agent for the subscription(s) is running or the server is working on a previous request by the same agent.

Work Around:

Find the SPID /processes running related to the distribution agent job.

But before that make sure to stop the job if running. (Most of the cases it would have been stopped)

select * from master..sysprocesses where program_name = ‘Name_of_the_agent_job’;

Note: Make sure to run this on the Distribution server.

This will list all the active processes related to the agent job if any. Make sure to kill that process and recheck again if any agent processes are active or not. Once confirmed that there are no processes running related to the agent job we can restart the agent job.

Once, this is done. You may see that distribution job are working fine.

Scenario: Transaction Replication Distributor job was failing/retrying with error message as follows:

Error Message:

The process is running and is waiting for a response from the server.


I started investigating this issue by first logging on to the Production OLTP/ Publisher server and checking the following parameters:

  1. I verified the synchronization status of the subscriber and found that there was an issue with it. Below is the message which was shown in the synchronization status:
  2. Then on Activity Monitor found that there were a few processes in which were sleeping and whose CPU as well as IO utilization were high.
  3. So, I ran this query as follows to identify which SPID is causing high CPU & I/O Utilization on distributor/subscriber server.
  4. I found one particular SPID was in sleeping state and blocking other distributor/Subscriber jobs not to run.
    Select * from master..sysprocesses where blocked <> 0
  5. Then, I have killed that particular Header Blocking SPID which made Subscriber job to start replicating data to subscriber tables.
KILL 55 --> Header Blocker (SPID on Blocked by Column)

Then verify synchronization status of the subscriber and found that the data had started synchronizing as follows:

110 Transactions with 2295 Commands were delivered.
57 Transactions with 8767 commands were delivered.


Linux Commands for Big Data Professionals

ls Command:
ls command is used to list down files.

a) List Files using ls with no option

[jmarun@jmarun BD]# ls

a.txt  b.txt

[jmarun@jmarun BD]#

b) List Files With option –l

[jmarun@localhost ~]$ ls -l

-rw-rw-r–. 1 jmarun jmarun    0 Feb 11 18:51 jmarun.txt

-rwxr-xr-x. 1 jmarun jmarun    0 Feb 12 18:22 diva.txt

[jmarun@localhost ~]$

c) View Hidden Files

List all files including hidden file starting with ‘.‘.

[jmarun@jmarun ~]$ ls -al

drwx——. 27 jmarun jmarun 4096 Feb 21 14:22 .

drwxr-xr-x.  3 root    root    4096 Feb 10 13:52 ..

-rw-rw-r–.  1 jmarun jmarun    0 Feb 11 18:51 jmarun.txt

-rwxr-xr-x.  1 jmarun jmarun    0 Feb 12 18:22 diva.txt

[jmarun@localhost ~]$

d) List Files with Human Readable Format with option -lh

    With combination of -lh option, shows sizes in human readable format.

[jmarun@localhost ~]$ ls -lh

-rw-rw-r–. 1 jmarun jmarun    0 Feb 12 18:25 d1.txt

-rw-rw-r–. 1 jmarun jmarun    0 Feb 12 18:25 d2.txt

[jmarun@localhost ~]$

e) List Files and Directories with ‘/’ Character at the end

Using -F option with ls command, will add the ‘/’ Character at the end each directory.

[jmarun@localhost ~]$ ls -F

d1.txt  Desktop/     diva.txt*   Downloads/  Pictures/  Templates/  xyz/

d2.txt  jmarun.txt  Documents/  Music/      Public/    Videos/

[jmarun@localhost ~]$

f) List Files in Reverse Order

The following command with ls -r option display files and directories in reverse order.
[jmarun@localhost ~]$ ls -r

xyz Templates  Pictures  Downloads  diva.txt Desktop  d1.txt Videos  Public jmarun.txt  d2.txt
[jmarun@localhost ~]$

g) Recursively list Sub-Directories

    ls -R option will list very long listing directory trees. See an example of output of the command.

[jmarun@localhost ~]$ ls -R

d1.txt  Desktop  diva.txt   Downloads  Pictures  Templates  xyz

h) Reverse Output Order

With combination of -ltr will shows latest modification file or directory date as last.

[jmarun@localhost ~]$ ls -ltr

drwxr-xr-x. 2 jmarun jmarun 4096 Feb 10 18:55 Templates

drwxr-xr-x. 2 jmarun jmarun 4096 Feb 10 18:55 Downloads

drwxr-xr-x. 2 jmarun jmarun 4096 Feb 10 18:55 Videos

drwxr-xr-x. 2 jmarun jmarun 4096 Feb 10 18:55 Public

i) Sort Files by File Size

With combination of -lS displays file size in order, will display big in size first.

[jmarun@localhost ~]$ ls -ls

0 -rw-rw-r–. 1 jmarun jmarun    0 Feb 12 18:25 d1.txt

0 -rw-rw-r–. 1 jmarun jmarun    0 Feb 12 18:25 d2.txt

4 drwxr-xr-x. 4 jmarun jmarun 4096 Feb 11 18:03 Desktop

0 -rw-rw-r–. 1 jmarun jmarun    0 Feb 11 18:51 jmarun.

j) Display Inode number of File or Directory

We can see some number printed before file / directory name. With -i options list file /directory with        inode number

[jmarun@localhost ~]$ ls -i

272870 d1.txt       272869 diva.txt   397175 Pictures   397282 xyz

272871 d2.txt       397173 Documents  397172 Public

k) Shows version of ls command

Check version of ls command.

[jmarun@localhost ~]$ ls –version

ls (GNU coreutils) 8.4

Copyright (C) 2010 Free Software Foundation, Inc

l) Show Help Page

List help page of ls command with their option.

[jmarun@localhost ~]$ ls –help

Usage: ls [OPTION]… [FILE]…

List information about the FILEs (the current directory by default).

Sort entries alphabetically if none of -cftuvSUX nor –sort.

m) List Directory Information

With ls -l command list files under directory /tmp. Wherein with -ld parameters displays information       of /tmp directory.

[jmarun@localhost ~]$ ls -l /

dr-xr-xr-x.   2 root root  4096 Feb 10 19:34 bin

dr-xr-xr-x.   5 root root  1024 Feb 10 13:52 boot

n) Display UID and GID of Files

  To display UID and GID of files and directories. use option -n with ls command.

[jmarun@localhost ~]$ ls -n

-rw-rw-r–. 1 500 500    0 Feb 12 18:25 d1.txt

-rw-rw-r–. 1 500 500    0 Feb 12 18:25 d2.txt

Copying with the cp Command
a) How do I copy files?
cp filename1 filename2 -> copies a file.

b) How do I copy recursively?    cp –r dir1 dir2

c) To see copy progress pass –v option to cp command.    cp  –v  –r  dir1 dir2

d) How do I confirm file overwriting?    cp –i dir1 dir2

e) Preserve the file permission and other attributes.
cp –p file1 file2

Deleting Files with the ‘rm’ Command

The rm command deletes the files.This command has several options,but should be used cautiously.
a) The rm command will delete one or several files from the command line.
rm file1
rm file1 file2 file3
b) One of the safer ways to use rm is through the -i or interactive option, where you’ll be asked if you want to delete the file.
rm -i file1
c) you can also force file deletion by using -f option 
rm -f file1
d) when we combine -f and -r, the recursive option , you can delete directories and all files or directories found.
rm -rf <directoryname>

Creating Directories with the ‘mkdir’ Command
The mkdir command can create one or several directories with a single command line.
a) Creating directories
mkdir <directoryName>
b) Creating multiple directories
mkdir <directoryName1> <directoryname2>
c) Creating Child under directories
mkdir temp/child
d) To build a hierarchy of directories with mkdir, you must use the -p,or parent option, for example
mkdir -p temp5/parent/child

Removing Directories with the ‘rmdir’ Command

a) The rmdir command is used to remove directories. To remove a directory, all you have to do is type

rmdir <DirectoryName>
b) Removing directories and sub directories as well
rm -rf

Renaming Files with the ‘mv’ command.
The mv command, called a rename command but know to many as a move command
a) Move the data file1 to file2 
mv <file1> <file2>
b) The mv command can work silently, or as with rm, you can use the -i (interactive) option 
mv -i <file1> <file2>

Creating Hard and Symbolic Links with the ‘ln’ Command
The ln command creates both types of links.If you use the ln command to create a hard link, you specify a second file on the command line you can use to reference the original file,for Ex
# ln file1 file2
#ln -s file1 file 2

ps command: The ps command will show information about current system process.

Ps-> The user’s currently running processes.

Ps –f -> Full listing of the user’s currently running process.

ps –ef -> Full listing of all process, except kernel process.

Ps –A -> All process, including kernel process.

Ps auxw -> wide listing sorted by percentage of CPU usage.

Last: The last command shows the history of who has logged in to the system since the wtmp file was created.

Who: The who command gives this output for each logged-in user: username,tty .login time and where the user logged in from.

W: The W command is really an extended who

Checking your installation Files

rpm –qa | grep ^x

you should receive the output similar to the following..





Installing the X Files

rpm –ivh <filename>

Moving to different directories with the cd command.

cd /usr/bin

cd ..

cd ../..

cd or cd –

Knowing where you are with the pwd command.


pwd –help

/bin/pwd   –help

Searching directories for matching files with the find command

Syntax: find where-to-look criteria what-to-do

Find /usr –name spell –print

Listing and Combining Files with the cat Command

The cat (Concatenate file) commands are used to send the contents of files to your screen.

a) cat a.txt

[abc@master]$ cat a.txt

Hello Hello Hello

Hello hello hello


b) The cat command also has a number of options. If you’d like to see your file with line numbers, you can use the n-option

#cat –n  a.txt

1  Hello Hello Hello

2  Hello hello hello

c) You can also use cat to look at several files at once.

# cat –n  test*

1  Hello Hello Hello

2  Hello hello hello

[root@localhost ~]# cat a.txt


[root@localhost ~]#

[root@localhost ~]# cat b.txt




[root@localhost ~]#

d) As you can see, cat has also include a second file in its output.

[root@localhost ~]# cat a.txt b.txt





[root@localhost ~]#

cat > c.txt
e) To see the numbers using cat
# cat -n jmarun.txt
100     jmarun 20000
200     diiia   10000
399     ksjjkj  30999[

Reading the files with the ‘more’ command

[root@localhost ~]# more c.txt




[root@localhost ~]#

Browsing Files with the ‘less’ command.

less c.txt

Reading the Beginning or End of Files with the head and tail Commands.

head -5 /usr/man/man.txt

head -5 –q /usr/man/man.txt

tail -12 /var/log/message/a.txt

The more command is one of a family of Linux commands called pagers.

Creating Files with the ‘touch’ Command

The touch command is easy to use, and generally, there are two reasons to use it .The first reason is to create a file, and the second is to update a file’s modification date.

a) To create a file with touch,use

# touch newfile

#ls –l newfile

-rw-r–r– 1 jmarun jmarun 0 Feb 21 14:12 newfile

b) To Change time stamp

# touch –t 1225110099 newfile2

Trap: when the program is terminated before it would normal end, we can catch an exit signal.

0-      Normal termination, end of script.

1-SIGHUP -> hang up, line disconnected

2-singint-> terminal interrupts, usually ctrl+c

3- SIGQUIT -> Quit Key, Child process to die before terminating.

9-SIGKILL->kill -9 commands can’t trap this type of exit status.

15-SIGTERM  àkill command’s default action.

19-SIGSTOP->stop, usually ctrl+z

17 -> dintfunc

Ex: Kill –9 <ps Id>

df -> Report how much free disk space is available for each mount you have.

df –a  –all -> include dummy file system.

df –B  100, –block-size=SIZE -> use SIZE –byte blocks

df –h ->human readable àprint sizes in human readable format.

df –I -> list inode information instead of block usage.

Df –k -> like  –block –Size =1 k.

df –T ->Print file System Type.

du -> disk usage

du ->tells you how much disk space a file occupies.

du –a ->display  the space that each file is taking up.

Du –h -> which can make the output easier to read by displaying it in KB /M/G.

Du –sh -> The -s (for suppress or summarize) option tells du to report only the total disk space occupied by a directory tree and to suppress individual reports for its subdirectories

top àdisplays top CPU process.

The top program provides a dynamic real-time view of a running system.


Free -> displays information about free and used memory on the system.

-b,-k,-m,-g show output in bytes, KB, MB, or GB

-l show detailed low and high memory statistics

-o use old format (no -/+buffers/cache line)

-t display total for RAM + swap

-s update every [delay] seconds

-c update [count] times

-V display version information and exit

awk-> The awk command is powerful method for processing or analyzing text files,in particulat data files that are organized by lines (rows) and cloumns.

How to add user:

Useradd div  –to add user Id

Passwd div –to set password

Set Account disable date:

useradd -e {yyyy-mm-dd} {username}
useradd -e 2008-12-31 jerry

Set default password expiry:

useradd -f {days} {username}
useradd -e 2009-12-31 -f 30 jerry

How Can I see the entire users list on Linux Server?

cat /etc/passwd

Vim /etc/passwd

cat /etc/passwd | grep home | cut -d’:’ -f1

How to convert you as root, create new user and setting password.
[jmarun@localhost Desktop]$ su root

Password: <give your user passwd>

[root@localhost Desktop]# useradd diva1

[root@localhost Desktop]# passwd diva1

Changing password for user diva1.

New password:

BAD PASSWORD: it is too short

BAD PASSWORD: is too simple

Retype new password:

passwd: all authentication tokens updated successfully.

[root@localhost Desktop]#

How to add user in sudo list.

Go to vi /etc/sudoers

## Allow root to run any commands anywhere

root    ALL=(ALL)       ALL

jmarun   ALL=(ALL)   ALL

Man ->

Find files with the whereis command.

Whereis find

You can also use whereis to find only the binary version of the program with

Whereis –b find

If whereis cann’t find your request , you’ll get an empty return string, for example

Whreis foo

It will search in entire system.

Not limiting searches to known directories such as /usr/man, /usr/bin, or /usr/sbin can speed up the task of finding files.

Although whereis is faster than using find to locate programs or manual pages.

Locate is faster than whereis

Locating files with locate command.

Finding a file using locate is much faster than the find command because locate will go directly to the database file,find any matching filenames, and print its results.

Locate *.ps

Locate resides in /var/lib

Moving different directories with cd command.

cd or

cd ../.. or

cd or cd –

Knowing where you are wit pwd command.

Go to /user/local and type pwd to know your directory.

ps -ef | grep aneel

pkill -f

Link while copying the data?

cp –i test 1 test2

Getting command Summaries with whatis and apropos

Specifying other directories with ls

# ls /usr/bin

Listing Directories with the dir and vdir commands

#dir :-> this command works like the default ls command,listing the files in sorted columns

Vdir -> The vdir command works like the ls –l option, and presents a long format listing by default,

Graphic Directory listings with the tree command

# tree /var/lib

# tree –d /usr/local/netscape/

Search Inside Files with the ‘grep’ Command
grep command will help to search any words in file
Ex: cat hive.log | grep loaded [Here we are searching loaded files in log]

Compressing files with the ‘gzip’ command
# gzip file.tar

Compressing Files with the ‘compress’ Command
#compress file

To uncompress a file,use
# uncompress file.Z

Running Programs in the Background
# nohup ./ &

Checking the Connection
Using the ‘ipconfig’ Command, This Command will help to identify the IP address of your system
# ifconfig
[jmarun@localhost ~]$ ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0C:29:7F:24:89
inet addr:  Bcast:  Mask:
inet6 addr: fe80::20c:29ff:fe7f:2489/64 Scope:Link

Using the ‘netstat’ Command
The netstat command is the definitive command for checking your network activity,connections,routing tables,and other network messages and statistics.
# netstat

Using the ping Command
# ping <hostname>.com

Find Hostname of the System

How to replace One word with another word in vi:


Try clearing the outlook form cache and maybe reinstall the EV user extensions.

Perform the following steps to clear the Outlook form cache:

1. On the Tools menu, click Options, and then click the Other tab
2. Click Advanced Options
3. In the Advanced Options box, click Custom Forms
4. In the Custom Forms box, click Manage Forms
5. In the Forms Manager box, click Clear Cache


1) Logon to the server with local administrator privileges
2) Stop the SQL Server Services from services.msc
3) Open Command Prompt with administrator privileges.
4) Find the path where “sqlservr.exe” is installed and change your current path of CMD to that path.
SQL instance directory:
(example cd /d “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\”
5) Type .\SQLServr.exe -m from command prompt and start sql server services.
6) Start SQL Management Studio. Add/Change logins as needed and close SQL Management Studio.
7) On the command prompt Press CTRL-C to stop your SQL single user instance.
8) Start SQL Server from services.msc.
9) Verify if added logins have “SYSADMIN” privileges on SQL Server instance

    ,[FILE_Name] =
    ,[File_Location] = A.PHYSICAL_NAME
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
order by A.TYPE desc, A.NAME; 
Installing SQL Server from Command Prompt using Update Source:

Open Command Prompt using “Run as Administrator” elevated privileges and run setup as follows:

Note: Make sure there are no other SQL Instances are running on this current node where you are starting installation.

Browse to the installation folder where you have setup.exe and start installation

First Node:

setup.exe /action=installfailovercluster /updatesource=D:\software\CU3

Additional Nodes:

setup.exe /action=addnode /updatesource= D:\software\CU2

 D:\software is the location where you have your updates for SQL Server

For a standalone you can do a similar thing:

setup.exe /action=install /updatesource=c:\sql2012cu3

setup.exe /action=install /updatesource=\\sharedpath\share\sql2012cufiles\

SELECT DB_name() as DBName,name as [username], type_desc as usertype, create_date, modify_date FROM sys.database_principals where (type='S' or type = 'U') and name not in 
('dbo','guest','information_schema', 'sys')
select distinct @@Servername as [Server Name], as [DB Name], HARS.role_desc as 'Role Type',
rs.synchronization_state_desc, rs.synchronization_health_desc,
CASE rs.is_suspended WHEN 0 THEN 'NO' WHEN 1 THEN 'YES'   END AS [Is Suspended],
 CASE rs.suspend_reason WHEN 0 THEN 'User Action'
 WHEN 1 THEN 'Suspend from Partner'
 WHEN 2 THEN 'Redo'
 WHEN 3 THEN 'Capture'
 WHEN 4 THEN 'Apply'
 WHEN 5 THEN 'Restart' 
 WHEN 6 THEN 'Undo'
 WHEN 7 THEN 'Revalidation'
 WHEN 8 THEN 'Error in the calculation of the secondary-replica synchronization point' END AS [Suspended Reason], 
  rs.suspend_reason_desc as [Suspended Reason], rs.last_commit_time
from sys.dm_hadr_database_replica_states rs
Join sys.databases  sd
on rs.database_id = sd.database_id

JOIN sys.dm_hadr_availability_replica_states hars
ON rs.group_id =  hars.group_id

JOIN sys.availability_replicas
ON hars.group_id =  sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_group_states 
ON sys.dm_hadr_availability_group_states.group_id =  sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states
ON sys.dm_hadr_availability_group_states.group_id =  sys.dm_hadr_availability_replica_cluster_states.group_id

JOIN sys.dm_hadr_database_replica_states HDRS
ON sys.dm_hadr_availability_group_states.group_id =  HDRS.group_id

WHERE operational_state_desc IS NOT NULL
AND rs.database_state_desc IS NOT NULL