Database Administration | SQL Conjuror

SQL Server – Reinitializing a Single Article In Transaction Replication

By Enrique 26/10/2017 24/04/2021 SQL SERVER, Database Administration

In this blog post, I will list down the steps on how to re-initialize just a single article in transaction replication

1. Turn off @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'false'
GO
               
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'false'
GO

The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.

2. Add new article.

EXEC sp_addarticle
@publication = 'Replication_Test',
@article = 'REPL_TABLE',
@source_object = 'REPL_TABLE',
@force_invalidate_snapshot = 1

3. Refresh the subscription

EXEC sp_refreshsubscriptions @publication = 'Replication_Test'
GO

4. Check the current snapshot agent history.

use distribution
go
select * from dbo.MSsnapshot_history

5. Start Snapshot agent.

EXEC sp_startpublication_snapshot @publication = 'Replication_Test';
GO

6. Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.

7. Turn ON @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go

EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'true'
GO


EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'true'
GO

This is how you can re-initialize only the required articles.

Facebook

Google

Twitter

No comments

SQL Server – Check The Port Configured in SQL Server

By Enrique 18/08/2017 29/04/2020 SQL SERVER, Database Administration

Use the script below to find the tcp port number that the SQL Server is listening on.

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

SQL Server – Get Backupset Records

By Enrique 17/08/2017 29/04/2020 SQL SERVER, Database Administration

The script below retrieves the backupset records. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

USE msdb ;
SELECT 
backup_start_date ,
backup_finish_date,
CAST(ROUND ((backup_size / 1024 / 1024), 0, 1) AS DECIMAL (18,0)) AS Backup_Size_MB ,
recovery_model,
[type]
FROM dbo.backupset
WHERE database_name = 'RBD_DB'
ORDER BY backup_start_date DESC

/*
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

Reference: https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
*/

ORACLE – Find Out If An Instance Is Running

By Enrique 11/08/2017 29/04/2020 ORACLE, Database Administration

Here is a script to check if an Oracle instance is running.

 SELECT instance_name, status FROM v$instance;

ORACLE – Investigate the Memory Structures of the Instance

By Enrique 10/08/2017 29/04/2020 ORACLE, Database Administration

Below is a script to show the current, maximum and minimum sizes of the SGA components that can be dynamically resized.

SELECT 
   component, 
   current_size, 
   min_size, 
   max_size    
FROM 
   v$sga_dynamic_components;

Execute the script below to Determine how much memory has been, and is currently, allocated to program global areas

SELECT 
   name, 
   value 
FROM 
   v$pgastat 
WHERE 
   name IN ('maximum PGA allocated','total PGA allocated');

SQL Server – Get Index Creation Date

By Enrique 19/07/2017 29/04/2020 SQL SERVER, Database Administration, T-SQL

There are many T-SQL scripts to get the index creation date but I always use this one.

 DECLARE @filename VARCHAR(500) 
SELECT @filename = CAST(value AS VARCHAR(500)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 
       gt.EventClass, 
       gt.EventSubClass,
       te.Name AS EventName,
       gt.HostName, 
       gt.StartTime, 
       gt.DatabaseName,
       gt.ObjectName
FROM fn_trace_gettable(@fileName, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass = 46
  and ObjectType = 22601
  and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;

SQL Server – Check Database Properties

By Enrique 29/05/2017 29/04/2020 SQL SERVER, Database Administration

The T-SQL query below can be used to check the properties of your databases.

USE master
GO
SELECT  name AS 'Database_Name' ,
        snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
        is_ansi_null_default_on AS 'ANSI NULL Default' ,
        is_ansi_nulls_on AS 'ANSI NULLS Enabled' ,
        is_ansi_padding_on AS 'ANSI Paddings Enabled' ,
        is_ansi_warnings_on AS 'ANSI Warnings Enabled' ,
        is_arithabort_on AS 'Arithmetic Abort Enabled' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' ,
        is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' ,
        is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' ,
        is_date_correlation_on AS 'Data Correlation Optimization Enabled' ,
        is_read_only AS 'Database Read-Only' ,
        is_local_cursor_default AS 'Default Cursor' ,
        is_encrypted AS 'Encryption Enabled' ,
        is_arithabort_on AS 'Numeric Round-Abort' ,
        page_verify_option_desc AS 'Page Verify' ,
        is_parameterization_forced AS 'Parameterization' ,
        is_quoted_identifier_on AS 'Quoted Identifiers Enabled' ,
        is_read_committed_snapshot_on AS 'Read Committed Snapshot' ,
        is_recursive_triggers_on AS 'Recursive Triggers Enabled' ,
        user_access_desc AS 'Restrict Access' ,
        is_broker_enabled AS 'Service Broker Enabled' ,
        is_trustworthy_on AS 'Trustworthy'
FROM    sys.databases ;
GO

SQL Server – Check Database Mirroring Status

By Enrique 15/05/2017 29/04/2020 SQL SERVER, Database Administration

Below is a script to check the database mirroring synchronization state.

SELECT 
	@@SERVERNAME as Server_Name,
	DB_NAME(database_id) as Database_Name,  
	mirroring_state_desc,
	mirroring_role_desc,
	mirroring_safety_level_desc
FROM 
	sys.database_mirroring
WHERE  
	mirroring_role IS NOT NULL

SQL Server – Get Size of All Databases in MB and GB

By Enrique 11/05/2017 29/04/2020 SQL SERVER, Database Administration, T-SQL

The script below will retrieve the size of all your databases in MB and GB.

 SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

SQL Server – Find Owners of SQL Server Agent Jobs Using TSQL

By Enrique 26/04/2017 29/04/2020 SQL SERVER, Database Administration, T-SQL

Find out who are the owners of the SQL agent jobs by running the script below.

SELECT    
    J.name AS [Job Name]
    ,L.name AS [Job Owner]
FROM
    msdb.dbo.sysjobs_view J
    INNER JOIN master.dbo.syslogins L
        ON J.owner_sid = L.sid
GO

Facebook

Google

Twitter

Category: Database Administration

SQL Server – Reinitializing a Single Article In Transaction Replication

SQL Server – Check The Port Configured in SQL Server

SQL Server – Get Backupset Records

ORACLE – Find Out If An Instance Is Running

ORACLE – Investigate the Memory Structures of the Instance

SQL Server – Get Index Creation Date

SQL Server – Check Database Properties

SQL Server – Check Database Mirroring Status

SQL Server – Get Size of All Databases in MB and GB

SQL Server – Find Owners of SQL Server Agent Jobs Using TSQL

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud