A database in Availability Group secondary node (Not Synchronizing/In Recovery) Status

Tempdb filled up last night and databases availability group are not accessible in secondary replica. After restarting a secondary SQL instance, one of the databases in Not Synchronizing / Recovery Pending mode.

I searched the issue on google and apply a resolution from

http://dba.stackexchange.com/questions/129471/availability-group-database-stuck-in-not-synchronizing-recovery-pending-mode

1. Suspend data movement of the database in secondary

ALTER DATABASE [DB1] SET HADR SUSPEND;

GO

2 . Remove database from AG group


Alter Database [DB1] SET HADR OFF;

GO

3. Restore the latest transaction logs (before tempdb is filled up/databases are not accessible) in secondary node. All the transaction logs after that.

RESTORE LOG [DB1] FROM DISK = '\\backups\DB.trn' WITH NORECOVERY;

GO

4. Re-join database to availability group

ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [SENetwork_AG];

GO

5. Resume the data movement

ALTER DATABASE [DB1] SET HADR RESUME;

GO

The above solution works for me. The database becomes Synchronized status.

Thank you for reading

Fix : Message Executed as user: Proxy Domain account. The process could not be created for step 1 of job 0xD656A2765BDCF54F91F7D2CA16398CC3 (reason: A required privilege is not held by the client). The step failed.

I am running a job on SQL 2016 and server is windows 2012 R2. The job is run as proxy account which is domain account. In the job step: Type is Operating System(CmdExec), Command is Powershell as an example below.

PowerShell.exe "E:\PowerShell\FindLastRebootDate\ServerLastReboot.ps1"

The job failed with the error below.

Message Executed as user: Proxy Domain account. The process could not be created for step 1 of job 0xD656A2765BDCF54F91F7D2CA16398CC3 (reason: A required privilege is not held by the client).  The step failed.

I was able to fix the issue by.

Step 1. Change the service account running SQL Agent to Local service and restarted the SQL Agent
Step 2. Change the local service running SQL Agent to the previous service account back and restarted the SQL Agent

After that I run the job, the job ran successfully.

Thank you
Mya

Checking Server online Status - PowerShell

We managed 100 + servers and we want make sure they come back after monthly maintenance reboot. We will run the following script by confirming the servers are Up. I retrieve servername from a Table called SQLServers from Database - DBA_ServerDW from ServerInstance name SQLTEST.

$servername = invoke-sqlcmd -ServerInstance SQLTEST -Database DBA_ServerDW `
-Query "select ServerName from DBA_ServerDW.dbo.SQLServers
where servername not like '%\%'"
ForEach ($server in $servername)
{
   # Ping the machine to see if it's on the network
   $ServerN=$server.ServerName
   $results = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$ServerN'"
   $responds = $false  
   ForEach($result in $results) {
      # If the machine responds break out of the result loop and indicate success
      if ($result.statuscode -eq 0) {
         $responds = $true
         break
      }
   }
         If ($responds) {
      # Gather info from the server because it responds
      Write-Output "$ServerN responds"
   } else {
      # Let the user know we couldn't connect to the server
      Write-Output "$ServerN does not respond"
   }
}

Tempdb.mdf file takes on Model database initial size

I changed model database size  to 5 Gbs so new user databases will take on Model database size.

USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 5242880KB )
GO

Before restart SQL instance, I have sized tempdb datafiles size to 512 MB

Then I restarted SQL instance and agent. It takes a while to connect to SQL server via SSMS.

Tempdb.mdf file is recreated with 5 Gbs which takes on Model database initial size.

According to MS KB, tempdb should not take on Modeldb.
https://support.microsoft.com/en-us/kb/307487

PowerShell - Find AlwaysOn Primary node Report

I was working on a powershell script which tells me which node is AlwaysOn primary replica. We have 38 servers which participate in AlwaysOn availability group technology. This script will help you to determine which server is a primary replica without logging into each server and find out.

NOTE:

I set up a path which is for error log at E:\PowerShell\GetAlwaysOnPrimary\

DBATestServer is our DBA server and DBA_ServerDW is DBA inventory database

SQLServers table is in DBA_ServerDW database. The table is already populated with a list of servers that we manage in our organization.

First, I will get a list of server names from SQLServers table. Then each server will be passed in for loop. I use invoke-sqlcmd command to all sql statements. Inside for each loop , I will get each server info if it is primary replica. Finally, I will omit all null results from the query and insert only returned data to a table AlwaysOnPrimary in DBA_ServerDW database. Thank you for reading my blog. Enjoy scripting!

$($errorlog = 'E:\PowerShell\GetAlwaysOnPrimary\Error.txt'

Clear-Content $errorlog

#Get Server List from SQL server inventory table, exclude name instances

$servername = invoke-sqlcmd -ServerInstance DBATestServer -Database DBA_ServerDW `

-Query "select servername from SQLServers

where SQLservers.Purpose like '%AlwaysOn%'"

foreach($server in $servername){

Try{

#convert system.object data type to String

$ServerN=$server.ServerName

$AG = invoke-sqlcmd -ServerInstance $ServerN -Database master `

                -Query "IF SERVERPROPERTY ('IsHadrEnabled') = 1

BEGIN

SELECT

  RCS.replica_server_name as ServerName

 ,AGC.name as AvailablityGroupName

  , ARS.role_desc as Role

 , AGL.dns_name as ListenerName

FROM

 sys.availability_groups_cluster AS AGC

  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS

   ON

    RCS.group_id = AGC.group_id

  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS

   ON

    ARS.replica_id = RCS.replica_id

  INNER JOIN sys.availability_group_listeners AS AGL

   ON

    AGL.group_id = ARS.group_id

WHERE

 ARS.role_desc = 'PRIMARY'

END"

$SName = $AG.ServerName

$GName = $AG.AvailablityGroupName

$Role  = $AG.Role

$LN    = $AG.ListenerName

if ($SName)

        {

                invoke-sqlcmd -ServerInstance DBATestServer -Database DBA_ServerDW `

                -Query  "INSERT INTO AlwaysOnPrimary(ServerName,AvailabilityGroupName,Role,ListenerName)

                        VALUES ('$SName','$GName','$Role','$LN')"

          }

}

Catch

{    Clear-Content $errorlog

     "Fail to get information $ServerN +' ' + $RunTime :$_" |Out-File $errorlog -Append

}

}

Add a database to AlwaysOn availability group automatically

Description: The following script adds new databases to AlwaysOn Availability Group. You can create it as a stored procedure.
To add a database to availability group automatically, you will need to create a server level trigger or a new database creation alert. Then, you call the script as a stored procedure from the server trigger or the database creation alert. I do not include server trigger on this page. You can find it on google. Thank you for reading!

NOTE : Before you run the stored procedure, please create a linked server between primary and secondary replica on both replica. Set RPC and RPC Out to True on the linked server properties

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

DECLARE @linkedserver as SYSNAME
DECLARE @sql1 NVARCHAR(MAX)
DECLARE @sql2 NVARCHAR(MAX)
DECLARE @path VARCHAR(150)
DECLARE @bakupfile VARCHAR(200)
DECLARE @db VARCHAR(255)
DECLARE @BackUpsql NVARCHAR(MAX)

--Get a linked server information
SET @linkedserver = (select Name from sys.servers where server_id <> 0 and is_linked = 1)

-- Get back up path from database setting
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',  
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name = 'BackupDirectory', @path = @path OUTPUT ;  

--Added back slash at the end of back up path
Set @path = @path + '\'

DECLARE AG_cr CURSOR LOCAL FOR

 -- Get new databases which are not part of an availability replica of in availablity group

  SELECT name FROM sys.databases 
  WHERE replica_id IS NULL 
  AND database_id > 4 and 
  source_database_id is NULL 
  and State =0 and 
  name not in('distribution','ReportServer','ReportServerTempDB')

  OPEN AG_cr

  FETCH AG_cr INTO
  @db

WHILE @@FETCH_STATUS >= 0
        BEGIN

print @db
--Change database recovery mode to FULL

  IF (SELECT recovery_model FROM sys.databases WHERE name=@db) <> 1
        BEGIN
            PRINT 'Changing recovery model to FULL';
            DECLARE @ModeChange NVARCHAR(512) = 'ALTER DATABASE [' + @db + ']  SET RECOVERY FULL WITH NO_WAIT';
            EXEC sp_executesql @ModeChange;
        END;
    ELSE
        BEGIN
            PRINT 'Database is already in FULL recovery mode.'
        END;

Print 'Initial Database BackUp...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP DATABASE ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '.BAK'';'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;


Print 'Backing up a database...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP DATABASE ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '_AG.BAK'' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5;'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;

Print 'Backing up a Log...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP LOG ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '_AGLOG.TRN'' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5;'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;

set @bakupfile = @db +'_AG.BAK'

set @sql1 = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N'' RESTORE DATABASE ' +@db+ ' FROM DISK ='+''''''+ @path + @bakupfile+''''''+ 'with norecovery'''

print @sql1

Exec sp_executeSQL @sql1

set @bakupfile = @db +'_AGLOG.TRN'

set @sql1 = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N'' RESTORE LOG ' +@db+ ' FROM DISK ='+''''''+ @path + @bakupfile+''''''+ 'with norecovery'''

print @sql1

Exec sp_executeSQL @sql1


--join AG group
DECLARE @AGroupName VARCHAR(200)
SET @AGroupName= (select Name from sys.availability_groups)


PRINT 'Joining database to availability group...'

DECLARE @AddDbToAG NVARCHAR(2048) = 'USE master' + char(13)+'ALTER AVAILABILITY GROUP [' + @AGroupName + '] ADD DATABASE [' + @db + ']';

EXEC sp_executesql @AddDbToAG;

PRINT 'Bringing replica online'     

DECLARE @AddHADR NVARCHAR(2048) = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N''ALTER DATABASE [' + @db + '] SET HADR AVAILABILITY GROUP = [' + @AGroupName +'];''';

EXEC sp_executesql @AddHADR


FETCH AG_cr INTO
  @db

END

 CLOSE AG_cr;
 DEALLOCATE AG_cr;

Changing User Database Owner - sp_MSforeachdb

Declare @Command varchar(max)
Select @Command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false END'
Print @Command
EXEC sp_MSforeachdb @Command

GO