Applying DBCC DBREINDEX On A Database That Is Part Of Transaction Replication

Mar 4, 2007

I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.





Thanks in advance

Regards

R Suresh

View 1 Replies


ADVERTISEMENT

DBCC DEFRAG Vs DBCC DBREINDEX

May 11, 2006

What factors would make you decide to use DBCC DBREINDES instead of DBCC DEFRAG when you notice index defragmantation?

Thanks.

ekareem

View 1 Replies View Related

SQL 2012 :: Cannot Truncate Log File Which Is Part Of Transaction Replication

Feb 9, 2015

SQL 2012 Ent SP2

Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.

I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.

The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.

I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).

I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.

I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.

Do I have to remove replication completely to get round this? Surely not.

I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?

I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.

View 1 Replies View Related

Dbcc Dbreindex

Jan 7, 2002

We are new to SQL Server and we are currently setting up a job that
does a dbreindex for tables that need it. I got the scripts for this
from swynk. 50 of the tables that needed a dbreindex,
(under 90% scan density) were not impacted by running the dbreindex.
I.e., I ran the dbreindex and it left it at 50% scan density.
The fill factor for these tables is set to 100%.
I tried update stats, updateusage...nothing changes....
I am sure there must be a logical reason as to why the dbreindex
had no impact on these tables. Does any one have any info on this?
Thanks in advance!

View 4 Replies View Related

DBCC DBREINDEX

Mar 21, 2001

I am executing a DBCC DBREINDEX for a table with about
4 million rown in SQL6.5. I has run for about
15 hrs now and is till running.
The index is the PK for the table and it is clustred index.
I ran it on a non clustered index on the same table and it ran for a couple of hrs.
Does any one have any idea how much time it might take for the clustered index?
I heard that this process might take more time and for one of my friends it ran for more than 40 hours.
I don;t have so much if time as th DB has to go for production to night and if not it might effect our businees.
I cancelled DBCC DBREINDEX before by killing the process and the master.DAT file got corrupted.I had to restore everytihng. Does anyone know any work around?
Help appreciated.

Thanks.

View 4 Replies View Related

DBCC DBREINDEX

Jun 11, 2001

Could users use database while running DBCC DBREINDEX for all tables in database?

View 1 Replies View Related

DBCC DBREINDEX ?

Aug 14, 2001

Hi,
I wanted to know is DBCC DBREINDEX is a logged operation?
The reason why I am asking this is, Whenever we create an index or drop an existing it is a logged operation. Is this the same when we do DBCC DBREINDEX? Is it drops existing indexes and creates new indexes. My concern here is about transaction log. If I run a DBCC DBREINDEX on a huge table with lot of indexes, Is it going to fill transaction log?

Any help is appreciated!

Thank you,
Ravi.

View 1 Replies View Related

Dbcc Dbreindex

Nov 7, 2000

About how long should I expect this to run? The Database is 200MB. The table I doing the reindex on has about 800 rows. Is an hour outrageous.. ?

View 1 Replies View Related

Dbcc Dbreindex

Aug 4, 2000

Hello!

SQL Server 7
After database clean up (removing old data)I run dbcc dbreindex.
Is it true I don't need to run UPDATE STATISTICS after DBCC DBREINDEX as since SQL Server 7 runs UPDATE STATISTICS automatically (after dbcc dbreindex).
Thank you.
Anny

View 2 Replies View Related

DBCC DBREINDEX

Mar 21, 2001

I am executing a DBCC DBREINDEX for a table with about
4 million rown in SQL6.5. I has run for about
15 hrs now and is till running.
The index is the PK for the table and it is clustred index.
I ran it on a non clustered index on the same table and it ran for a couple of hrs.
Does any one have any idea how much time it might take for the clustered index?
I heard that this process might take more time and for one of my friends it ran for more than 40 hours.
I don;t have so much if time as th DB has to go for production to night and if not it might effect our businees.
I cancelled DBCC DBREINDEX before by killing the process and the master.DAT file got corrupted.I had to restore everytihng. Does anyone know any work around?
Help appreciated.

Thanks.

View 1 Replies View Related

Dbcc Dbreindex

Jan 10, 2006

Hi All,

I have a following script:

DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding indexes ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 10)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

I added as a step in the job. However, when I run it I get an error message:

Msg 2501, Sev 16: Could not find a table or object named 'table_1'. Check sysobjects. [SQLSTATE 42S02]

When I run
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

table_1 is the first on list. Any idea why I am getting this error message?

View 5 Replies View Related

DBCC DBREINDEX Does Nothing

May 17, 2004

Has anyone had the problem with DBCC DBREINDEX on a table/index not doing anything.
You just get the usual "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message.
I have tried:
DBCC DBREINDEX ('<table_name>')
DBCC DBREINDEX (<table_name>)
DBCC DBREINDEX ('<table_name>', '')
DBCC DBREINDEX ('<table_name>', '<index_name>')
DBCC DBREINDEX ('<table_name>', '<index_name>', 80)

Maybe its "optimising" and not thinking anything needs updating.

Should I look for an alternative way of rebuilding an index like dropping and recreating?

The beauty with DBCC DBREINDEX ('<table_name>') would have been that I was going to get the list of user tables in the database and call it for each table to simply rebuild all indexes.

View 5 Replies View Related

DBCC DBREINDEX

Sep 21, 2007



DBCC CHECKDB;
GO
Result:
DBCC results for 'AgentsStateChanges'.
There are 2422697 rows in 46484 pages for object 'AgentsStateChanges'.
CHECKDB found 0 allocation errors and 92 consistency errors in table 'AgentsStateChanges' (object ID 2025058250).

Server: Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 53575229, index ID 1. The high key value on page (1:193625) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:269532).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. The previous link (1:269531) on page (1:269532) does not match the previous page (1:193625) that the parent (1:272396), slot 252 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. B-tree chain linkage mismatch. (1:193625)->next = (1:269532), but (1:269532)->Prev = (1:269531).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 14 and 15.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 154 and 155.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266869), slots 49 and 50.


DBCC DBREINDEX ('Vestel.dbo.AgentsStateChanges', PK_LLAgentFlowSummary, 0);
GO
Result:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'type 24, len 16'.
The statement has been terminated.



PLEASE HELP!;
GO

View 6 Replies View Related

Executing DBCC Dbreindex

Oct 10, 2001

Is it Nessary to disconnect users , go in single user mode to execute
DBCC DBREINDEX in SQL Server 2000 ?

View 2 Replies View Related

Is A Dbcc Dbreindex Logged?

Jan 5, 2001

If I reindex tables on a production server and dump the transaction log then load that tran log to a
warm standby server --will the indices be rebuilt on the standby server? My guess is no,
but I've heard people say otherwise.

--jen

View 1 Replies View Related

Dbcc Dbreindex Failed

Feb 5, 2001

when i run the dbcc dbreindex command it failed and message like this

[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionCheckForData (PeekNamedPipe()).
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.

Connection Broken
please help

View 3 Replies View Related

DBCC DBREINDEX Does Not Work

Feb 21, 2006

DBCC SHOWCONTIG shows following information. But after ran
DBCC DBREINDEX ('CM20100', ''), nothing changed.

What I did wrong? Thanks in advance.

******************************************************
DBCC SHOWCONTIG scanning 'CM20100' table...
Table: 'CM20100' (1957582012); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 491
- Extents Scanned..............................: 159
- Extent Switches..............................: 158
- Avg. Pages per Extent........................: 3.1
- Scan Density [Best Count:Actual Count].......: 38.99% [62:159]
- Extent Scan Fragmentation ...................: 99.37%
- Avg. Bytes Free per Page.....................: 946.0
- Avg. Page Density (full).....................: 88.31%

View 10 Replies View Related

DBCC DBREINDEX Not Working

Jul 23, 2005

I have a stored proc that identifies indexes that need to bedefragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG.This works fine and shows me all of the candidate indexes to bedefragged. I am using DBCC DBREINDEX to rebuild the indexes. What Inoticed yesterday is that it seems that DBREINDEX is not actually doinganything. I would check the target indexes, run DBREINDEX, then checkagain. The same list of indexes came up unchanged.What is strange is although I am not specifying "WITH NO_INFOMSGS" thisis the only output from the DBREINDEX:"DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator."According to BOL DBREINDEX should return a result set if "NO_INFOMSGS"is not specified, but if "NO_INFOMSGS" is supplied the prior message iswhat is returned. This doesn't seem to be working correctly, or if itis, I have no idea where any error messages are being returned.Anyone have any ideas?

View 2 Replies View Related

Dbcc Dbreindex Question

Apr 20, 2007

Is this command, which I know can be executed on a per-table basis,the same as the Rebuild Index Task in the SQL Server Management Studiofor maintenance tasks??The documentation I have mentions the reindex.sql script, is this thescript that is executed by the Rebuild Index Task??Thank you, Tom

View 1 Replies View Related

DBCC DBREINDEX Questions

May 15, 2008

Hi,

I've previously raised a thread about an index corruption issue we are getting on an infrequent basis. We are running SQL 2000 SP4, patched up with latest hotfixes on a cluster.

Yesterday, we experienced the corruption again. In the past, running DBCC DBREINDEX on the corrupted index has fixed the problem.

However, this time, even though DBCC DBREINDEX reported that it completed successfully, the corruption remained (proved by running DBCC CHECKTABLE on the index).

First question:
We created an identical index, with a different name, using the CREATE INDEX command, but this was created with the same corruption. Does SQL Server try and be clever and say "this index is the same as another, so I'll just copy that one instead of creating a new one" ? This is what would appear to be the case.



We then dropped the index and then recreated it. This time the index was fine.

Second question:
My understanding of DBCC DBREINDEX is that it drops and recreates the index. As manually dropping and recreating the index worked but DBCC DBREINDEX didn't I can only assume that this isn't the case. So what does it do?



This then begs a third question that, as I've been 'fixing' the issue in the past by running DBCC DBREINDEX, is it possible that it fixes it enough for DBCC CHECKTABLE to think it's been fixed, but deep down the problem has remained which is why the index has continued to corrupt a few weeks down the line?


If anyone can help answer these questions that would be really useful.

Thanks.

View 10 Replies View Related

DBCC DBREINDEX Running Out Of Room

May 16, 2000

I am trying to reindex a large table, and cannot because there isn't enough room on the the primary filegroup. the database consistes of one physical file in the primary filegroup. the table is over 50% of the size of the database. When the table is less than 50% of the size of the database, I do not see this problem.

BTW, the only index on the table is the primary key which consists of two columns, one is an integer and the other datetime.

It seems as if SQL server needs 1x the current size of the table to be free in order to reindex? Is this the case?

It is not an option for me to allow the database to autogrow. Is there anything else I can do?

Thanks for your help!

View 3 Replies View Related

DBCC DBREINDEX Fail While No Duplicates....

Nov 21, 2003

Here is the DBCC CHECKDB error result:
Msg 8934, Sev 16:
Table error:
Object ID 517576882, index ID 1. The high key value on page (1:919) (level 0) is not less than the low key value in the parent (1:13120), slot 270 of the next page (1:920). [SQLSTATE 42000]

Object ID 517576882, index ID 1. The high key value on page (1:958) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:4043). [SQLSTATE 42000]

Object ID 517576882, index ID 1. The low key value on page (1:4043) (level 0) is not >= the key value in the parent (1:13120) slot 301. [SQLSTATE 42000]
Object ID 517576882, index ID 1. The high key value on page (1:4055) (level 0) is not less than the low key value in the parent (1:13120), slot 305 of the next page (1:4040).
Object ID 517576882, index ID 1. The previous link (1:3987) on page (1:4067) does not match the previous page (1:4069) that the parent (1:12804), slot 3 expects for this page.

Object ID 517576882, index ID 1. B-tree chain linkage mismatch. (1:4069)->next = (1:4067), but (1:4067)->Prev = (1:3987).

Object ID 517576882, index ID 1. The previous link (1:3987) on page (1:4071) does not match the previous page (1:4048) that the parent (1:13120), slot 313 expects for this page.

Object ID 517576882, index ID 1. B-tree chain linkage mismatch. (1:4048)->next = (1:4071), but (1:4071)->Prev = (1:3987).
--==================================
Then I do DBCC DBREINDEX (tblABC, '', 70)

get error of Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '451396'.

However, don't find duplicates on the table PK which is index ID 1. (no another alternate uniqe key)

Thanks for help
David

View 3 Replies View Related

DBCC DBREINDEX - Unexpected Results

Jul 28, 2004

I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.

First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?

Clive

View 12 Replies View Related

DBCC DBREINDEX Unexpected Results

Aug 9, 2004

Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?

Clive

View 1 Replies View Related

DBCC DBREINDEX Fails For Table

Aug 7, 2006

Hi,

I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.

--------
Error Number : 3628

Message :

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.

[Microsoft][ODBC SQL Server
------------
This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..

Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.

Thanks in advance,
Ranjit.

View 6 Replies View Related

DBCC DBReindex VS Update Statistics

Feb 1, 2004

Hi all,

Is it neccessay to run the "Update Statistics" after run "DBCC DBReindex"? Does "Update Statistics" automatically update the stats?

Thanks in advance
Kim,

View 1 Replies View Related

Strange Error When Do DBCC DBREINDEX

Mar 1, 2004

The error message is:
Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 52
Process ID: 732
ODBC: Message 0£¬severity 16£¬state 1
Communication link failure
ODBC: Message 0£¬severity 16£¬state 1
Communication link failure

god , I have never seen error like that!
what will be reason for the error??

View 3 Replies View Related

Does DBCC DBREINDEX Update Stats?

Sep 26, 2005

(Assuming SQL Server 2000, auto create statistics on, auto updatestatistics on.)DoesDBCC DBREINDEX(<tablename>)update statistics? If yes, are the statistics equivalent to thosethat would be produced by:UPDATE STATISTICS <tablename> WITH FULLSCAN

View 1 Replies View Related

How Do I Excute DBCC DBreindex From Public Group

Aug 24, 2000

Hi All
Could you guys help me i have small problem in running dbcc dbreindex.I could run this command from dbo,sysadmin and
Object owner logins to rebuild the index but what i want to do is i want to run this command in a stored procedure.
this is also fine if i run the stored procedure any one of above logins it executes when i grant exec permission on this procedure
to public it will not execute in public group since public group is not a owner of this object..
How do i excute DBCC DBreindex from public group.Could you please help me.....................

View 1 Replies View Related

Dbcc Dbreindex/indexdefrag && Update Stats

Jul 22, 2004

Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?

Clive

View 1 Replies View Related

Order By Error Running DBCC DBREINDEX

Jul 20, 2005

I'm running a simple DBCC DBREINDEX ('myTable') and I receive thefollowing error:"Server: Msg 169, Level 15, State 2, Line 2A column has been specified more than once in the order by list.Columns in the order by list must be unique. DBCC executioncompleted. If DBCC printed error messages, contact your systemadministrator."I can successfully reindex other tables in this database. I thoughtthat perhaps I had objects in the database that ended up with the samename, but I've pretty much ruled that out.Any suggestions?ThanksJohn D. Morrismailto://jmorris_42@hotmail.com

View 2 Replies View Related

Server Locking Up Everytime A DBCC DBREINDEX Is Issued

Apr 6, 2001

Every time I execute DBCC DBREINDEX (Table_Name, '',0)WITH NO_INFOMSGS the server hangs. No error messages in windows 2000 logs or in the SQL 7.0 logs.

The table is about 2 gig in size and the index is a clustered index on two fields.

The server has 1 gig of memory and the cache hit ratio, and cache flushes both look good. Any ideas? Any help would be greatly appreciated.

View 2 Replies View Related

Can't Delete Database That Has Been Part Of Replication

Feb 7, 2007

I had a publication (merge) setup for a database. Deleted the publication and tried to delete the database. Sql server says it can't be deleted because it's has replication setup. Bug??

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved