Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
saprouzy
Starting Member
2 Posts |
 Posted - 2007-09-29 : 19:16:45
|
| Hello There,I have two SQL2000 servers, I am trying to set a replication from server A to server B.the Indexes on server A occupy around 14GB, after i have set the replication, the tables on server B have the indexes listed (in manage indexes view in Sql Enterprise) but the size of the indexes is less than 1MB.so it seems that the indexes aren't working well on server B.(a query that takes around 15 secs on server A takes around 2 minutes on server B)any hints on what could be the problem?more info:- I am using tansactional replication- the database is using 2 file groupsthanks. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-29 : 20:00:45
|
| Are you sure the indexes are there? Try using sp_helpindex on the table.If they are then try an update statistics.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-30 : 05:32:47
|
| As well as Update Stats you probably need a Reindex (which itself will Update Stats) if you have just pushed several GB of initial data onto the second server ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-30 : 05:33:19
|
| And defragging the physical files would be a good idea for a just-created-database (See CONTIG.EXE from Sysinternals) |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-30 : 12:58:45
|
| Another way to do this will be to back up the database from Server A, restore it over B and set up replication again.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
saprouzy
Starting Member
2 Posts |
Posted - 2007-10-01 : 02:17:30
|
quote:
Originally posted by dinakar
Another way to do this will be to back up the database from Server A, restore it over B and set up replication again.
Dinakar, can you please tell me what options should be specified for the replication in this case, since this is actually what I have done but when the replication started it re-created the tables all over again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-01 : 02:32:44
|
| I think from where you are you should try getting good Housekeeping in place. Because if you don't have good housekeeping the problem will reoccur soon once more data is added in the normal way.Although a Backup & Restore would tell you, i.e. if that too ran really slowly, that there were some other problems that need addressing - hardware performance etcKristen |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-01 : 11:54:12
|
quote:
Originally posted by saprouzy
quote:
Originally posted by dinakar
Another way to do this will be to back up the database from Server A, restore it over B and set up replication again.
Dinakar, can you please tell me what options should be specified for the replication in this case, since this is actually what I have done but when the replication started it re-created the tables all over again.
Did you select "the snapshot already exists" option? If you didnt, Replication agent will recreate the tables and BCP the data into those tables. Am not sure if the stats/indexes will be upto date in that scenario.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|
|
Replication problem: Indexes