InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:11:04 GMT20http://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxthanks man, for giving me such a wonderful code. I didn't try yet but i think this is the solution of my problem. (in working hours i can't run this sp)i will let you know the result.thanks again:-)Mon, 07 Jan 2013 02:08:20 GMTKcVhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxthere is no query to see fragmentation.i used to see it in index --> rebuild or index -- > reorganize options.Mon, 07 Jan 2013 01:50:38 GMTKcVhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspx[quote][b]Perry Whittle (1/5/2013)[/b][hr][quote][b]KcV (1/4/2013)[/b][hr]Hi,I have just shifted my database of sql 2000 to sql 2008 64 bit after running Upgrade adviser.then i run DBCC DBREINDEX in all tables.After that when i checked manually then i found that about 80 % tables are well fragmented having 0 logical fragmentation but rest 20 % tables ware having fragmentation of 40 %,50 %,80 %,99 %.why it is so high ?after doing manual re-indexing and re-organizing 2 to 5 times , it is not coming to 0 %.why this is happening ?pls help.[/quote]can you post the fragmentation results please?[/quote]I'd still like to see the query that is used to get the fragmentation.Sat, 05 Jan 2013 10:54:53 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspx[quote][b]KcV (1/4/2013)[/b][hr]Hi,I have just shifted my database of sql 2000 to sql 2008 64 bit after running Upgrade adviser.then i run DBCC DBREINDEX in all tables.After that when i checked manually then i found that about 80 % tables are well fragmented having 0 logical fragmentation but rest 20 % tables ware having fragmentation of 40 %,50 %,80 %,99 %.why it is so high ?after doing manual re-indexing and re-organizing 2 to 5 times , it is not coming to 0 %.why this is happening ?pls help.[/quote]can you post the fragmentation results please?Sat, 05 Jan 2013 05:07:47 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxFirst problem[quote][b]KcV (1/5/2013)[/b][hr] ---- Set Recovery Mode SIMPLE Begin ALTER DATABASE aaa SET RECOVERY SIMPLE; End[/quote]Breaking the log chain, preventing any point in time restores until the DB is switched back to full and a full or diff backup is taken, plus means that log backups can't restore over this time. This is not a good idea[quote] ---- Re-Indexing of all tables of Database<nip>[/quote]So where do you check fragmentation? Looks like a blanket rebuild of everything, whether it needs rebuilding or not.May I suggest you replace that with one of these:[url]http://ola.hallengren.com/Versions.html[/url][url]http://sqlfool.com/2011/06/index-defrag-script-v4-1/[/url]Sat, 05 Jan 2013 01:55:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxHere sp,ALTER Procedure [dbo].[DBA_DBCC_DBREINDEX_UPDATESTAT]AsBEGIN ---- Set Recovery Mode SIMPLE Begin ALTER DATABASE aaa SET RECOVERY SIMPLE; End ---- Re-Indexing of all tables of Database Begin declare @tabName sysname, @schname sysname --declare cur_index cursor for select s.name, t.Name from Sys.Tables t inner join sys.schemas s on t.schema_id = s.schema_id order by t.Create_Date -- for 2005 declare cur_index cursor for SELECT sysusers.name,sysobjects.name FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid where sysusers.name='dbo' and xtype ='U' order by sysobjects.crdate -- for 2000 Open cur_index fetch next from cur_index into @schname, @tabname while @@Fetch_status = 0 begin print ('DBCC DBREINDEX (['+ @schname + '.'+ @tabname +'])') exec('DBCC DBREINDEX (['+ @schname + '.'+ @tabname +'])') fetch next from cur_index into @schname,@tabname end Close cur_index deallocate cur_index EXEC sp_updatestats End ---- Set Recovery Mode FULL Begin ALTER DATABASE aaa SET RECOVERY FULL; EndENDSat, 05 Jan 2013 00:01:00 GMTKcVhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxWhat is the query you are using to determine fragmentation?Fri, 04 Jan 2013 23:52:20 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxFirst of all thanks for reply.What is the size of some of these tables? - some of tables are having 100 to 2000000 rowsHow many pages? - i don't know about pages. actually i don't know how to see it. where i can find it ?Is there a Clustered Index on the tables in question? - Yes, there are clustered indexes and non clustered too.Fri, 04 Jan 2013 23:44:39 GMTKcVhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxWhat is the size of some of these tables? How many pages? Is there a Clustered Index on the tables in question?Fri, 04 Jan 2013 23:28:13 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403194-391-1.aspxHi,I have just shifted my database of sql 2000 to sql 2008 64 bit after running Upgrade adviser.then i run DBCC DBREINDEX in all tables.After that when i checked manually then i found that about 80 % tables are well fragmented having 0 logical fragmentation but rest 20 % tables ware having fragmentation of 40 %,50 %,80 %,99 %.why it is so high ?after doing manual re-indexing and re-organizing 2 to 5 times , it is not coming to 0 %.why this is happening ?pls help.Fri, 04 Jan 2013 22:50:57 GMTKcV