World Search

Thursday, August 29, 2013

Compare Replicated Tables for Differences

Problem:- There is a mismatch in the data which is replicated in a transactional replication.

Solution :-

Step 1:- Need to identify the tables
Step 2:- Compare using TableDiff Utility
Step 3:- Update the same on subscriber as publisher wins always. (applicable only if data is less)
else we need to drop and re- add the article.



Replication Diff Tool Command Line Options

        usage: tablediff

          -- Source Options --
 -sourceserver          Source Host
 -sourcedatabase        Source Database
 -sourceschema          Source Schema Name
 -sourcetable           Source Table or View
 -sourceuser            Source Login
 -sourcepassword        Source Password
 -sourcelocked          Lock the source table/view durring tablediff

          -- Destination Options --
 -destinationserver     Destination Host
 -destinationdatabase   Destination Database
 -destinationschema     Destination Schema Name
 -destinationtable      Destination Table or View
 -destinationuser       Destination Login
 -destinationpassword   Destination Password
 -destinationlocked     Lock the destination table/view durring tablediff

          -- Misc Options --
 -t             Timeout
 -c             Column Level Diff
 -f             Generate Fix SQL (You may also specify a file name and path)
 -q             Quick Row Count
 -et            Specify a table to put the errors into
 -dt            Drop the error table if it exists
 -o             Output file
 -b             Number of bytes to read for blob data types
 -strict        Strict compare of source and destination schema
 -rc            Number of retries
 -ri            Retry interval

Example 1:-


C:\Program Files\Microsoft SQL Server\110\COM>tablediff -sourceserver SQBILS -sourcedatabase MSTER_DATA -sourcetable CustomrCRM-destinationserver ACPB01 -destinationdatabase PCTER_DATA_REPL -destinationtable CustomrCRM

Example 2:-
tablediff -sourceserver PRADYOTHNAP02 -sourcedatabase TELIPHONECALLS -sourcetable student -destinationserver santhoshc -destinationdatabase TELICALL_REPLICA destinationtable Student


Wednesday, August 14, 2013

The concurrent snapshot for publication is not available because it has not been fully generated .

Problem:-

The snapshot files for a transactional publication are immediately removed after being generated by the snapshot agent.

Noticed that status of the publication articles are 3. Which is wrong either they should be 0 = Inactive, 1 = Subscribed or 2 = Active.




Solution :-

1) Delete the subscription and publication.
2) Check the tables to see if there are still rows for the publication database available:
select * from msdb..MSdistpublishers

select * from distribution..MSpublisher_databases

select * from distribution..MSarticles where publisher_db = 'distribution'
select * from distribution..MSsubscriptions where publisher_db = 'distribution'

3) Delete all rows that have still the 'publisher_db' from the just deleted publisher database:
delete from distribution..MSarticles where publisher_db = 'distribution'
delete from distribution..MSsubscriptions where publisher_db = 'distribution'
4) Re-create the publication subscriber and initialize.


Help Links :-



Tuesday, August 6, 2013

Script to Enable/Disable Database for Replication

Script to Enable/Disable Database for Replication


You can enable the database for replication using below script.
1
2
3
4
5
use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go
If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.
1
2
3
4
5
use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go

Friday, August 2, 2013

List All Tables Starts With MDT

SP_TABLES

Syntax:-

sp_tables [ [ @table_name = ] 'name' ]
     [ , [ @table_owner = ] 'owner' ]
     [ , [ @table_qualifier = ] 'qualifier' ]
     [ , [ @table_type = ] "type" ]
     [ , [@fUsePattern = ] 'fUsePattern'];
 
 
Example :- 
 
Use Database
 go
EXEC sp_tables @table_name = 'mdt_%'
 
 
 


































 

DBCC

More on :- http://msdn.microsoft.com/en-us/library/ms188796.aspx

Handy Information :-

Database Console Command statements are grouped into the following categories.
Command category Perform
Maintenance Maintenance tasks on a database, index, or filegroup.
Miscellaneous Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational Tasks that gather and display various types of information.
Validation Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
---------------------------------------------------------------------------
DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

 DBCC Internal Database Snapshot Usage

The following DBCC commands operate on an internal read-only database snapshot that the Database Engine creates. This prevents blocking and concurrency problems when these commands are executed. For more information, see Database Snapshots (SQL Server).
DBCC CHECKALLOC DBCC CHECKDB
DBCC CHECKCATALOG DBCC CHECKFILEGROUP
DBCC CHECKTABLE
When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:
  • Against master, and the instance of SQL Server is running in single-user mode.
  • Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
  • Against a read-only database.
  • Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
  • Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
  • Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.
The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following:
  • A read-only filegroup
  • An FAT file system
  • A volume that does not support 'named streams'
  • A volume that does not support 'alternate streams'
Note Note
Trying to run DBCC CHECKALLOC, or the equivalent part of DBCC CHECKDB, by using the WITH TABLOCK option requires a database X lock. This database lock cannot be set on tempdb or master and will probably fail on all other databases.
Note Note
DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created.
The sys.dm_exec_requests catalog view contains information about the progress and the current phase of execution of the DBCC CHECKDB, CHECKFILEGROUP, and CHECKTABLE commands. The percent_complete column indicates the percentage complete of the command, and the command column reports the current phase of the execution of the command.
The definition of a unit of progress depends on the current phase of execution of the DBCC command. Sometimes progress is reported at the granularity of a database page, in other phases it is reported at the granularity of a single database or allocation repair. The following table describes each phase of execution, and the granularity at which the command reports progress.
Execution phase Description Progress reporting granularity
DBCC TABLE CHECK The logical and physical consistency of the objects in the database is checked during this phase. Progress reported at the database page level.
The progress reporting value is updated for each 1000 database pages that are checked. 
DBCC TABLE REPAIR Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are object errors that must be repaired. Progress reported at the individual repair level.
The counter is updated for each repair that is completed.
DBCC ALLOC CHECK Allocation structures in the database are checked during this phase.
Note Note
DBCC CHECKALLOC performs the same checks.
Progress is not reported
DBCC ALLOC REPAIR Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are allocation errors that must be repaired. Progress is not reported.
DBCC SYS CHECK Database system tables are checked during this phase. Progress reported at the database page level.
The progress reporting value is updated for every 1000 database pages that are checked.
DBCC SYS REPAIR Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are system table errors that must be repaired. Progress reported at the individual repair level.
The counter is updated for each repair that is completed.
DBCC SSB CHECK SQL Server Service Broker objects are checked during this phase.
Note Note
This phase is not executed when DBCC CHECKTABLE is executed.
Progress is not reported.
DBCC CHECKCATALOG The consistency of database catalogs are checked during this phase.
Note Note
This phase is not executed when DBCC CHECKTABLE is executed.
Progress is not reported.
DBCC IVIEW CHECK The logical consistency of any indexed views present in the database is checked during this phase. Progress reported at the level of the individual database view that is being checked.

Features Supported by the Editions of SQL Server 2012

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation




  • Collation was missing on the column which is highlighted, added the same and worked smoothly.