SQL Server Collation Change Step By Step

 Please find here:

https://gallery.technet.microsoft.com/SQL-Server-Collation-cc5cc63c

Replicated Transaction Information:Oldest distributed LSN OR The process could not execute 'sp_repldone/sp_replcounters'

Some times this is resolved by using repl_done command along with repl_flush.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

go

sp_repleflush

This time we faced issue and went unsuccessful. Then we followed below steps.

1. Stopped Log Reader Agent
2. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

3. sp_repleflush
4. Started Log reader agent
5. Replication came in Sync.

Enable Change Tracking - SQL Server 2008

Setup.

1. First enable Change tracking. Here’s some sample T-SQL you can execute to start the process

ALTER DATABASE SAMPLEDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON);

GO

After it’s enabled and you want to check on which databases are affected, run this T-SQL

SELECT DB_NAME(database_id) [mydbname]
FROM SYS.CHANGE_TRACKING_DATABASES;

GO

To enable change tracking on a specific table use a query like this:

ALTER TABLE dbo.MYTABLENAME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

Now to check the tables that have tracking enabled, you can get that list here:

SELECT OBJECT_NAME(OBJECT_ID) [MYTABLENAME]
FROM SYS.CHANGE_TRACKING_TABLES;

GO

No changes are required to statements for insert or update statements that are enabled with change_tracking.

To find out what’s changed after enabled, you can run a query similar to the following

SELECT ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]

FROM CHANGETABLE(CHANGES dbo.MYTABLENAME, 0) ct

JOIN dbo.MYTABLENAME c ON c.[ID] = ct.[ID];

GO

Grant access to Report Builder

1. Start Report Manager.
   
2. Click Site Settings.
   
3. Click Security.
   
4. If a role assignment already exists for the user or group for which you want to configure Report Builder access, click Edit.
   Otherwise, click New Role Assignment. In Group or user, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
   
5. Select System User, and then click OK.
   
6. Click Home.
   
7. Click the Folder Settings tab.
   
8. Click the Security tab.
   
9. If a role assignment already exists for the user or group for which you want to configure Report Builder access, click Edit.
   Otherwise, click New Role Assignment. In Group or user, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
   
10. Select Report Builder, and then click Apply.
    
11. Repeat to create or modify role assignments for additional users or groups.
    

SQL Server 2012 SP2 failure - Validation for setting 'FAILOVERCLUSTERGROUP' failed. Error message: The cluster group cannot be determined for the instance name 'DBSQLPW80PA''

Error :

01) 2014-07-26 22:41:02 Slp: Sco: Attempting to get directory information for path C:\Program Files\Microsoft SQL Server\

(01) 2014-07-26 22:41:05 Slp: The failover instance name 'DBSQLPW80PA' does not exist.

(01) 2014-07-26 22:41:05 Slp: Validation for setting 'FAILOVERCLUSTERGROUP' failed. Error message: The cluster group cannot be determined for the instance name 'DBS80pA'.  This indicates there is a problem with the product registry setting for ClusterName, with product discovery, or the cluster resources.

(01) 2014-07-26 22:41:05 Slp: Validation for setting 'FAILOVERCLUSTERNETWORKNAME' failed. Error message: The SQL Server failover cluster instance name 'DBSQLPW80PA' could not be found as a cluster resource.

(01) 2014-07-26 22:41:05 Slp: Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.

(01) 2014-07-26 22:41:05 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: The cluster group cannot be determined for the instance name 'DBS80PA'.  This indicates there is a problem with the product registry setting for ClusterName, with product discovery, or the cluster resources. ---> Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException: The cluster group cannot be determined for the instance name 'DBS80PA'.  This indicates there is a problem with the product registry setting for ClusterName, with product discovery, or the cluster resources.

 
Resolution :

SStep 1.     Failover SQL Node A to Node B  

2Step 2.  On Node A, backup existing registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DBSQL30A\Cluster”

3 

SStep 3.     On Node A, update existing registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DBSQL30A\Cluster” to DBSQLPW80PA

4Step 4.     Reboot Node A

  

SStep 5.      Apply SQL 2012 SP2 and hotfix to  on Node A

Long Running Jobs Alert

Copy the below code and schedule the job on the server :-



DECLARE @INT INT
 SELECT jobs.name AS [Job_Name]
 , CONVERT(VARCHAR(23),ja.start_execution_date,121)  AS [Start_execution_date]
 , ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running') AS [Stop_execution_date]
 ,DATEDIFF(SECOND,CONVERT(VARCHAR(23),ja.start_execution_date,121),ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121),GETDATE())) as Duration
 into #jobhist
 FROM msdb.dbo.sysjobs jobs
 LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
 AND ja.start_execution_date IS NOT NULL
 --where jobs.name not like 'repli%' and name not like '%mirr%' and name not like '%distribu%'and name not like '%subscript%' and name not like '%sys%' and name not like '%pub%'

 select *
 , convert(varchar(10), (Duration/86400)) + ':' +
convert(varchar(10), ((Duration%86400)/3600)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)/60)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)%60)) as 'DD:HH:MM:SS'
into #JH
 from #jobhist where stop_execution_date='Is Running' and start_execution_date is not null


DECLARE @tableHTML  NVARCHAR(MAX)  
declare @NumStDevs int = 2

SET @tableHTML = 
   N'

' + @@SERVERNAME + ': Long Running Jobs Alert :

' + 
   N'' + 
   N'
  
   ' + 
   N'' + 
   N' ' + 
   CAST ( ( SELECT td = job_name, '', td = start_execution_date, '', td = stop_execution_date, '', td = [DD:HH:MM:SS] 
      FROM #jh where Duration>(360*24) /* for 1 day */
           FOR XML PATH('tr'), TYPE  
 ) AS NVARCHAR(MAX) ) + 
  N'

Job Name	start_execution_date	Current_Status	Duration[DD:HH:MM:SS]

' ; 
 

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DBPROFILENAME',
@recipients='EMAILS',
    @subject = 'LongRunning Jobs',
    @body = @tableHTML,
    @body_format = 'HTML' ;

 drop table #jobhist
 drop table #jh

Shrink Transaction log DB Replication

First check what is causing your database to not shrink by running:

SELECT name, log_reuse_wait_desc FROM sys.DATABASES

If you are blocked by a transaction, find which one with:

DBCC OPENTRAN

Kill the transaction and shrink your db.

If the cause of the blocking is 'REPLICATION' and you are sure that your replicas are in sync, you might need to reset the status of replicated transactions. To see the status of what the database still think needs to be replicated use:

DBCC loginfo

You can reset this by first turning the Reader agent off (I usually just turn the whole SQL Server Agent off), and then run that query on the database for which you want to fix the replication issue:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, 

 @reset = 1

Exec sp_replflush

Close the connection where you executed that query and restart SQL Server Agent (or just the Reader Agent). You should be all set to shrink your db now.