Rebuilding The System Merge Repl Indexes
Jan 17, 2007
Hi,
We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.
Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?
Thanks for your help
Graham
View 6 Replies
ADVERTISEMENT
Jan 14, 2002
Hi!
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.
Thanks!
View 1 Replies
View Related
Sep 20, 2000
Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.
I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.
Thanks in advance!
View 3 Replies
View Related
Dec 2, 1999
All,
I have scheduled dbreindex command to run on an clustered indexed on a very large table over night, so far this as failed to complete successfully. I think it may have something to do with the amount of space available in the device; the table is approx 238mb in size and the available disk space left in the device is 200 mb.
Does anyone know how much space is required to reindex a table/index this size and if it is required on the same device as the table or in tempdb.
Or even better, if anyone can explain the inter-workings of how the dbreindex command reorgs the table.
Thanks Mathew
View 1 Replies
View Related
Feb 23, 2004
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now that’s a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesn’t lock tables. I know DBCC INDEXDEFRAG doesn’t lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks all
View 3 Replies
View Related
Jan 14, 2002
(Oops, sorry I posted this on the SQL 7 discussion earlier).
----------------------
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.
Thanks!
View 1 Replies
View Related
Feb 23, 2004
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now that’s a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesn’t lock tables. I know DBCC INDEXDEFRAG doesn’t lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks all
View 2 Replies
View Related
Jun 13, 2008
Hello,
I'm new to rebuilding and reorg indexes. I used the standard report feature in 2005 to look up Index Physical Statistics on one db. I found recommendation to rebuild a few indexes that contain a number of fragments. I created a maint. task, I know maint. tasks are not a cure-all but so far I am just testing the waters. I created two tasks, one to rebuild and another to reorg. After I ran the job, I looked at the report and it still showed recommendation to rebuild the indexes. What is a better solution for my case?
View 7 Replies
View Related
Feb 25, 2007
Hi,
I have recenlty had to rebuild the indexes for the entire database as they were running a little slow. It prompted me to further investigate the health of the indexes on the database. I have been using the sys.dm_db_index_physical_stats view to do this. I had a query with regards to the avg_fragmentation_in_percent column that the view displays. I have rebuilt all the indexes and for some of the indexes the fragmentation value remains the same. The following is an example of what is returned by the sys.dm_db_index_physical_stats for a single index:
Table, IndexName, avg_fragmentation_in_percent, avg_page_space_used_in_percent
Links, PK_Links, 77.7777777777778, 97.1501606127996
Links, PK_Links, 0, 1.42080553496417
I do not understand why this index the fragmentation is so high even though I have rebuilt it. It is not just this index it occurs for other indexes as well. What could be some of the factors that are causing this to occur?
View 5 Replies
View Related
Jul 20, 2005
HiI got the advice to rebuild the indexes on the databases once a year.Espesially if the database have grown much. The question is: How do Ido that?If I try to run the indexscript wich was run when the databases werecreated, I only get the message that the indexes already exists.Are there a command to automatically rebuild the indexesautomatically?Roger
View 3 Replies
View Related
Jul 12, 2007
I am trying to set up a merge replication between SQL 2005 and SQL Express. When I try to apply the snapshot to the SQL Express database. I get the error "Can not access filepathSnapshot.pre due to error OS 3"
Does anyone know what OS error 3 applies to?
Also, when I try to place the snapshop in the default folder location say D:ReplicationData the create snapshot process will work. If I try to place the snapshot folder not in the default and in a UNC like this \serverIPReplicationData I get an access error. sqlAgent has access to the share, which is fully shared out. Any ideas?
View 1 Replies
View Related
Sep 14, 2006
Aight, so I added a full text catalog and a full text index for one specific column and table in my database.Now the issue is, whenever I rebuild it, it locks the full text index forever, making it unsuable. Now, there are only 30,000 records i need to search, so it isn't like there is this massive amount of data. What am I doing wrong to where it is locking the index and disallowing me to use the stored procedure that does the searching?
View 2 Replies
View Related
Mar 19, 2015
Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).
View 9 Replies
View Related
Jul 23, 2015
Currently we are facing some performance issue while accessing the archive data from the archive tables. the archive table is hugh and it contains around 100,000,000 records and this archive table is being used in few reports and in our commission cycles too. since we are facing performance issues we are rebuilding index once in a week on all the indexes on this archive table.
We have 1 clustered index and 5 non clustered indexes, every time when we rebuild all these indexes on this table it is taking more time, more often rebuilding the clustered index itself is taking approx. 1hr which is consuming more time. wanted to know is there any useful to rebuild clustered indexes or not, if yes then what would be the better way. if not then do we need to rebuild only non clustered indexes.
View 7 Replies
View Related
Sep 26, 2007
Hi all,
When I am rebuilding the indexes on the tables, I am getting lot of free space( unallocated) on the database.
Before rebuilding the indexes , the size of the database = 385 Gb
After rebuilding the indexes, the size jumps to = 572 Gb (i.e.) This means 187 Gb of unallocated space .
The Command use to rebuild indexes is:
USE [databasename]
GO
ALTER INDEX [PK_index] ON [dbo].[tablename] REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )
GO
So, every time we rebuild indexes, we have to shrink the database
(or)
Is there anything else ,I should be doing.
Thanks.
View 4 Replies
View Related
Apr 22, 2004
I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?
Your help is greatly appreciated.
Gary
View 3 Replies
View Related
Sep 15, 2006
I'm in trouble again. I want to make merge replication between SS 2000 Enterprise and SS 2005 Express edition. 2000 will be publisher, 2005 will be subscriber. In addition I want to use push subsription at 2000 side, very last thing is it will be download only (I know how to make a merge rep. downloadonly, thanks god!). That's all I want to do.
I made a little search, it seems what I want can't be done automatically, using SMO or scripts is suggested. In one of other forums I saw "distibuter>=publisher>=subsriber" (refering sql server versions). Is it an incontestable truth that I cannot make SS 2000 a publisher where SS 2005 Express is subscriber?
How can I achieve my goal, isn't there anyway to make 2000 publisher for merge replication while a 2005 Express is subscriber?
Thanks
abdul
View 1 Replies
View Related
Jul 7, 2015
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
View 9 Replies
View Related
Feb 25, 2008
I am trying to automate my index rebuild and reorg based off of the percentage of fragmentation level. The first time you run it you have to change the alter proce Sp_NCRNRecreate then change it to Alter proc. It seems to run okay no errors but it doesn't seem to actually rebuild them. Please take a look and let me know if you see a program error somewhere. I would like create this sp to run automatically
use master
GO
Alter PROC sp_NCR_RecreateIndexes @AutoRun bit=0 AS
BEGIN
DECLARE @DatabaseName varchar(128), @SchemaName varchar(128), @objectName varchar(128), @IndexName varchar(128), @PercentFragmented float, @command varchar(max)
SELECT db_name(s.database_id) as DatabaseName, schema_name(o.schema_id) as SchemaName, o.name as TableName, i.name as IndexName, s.avg_fragmentation_in_percent AS PercentFragmented
INTO #IndexesToRebuild
FROM sys.dm_db_index_physical_stats (db_id(), Null, NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.indexes i ON s.object_id=i.object_id AND s.index_id=i.index_id
WHERE s.avg_fragmentation_in_percent > 10.0 AND s.index_id > 0
SELECT * FROM #IndexesToRebuild
DECLARE IndexCursor CURSOR FOR SELECT DatabaseName, SchemaName, TableName, IndexName, PercentFragmented FROM #IndexesToRebuild
OPEN IndexCursor
FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @databaseName + '.' + @schemaname + '.' + @objectName + CASE WHEN @PercentFragmented<30 THEN ' REORGANIZE' ELSE ' REBUILD' END;
print @command
IF @AutoRun=1
EXEC(@command)
FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented
END
--SELECT * FROM #IndexStats
END
GO
EXEC sys.sp_MS_marksystemobject sp_NCR_RecreateIndexes
GO
EXEC otis..sp_NCR_RecreateIndexes
EXEC ncrCommon..sp_NCR_RecreateIndexes
GO
View 1 Replies
View Related
Aug 31, 2015
If I'm doing data compression(page level) does it rebuild indexes too? and how about stats, does it update stats too?
View 4 Replies
View Related
Apr 24, 2006
I'm using SQL Server 2005 SP1 Standard.
On the Rebuild Index Task there is a checkbox at the bottom that says 'Keep index online while reindexing'.
Great I thought, I'll check that.
Later, when I tested the job, I got this error:
'Online index operations can only be performed in Enterprise edition of SQL Server.'
Why have that checkbox available to check, if I'm running a version that doesn't allow it? Where's the bug?
Thanks
Ed
View 1 Replies
View Related
Apr 30, 2008
We are using SQL 1005 Standard Edt, so online Rebuild index is not available. Any way to figure out how much time will it take to rebuild index on given size of database so i can make sure i set schedule for rebuilding index when it is okay not to answer DB traffice during that time.
Thanks,
View 4 Replies
View Related
May 27, 2008
I'll start off by saying these questions were submitted to Microsoft and no
one can give us an answer without them doing a source code review. Their
response was "these are internal BCP scripts that are used by SQL"....yes, I
know that....that doesn't help to explain why the system created them. At
this point, I don't want my compary to pay support hours for Microsoft
personnel to learn what their software does.
--------------------
We've had merge publication in place for several years.
Last year, we added some tables/articles to publications.
After the upgrade from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2
Standard, when the snapshot was run, the system created some new files that
had never existed before. Also, and more importantly, these files were only
created for the articles added after the initial snapshot was created.
Articles existing in the intial snapshot did not have these type of files
created.
Examples of these files for a SA_MOVE table that was added to an existing
publication:
MSmerge_contents_SA_MOVE.bcp
MSmerge_contents_SA_MOVE_forall.bcp
MSmerge_rowtrack_SA_MOVE90.bcp
sysmergesubsetfilters_SA_MOVE.bcp
sysmergesubsetfilters_SA_MOVE90.bcp
Also some new bcps created were:
MSmerge_contents90_forall.bcp
MSmerge_genhistory90.bcp
MSmerge_rowtrack90.bcp
sysmergesubsetfilters90.bcp
The system wanted to apply these files before it would start synchronizing
data so they had to be delivered to the subscriber. It appears it was the
sysmergesubsetfilters_ files that it wanted to apply. These are all 0 in
size and we do not have filter on(at least not as far as I know...see the
addmergearticle code below).
New databases created this year that had all of the articles defined in the
publication before the initial snapshot was taken did not get these
additional files created, nor did the system want to try to send files to the
subscriber before syncrhonization could begin.
This is an example of how an article had been cretead under SQL Server 2000
when adding a new database, before an initial snapshot is run:
exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
@source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000003000EFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
This is an example of how an article was added under SQL Server 2000 to an
existing publication that already had the initial snapshot run:
exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
@source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000000000EFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0,
@force_invalidate_snapshot = 1
A few questions:
Does anyone have an idea why the system created these files are part of the
2005 upgrade?
What is the real purpose of the files with names such as _forall.bcp ?
Thanks for any help/info on this,
Doug
View 2 Replies
View Related
Mar 28, 2006
Hello...
We have had Merge Replication working for the past few months (SQL 2005 to SQL Mobile) and suddenly today the replication URL that points to the SqlCESA30.DLL does not work. I have tried re-running the SQL Mobile virtual folder wizard a few times....resetting IIS and even a re-boot...and still the URL is not available.
Is there anything that might provide some clue as to why this has unexpectedly failed?
thanks for any help!
- will
View 3 Replies
View Related
Feb 2, 2015
I've been trying to get a definitive answer to this question but alas I have conflicting and patchy answers so far from other sources. I have an index that, lets say, requires 10GB of data space to rebuild..This index resides on a filegroup that spans 2 files on two seperate drives (i.e. a mdf and ndf)
When I rebuild this index how will each of these datafiles grow as the rebuild proceeds to completion? Lets for the time being remove the caveats of any other activity hitting the example index/database in question.My tests seem to show that only the mdf will grows (or the file with the lowest id in the that filegroup) provided there is enough space available in that particular file to complete the operation. The secondary ndf dat file doesnt grow at all if the mdf has enough space.
Is expected behavior? i.e. the index will be rebuilt in a contiguous manner relative to the files contained with the filegroup i.e. fileid 1 will grow till limit reached then next fileid grows etc?
View 0 Replies
View Related
Nov 22, 2006
I have just spent 2 days trying to rebuild my system databases after a crash.
Each time I issued the start /wait setup.exe etc. command, the task would fail with the error:
MSI (s) (80:10) [12:07:34:815]: Product: Microsoft SQL Server 2005 -- Error 1706. An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'.
The initial install of SQL was run from a network share. The rebuild was from CD.
It seems that the original installation source paths are retained in the registry and are not ignored when attempting a rebuild.
Searching the registry for 'SqlRun_SQL.msi' throws up
HKEY_CLASSES_ROOTInstallerProducts812B67BE5CF8FD14F9F1F73F0E443838
and
HKEY_LOCAL_MACHINESOFTWAREClassesInstallerProducts812B67BE5CF8FD14F9F1F73F0E443838
Resolution:
Under the HKEY_CLASSES_ROOT key, change the Sourcelist from network (n) to media (m) and <drive>:ServersSetup instead of the share
Also change MediaPackage under the Media key to ServersSetup
This will automatically amend the HKEY_LOCAL_MACHINE key
This allowed a successful rebuild of the system databases
Bring back Rebuildm!
View 5 Replies
View Related
Nov 29, 2007
As an effort to rebuild system databases, I ran following command
start/wait C:Setup.exe /qn INSTANCENAME="DEV$SQL02" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="strongpassword"
I brought setup.exe file from installation cd to c drive of the server. My SQL Server is DEV and SQL Instance is SQL02. While running above command in command prompt (from C: prompt) I have an error masage that said 'setup failed' without any specific cause given for the failure.
I am doing above as part of disaster/recovery procedure in test environment.
Could anyone please shed some lights on what are the steps or what went wrong above while rebuilding master databases.
Thanks a lot
Mike
View 4 Replies
View Related
Jan 5, 2007
I am having trouble rebuilding the SQL 2005 system databases on my cluster.
The Cluster hardware failed completely and I am recovering from scratch (but only 1 node at this time)
The Win2K3 OS is recovered and communicating with the domain.
Cluster services have started and the quorum is online.
The clustered SQL instance is offline because there is no master.mdf present.
I run the setup command as per BOL:
start /wait setup.exe /qn VS=<VSName> INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=<StrongPassword> SAPWD=<NewStrongPassword> SQLACCOUNT=<domainuser> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domainuser> AGTPASSWORD=<DomainUserPassword>
The Summary.log reports the following:
Machine : CLUSTER1
Product : Microsoft SQL Server 2005
Product Version: 9.00.1399.06
Install : Successful
Log File : C:Program FilesMicrosoft SQL ServerSetup BootstrapLOGFilesSQLSetup0001_CLUSTERSVR1_SQL.log
--------------------------------------------------------------------------------
Setup succeeded with the installation, inspect the log file completely for status on all the components.
However the Microsoft SQL ServerMSSQL.1MSSQLDATA directory is empty. There are no system databases.
Without a rebuilt Master I cannot bring the clustered SQL instance online to then restore my database backups.
Any ideas?
D.
View 7 Replies
View Related
Oct 5, 2006
Group,
I am getting the following error during replication of Database to a client:
The schema script 'Statutes_6.dri' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed. (Source: MSSQLServer, Error number: 7696)
Get help: http://help/7696
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. (Source: MSSQLServer, Error number: 319)
The database is relatively small, only about 5 tables but there is a clustered Full-text Index.
Any ideas?
Thanks,
Frank
View 1 Replies
View Related
Jun 21, 2001
I ran a SP that lists fragmented tables in our DB which listed almost all the tables and indexes in the DB. The list includes index names that start with "_WA_Sys_tablecolumn name_.....". Are these SQL Server generated indexes or are these statitics on the tables? What are they? If these are system generated indexes as I thought they were, how are they generated? Can these indexes be dropped or should one want to? IF so, how? Any effect on the DB performance if dropped?
Thanks for your advise.
Helen
View 2 Replies
View Related
Jan 17, 2006
Hi,Is het possible to find information about indexes in SQL Server 2000?Information like pad index, fill factor, index fields, SortinTempdb?Gr,Hennie
View 3 Replies
View Related
Nov 13, 2015
This website describes how Merge statements should be optimized by the use of indexes on the target?source tables: [URL]..... It says that a clustered index should be created on the join column in the target and a unique covering index on the source table.Â
I have read in other articles that insert/delete/update statements perform worse on tables with clustered indexes as the leaf level pages will have to be reorganized.
Why in the case of Merge statement having a indexes actually improve the performance of insert/delete/update statements?
View 3 Replies
View Related
Jul 15, 1998
I have come across a user that has error 2525 errors on sysobjects tables throughout their SQL 6.5 system. The ids match and I can identify the index. But as they are system tables I cannot drop or rebuild the indexes.
Any ideas please?
The errors have been occuring for some time, so it is not an option to restore. I would like to avoid having to transfer data to and from another SQL Server.
Many Thanks
View 6 Replies
View Related
