Tag: Database Design

2 Comments

SQL Server – Reinitializing a Single Article In Transaction Replication

  By ,

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.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Check The Port Configured in SQL Server

  By ,

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

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Get Backupset Records

  By ,

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
*/

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

ORACLE – Investigate the Memory Structures of the Instance

  By ,

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');
Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server SSRS – Query the Report Server Execution Log

  By ,

Below is the T-SQL script to query the Report Server Execution Log. You may query either the dbo.ExecutionLog2 or dbo.ExecutionLog3 table (same as dbo.ExecutionLog2 with 2 fields renamed: ReportPath has been renamed to ItemPath and ReportAction has been renamed to ItemAction)

Below is the output of the query.

USE ReportServer 
GO

SELECT *
FROM dbo.ExecutionLog3
ORDER BY TimeStart DESC
Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Get Index Creation Date

  By , ,

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;

No comments

SQL Server – Export SSRS Report to PDF in Landscape Format

  By ,

By default, your SSRS reports are generated in portrait format. The body of your report will be automatically resized  when you add report items.  When you deploy your reports, it will portray correctly in your browser. But when you export your report to PDF, you will notice that it is in portrait format. It is not presentable and it will have extra pages. You can prevent this from happening  by exporting the PDF in landscape format. To do that, just follow the steps below.

  1. Go to the properties of your report. (Right-click mouse just outside the body of your report). Set the width of the report to the landscape size of your A4 paper: 29.7 cm. Set the height of the report to 21 cm.

 

2. When you export the report, you will see extra blank pages. To avoid that, the                         size of the body should be less or equal to the size of the report margins

Set the width of the body to 26.7 cm                                                                                                   Set the height of the body to 18 cm

So now, when you export your report to PDF, it will be in landscape format.

 

 

 

No comments

SQL Server – Check Database Properties

  By ,

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

No comments

SQL Server – Top Queries Consuming CPU

  By ,

Below is a script that captures that top SQL queries consuming CPU.

SELECT TOP 50
[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.[text])
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5 --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC

No comments

SQL Server – Check Database Mirroring Status

  By ,

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