I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.
Thank you,
Kameron
I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.
My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?
Any help/advice appreciated.
Hi
We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)
How do I do it ?
Currently there is no data in the table - which I guess might help..
thanks
Bruce
Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?
Any help/short-cuts/undocumented features greatly appreciated.
Regards
Opal
Hi all,
I am new to replication and have a few questions.
1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?
2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?
I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.
I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.
I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.
I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:
TRANSACTION table
amount = 200, metadata_id = 1001 // Replicated from server INPUT_1
amount = -117, metadata_id = 2001 // Replicated from server INPUT_2
METADATA table:
id=1001 Actitiy=Sales, Country=USA
id=2001 Activity=Sales, Country=USA
What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.
If anyone can offer any advice on how to achieve this I would appreciate your input.
Hi, My application goes through a lengthy data processing phase beforeit's ready to run. During the DP phase I'm mostly inserting records,afterwards many of my tables will be read only. Is there anythingwrong with dropping indexes used for the DP and setting up clusteredindexes afterwards? Can I just declare my clustered indexes and go,or do I have run some kind of statistics ...?
View 1 Replies View RelatedIs any one know of a way of changing the clustered index without creating in the middle the default clustered index
we have a big table that we use to switch the clustered index
whenever we change the clustered index we cannot change it directly we have
to drop the existing than the default clustered is built
and than we can built the new one - since it is a big table the process
takes a lot of time and I wonder if we can do it directly from one cluster
index to another
What we do not is running the following SQL:
-- remove the old index
drop index Tbl.I_oldId
GO
-- now create the newId as clustered
CREATE CLUSTERED
INDEX [I_newId] ON Tbl ([newId])
ON [PRIMARY]
GO
Any Idea ?
Thanks
David
Is there any query to find out all the tables without a Primary key or without a Unique index ?
View 3 Replies View RelatedI have two replicated databases i.e.
1. Database 1 is a live database where all live applications point
2. Database 2 is a replica of Database 1 and all reporting / BI applications point to this db. This is read-only.
Is it possible for reporting applications, pointing to Database 2, to create temporary tables in the read-only database?
Thanks
I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Thanks a bunch.
I'm in the process of migrating over nearly 900 reports to a replicated server.
I have moved over 100 reports, stored procedures and their dependent objects so far.
I have two tables that are not being applied to the subscriber.
Ok, so I must have screwed something up.
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €śIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Thanks a bunch!
md
How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!
View 4 Replies View RelatedHello List,
I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:
"_WA_Sys_status_01EAB64E"
I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.
Thanks,
Hi,
Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.
Thanks,
Jeff
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
Hi,
I have 2 tables with this design: one has ArticleID as primary key and multiple other fields and one has GroupID as primary key and multiple other fields. Each article can belong in multiple groups so I created a new table called articleGroups with only 2 fields: ArticleID and GroupID to show the groups associated with each article. There is a relation between this table and each of the main 2 tables. My question is, in the articleGroup table, does it make any sense to create an Index on ArticleID, GroupID or both? Since the group is needed for each article the Groups will always be queried everytime the article is queried. So, I am not sure if an index is needed?
Thank you,
Regards,
Iulian
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
Hi,
I want to list the table names in a database "mydev_db".What would be the query ?.
I want to run a similar query to find out the indexes,views,stored_procs etc.
Regards,
Copernicus
I was trying to find out how much space is available in a 2000 db for allocation to tables and indexes. I am trying to find the amount of space that has to be used-up before another allocation is automatically made to the database. I looked at sp_spaceused but BOL is rather sketchy at defining what the numbers it returns really mean. Is the "unallocated space" the value I am looking for?
Thanks,
Michael
I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'
USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO
USE RZTQ5OL02
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT Name from sys.sysobjects where type = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Anyone know how to place additional indexes on system tables?
View 1 Replies View Relatedhi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards
Ali
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better
The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
I would like to create an index on a table from the Database Explorer.
I am using Microsoft Visual C# 2005 Express Edition.
I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.
Thanks!
Hi,
I need a query to get the index names of particular tables. for eg.. i have some tables like emp_data,emp_job....etc..Now i want to find all indexe names for those tablenames that starts with emp........ Plz help me...
I'm just beginning to experiment with memory optimised tables.
I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.
I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.
(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)
The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?
Hello,
I created ssis package to transfer tables from one DB to another.
However, I don't find the option where I can make that the transfer with the indexes and pks of the tables. in sql2000 I had this option.
appriciate your help.
Kubyustus
The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
Hi,I was just wondering... Is there any built-in function in MS SQL, which willallow me to do rows' "capitalisation"?Lets say that in database.table.name I've got:"FOO BAR LTD.", which I want to change to "Foo Bar Ltd.""Foo bar LTD.", which I want to be "Foo Bar Ltd.".Is there any way of doing this or do I have to read it from database, changein some script, and then insert it back into the table?Hope it's all clear ;-)Thank you,Martin
View 2 Replies View Related