.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Reindexing

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
 

I noticed that I have some indexes which, when rebuilt, have more fragmentation than before the reindexing. Why is this happening? There are also cases where there is no change at all in the average fragmentation. I use the DBCC DBREINDEX ("tablename", ', 70) command.




View Complete Post


More Related Resource Links

Indexing or Reindexing question

  
We use SQL for our product and our clients are begining to complain that the indexing or reindexing operation is locking the database until it is finished.   Two questions. Is this normal? Is there a way to index or reindex before the database performance comes to a crawl while still using the database on a 7X24 schedule?

reindexing errors

  

I run 2 scripts to reindex tables in our databases. The first populates a table based on sys.dm_db_index_physical_stats and the second tries to use alter index rebuild but continues to throw an error. I have used clustered for  i.type_desc and then I used nonclustered but both give the same error.

Is there a better way to find indexes with LOB DATA? Am I better off using a reorginize vs. rebuild w/ LOB compaction in this case?

SET NOCOUNT ON

DECLARE @cmd NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @status int

if object_id('master..defrag')>0
    truncate table defrag 

DECLARE DBName_Process CURSOR STATIC FOR
SELECT name as [Jobname]
FROM sysdatabases
WHERE name not in ('Master', 'Model','MSDB', 'TempDB')
ORDER BY [name]

OPEN DBName_Process

FETCH NEXT FROM DBName_Process INTO @DBName
SET @STATUS = @@FETCH_STATUS

WHILE @STATUS=0
BEGIN

set @cmd = 'use ['+@DBNAME +']; SET NOCOUNT ON; SELECT OBJECT_NAME(i.object_id) as dbtbl, db_name(database_id) as DatabaseName, alloc_unit_type_desc as unit,  index_type_desc as indextype FROM sys.dm_db_index_physical_stats (DB_ID(N'''+ @DBNAME + '''), NULL, NULL, NULL ,''Limited'') phystat '
+ ' JOIN sys.indexes i on i.object_id = phystat.o

DB Reindexing script makes the system down

  

Hi,

I am running the following scripts to reindex my database tables

sp_MSforeachtable @command1="print '?' dbcc dbreindex ('?')"

then

sp_msforeachtable 'update statistics? with fullscan'

to update the stats.

During the execution of first script it hangs down my production server for about 1 hour.

Can anyone tell me any alternate for this so that my production sever does not stop responding during re indexing?

Thanks in advance.



Transactional Replication with Database reindexing

  
We are using SQL 2005 and are setting a large database for transactional replication. If the publishing database is reindexed using a Maintenaince Plan that reindexes all tables, will the commands for reindexing be sent to the subscriber as well?
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend