I have a sql script that I use to reorganize indexes with more than 5%
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.
My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?
My script --
BEGIN
SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
INTO #INDEX_STATS_TEMP
FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
NULL, NULL)
WHERE avg_fragmentation_in_percent >= 5
AND index_type_desc != 'HEAP'
-- DECLARE LOCAL VARIABLES FOR THE CURSOR
DECLARE @.dbID int,
@.tableID int,
@.indexID int,
@.frag_percent float,
@.index_name varchar(100),
@.table_name varchar(100),
@.sql varchar(1000)
--DEFINE THE CURSOR
DECLARE FRAG_CURSOR CURSOR
FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
print @.tableID
SELECT @.table_name = name from DB_NAME.sys.objects where
object_id = @.tableID and type = 'U'
IF (@.frag_percent <=30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REORGANIZE'
print @.SQL
exec (@.SQL)
END
ELSE IF (@.frag_percent > 30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REBUILD WITH (ONLINE = OFF)'
print @.SQL
exec (@.SQL)
END
SET @.SQL = NULL
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
DROP TABLE #INDEX_STATS_TEMP
END
Regards
JaideepCheck the size of the index. I exclude indexes below a particular cutoff
(don't exactly recall what it is) because fragmentation information was not
meaningful.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||Try doing update statistics. Statistics will automatically get updated when
an index is rebuilt, but I don't think they do when an index is reorganized
or defragged.
--
MG
"Geoff N. Hiten" wrote:
> Check the size of the index. I exclude indexes below a particular cutoff
> (don't exactly recall what it is) because fragmentation information was not
> meaningful.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
> >I have a sql script that I use to reorganize indexes with more than 5%
> > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> > fragmentation. On running this, I found that, the script was working fine
> > but
> > every iteration updated about 550 indexes. After a run, if I queried the
> > dynamic management view, it still gave back indexes which were fragmented.
> >
> > My understanding is that after I do a reorg/rebuild, the entry should
> > disappear from sys.dm_db_index_physical_stats, if the alter table
> > succeeds.
> >
> > Question -- what can I do as a DBA, after the script runs to make sure
> > that
> > the management view gives back updated information and not state
> > information?
> >
> > My script --
> >
> > BEGIN
> >
> > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> >
> > INTO #INDEX_STATS_TEMP
> >
> > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> > NULL, NULL)
> >
> > WHERE avg_fragmentation_in_percent >= 5
> >
> > AND index_type_desc != 'HEAP'
> >
> >
> >
> >
> >
> > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> >
> > DECLARE @.dbID int,
> >
> > @.tableID int,
> >
> > @.indexID int,
> >
> > @.frag_percent float,
> >
> > @.index_name varchar(100),
> >
> > @.table_name varchar(100),
> >
> > @.sql varchar(1000)
> >
> >
> >
> > --DEFINE THE CURSOR
> >
> > DECLARE FRAG_CURSOR CURSOR
> >
> > FOR SELECT
> > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> >
> > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> >
> > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
> >
> >
> >
> > OPEN FRAG_CURSOR
> >
> > FETCH NEXT FROM FRAG_CURSOR INTO
> > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> >
> >
> >
> > WHILE (@.@.FETCH_STATUS = 0)
> >
> > BEGIN
> >
> > print @.tableID
> >
> > SELECT @.table_name = name from DB_NAME.sys.objects where
> > object_id = @.tableID and type = 'U'
> >
> > IF (@.frag_percent <=30)
> >
> > BEGIN
> >
> > USE DB_NAME
> >
> > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > REORGANIZE'
> >
> > print @.SQL
> >
> > exec (@.SQL)
> >
> > END
> >
> > ELSE IF (@.frag_percent > 30)
> >
> > BEGIN
> >
> > USE DB_NAME
> >
> > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > REBUILD WITH (ONLINE = OFF)'
> >
> > print @.SQL
> >
> > exec (@.SQL)
> >
> > END
> >
> > SET @.SQL = NULL
> >
> > FETCH NEXT FROM FRAG_CURSOR INTO
> > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> >
> > END
> >
> > CLOSE FRAG_CURSOR
> >
> > DEALLOCATE FRAG_CURSOR
> >
> > DROP TABLE #INDEX_STATS_TEMP
> >
> > END
> >
> > Regards
> >
> > Jaideep
> >
> >
> >
> >
>|||Thanks. I will update the statistics. What does the index size have to
defragmentation? Even if the size is less, but is spanning multiple pages,
the fragmentation can happen. I did not understand why did the management
view not get updated after the alter table? Is it solely because update
statistics was not run? Does anyone know how does the management view get
it's data?
Regards
Jaideep
"Hurme" wrote:
> Try doing update statistics. Statistics will automatically get updated when
> an index is rebuilt, but I don't think they do when an index is reorganized
> or defragged.
> --
> MG
>
> "Geoff N. Hiten" wrote:
> > Check the size of the index. I exclude indexes below a particular cutoff
> > (don't exactly recall what it is) because fragmentation information was not
> > meaningful.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
> > >I have a sql script that I use to reorganize indexes with more than 5%
> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> > > fragmentation. On running this, I found that, the script was working fine
> > > but
> > > every iteration updated about 550 indexes. After a run, if I queried the
> > > dynamic management view, it still gave back indexes which were fragmented.
> > >
> > > My understanding is that after I do a reorg/rebuild, the entry should
> > > disappear from sys.dm_db_index_physical_stats, if the alter table
> > > succeeds.
> > >
> > > Question -- what can I do as a DBA, after the script runs to make sure
> > > that
> > > the management view gives back updated information and not state
> > > information?
> > >
> > > My script --
> > >
> > > BEGIN
> > >
> > > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> > >
> > > INTO #INDEX_STATS_TEMP
> > >
> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> > > NULL, NULL)
> > >
> > > WHERE avg_fragmentation_in_percent >= 5
> > >
> > > AND index_type_desc != 'HEAP'
> > >
> > >
> > >
> > >
> > >
> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> > >
> > > DECLARE @.dbID int,
> > >
> > > @.tableID int,
> > >
> > > @.indexID int,
> > >
> > > @.frag_percent float,
> > >
> > > @.index_name varchar(100),
> > >
> > > @.table_name varchar(100),
> > >
> > > @.sql varchar(1000)
> > >
> > >
> > >
> > > --DEFINE THE CURSOR
> > >
> > > DECLARE FRAG_CURSOR CURSOR
> > >
> > > FOR SELECT
> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> > >
> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> > >
> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
> > >
> > >
> > >
> > > OPEN FRAG_CURSOR
> > >
> > > FETCH NEXT FROM FRAG_CURSOR INTO
> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> > >
> > >
> > >
> > > WHILE (@.@.FETCH_STATUS = 0)
> > >
> > > BEGIN
> > >
> > > print @.tableID
> > >
> > > SELECT @.table_name = name from DB_NAME.sys.objects where
> > > object_id = @.tableID and type = 'U'
> > >
> > > IF (@.frag_percent <=30)
> > >
> > > BEGIN
> > >
> > > USE DB_NAME
> > >
> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > > REORGANIZE'
> > >
> > > print @.SQL
> > >
> > > exec (@.SQL)
> > >
> > > END
> > >
> > > ELSE IF (@.frag_percent > 30)
> > >
> > > BEGIN
> > >
> > > USE DB_NAME
> > >
> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > > REBUILD WITH (ONLINE = OFF)'
> > >
> > > print @.SQL
> > >
> > > exec (@.SQL)
> > >
> > > END
> > >
> > > SET @.SQL = NULL
> > >
> > > FETCH NEXT FROM FRAG_CURSOR INTO
> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> > >
> > > END
> > >
> > > CLOSE FRAG_CURSOR
> > >
> > > DEALLOCATE FRAG_CURSOR
> > >
> > > DROP TABLE #INDEX_STATS_TEMP
> > >
> > > END
> > >
> > > Regards
> > >
> > > Jaideep
> > >
> > >
> > >
> > >
> >
> >|||In a trivially sized index (< 8 pages) the fragmentation information is
irrelevant. They will never be perfectly defragmented.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...
> Thanks. I will update the statistics. What does the index size have to
> defragmentation? Even if the size is less, but is spanning multiple pages,
> the fragmentation can happen. I did not understand why did the management
> view not get updated after the alter table? Is it solely because update
> statistics was not run? Does anyone know how does the management view get
> it's data?
> Regards
> Jaideep
> "Hurme" wrote:
>> Try doing update statistics. Statistics will automatically get updated
>> when
>> an index is rebuilt, but I don't think they do when an index is
>> reorganized
>> or defragged.
>> --
>> MG
>>
>> "Geoff N. Hiten" wrote:
>> > Check the size of the index. I exclude indexes below a particular
>> > cutoff
>> > (don't exactly recall what it is) because fragmentation information was
>> > not
>> > meaningful.
>> >
>> > --
>> > Geoff N. Hiten
>> > Senior SQL Infrastructure Consultant
>> > Microsoft SQL Server MVP
>> >
>> >
>> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
>> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>> > >I have a sql script that I use to reorganize indexes with more than 5%
>> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
>> > > fragmentation. On running this, I found that, the script was working
>> > > fine
>> > > but
>> > > every iteration updated about 550 indexes. After a run, if I queried
>> > > the
>> > > dynamic management view, it still gave back indexes which were
>> > > fragmented.
>> > >
>> > > My understanding is that after I do a reorg/rebuild, the entry should
>> > > disappear from sys.dm_db_index_physical_stats, if the alter table
>> > > succeeds.
>> > >
>> > > Question -- what can I do as a DBA, after the script runs to make
>> > > sure
>> > > that
>> > > the management view gives back updated information and not state
>> > > information?
>> > >
>> > > My script --
>> > >
>> > > BEGIN
>> > >
>> > > SELECT
>> > > database_id,object_id,index_id,avg_fragmentation_in_percent
>> > >
>> > > INTO #INDEX_STATS_TEMP
>> > >
>> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL,
>> > > NULL,
>> > > NULL, NULL)
>> > >
>> > > WHERE avg_fragmentation_in_percent >= 5
>> > >
>> > > AND index_type_desc != 'HEAP'
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
>> > >
>> > > DECLARE @.dbID int,
>> > >
>> > > @.tableID int,
>> > >
>> > > @.indexID int,
>> > >
>> > > @.frag_percent float,
>> > >
>> > > @.index_name varchar(100),
>> > >
>> > > @.table_name varchar(100),
>> > >
>> > > @.sql varchar(1000)
>> > >
>> > >
>> > >
>> > > --DEFINE THE CURSOR
>> > >
>> > > DECLARE FRAG_CURSOR CURSOR
>> > >
>> > > FOR SELECT
>> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
>> > >
>> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
>> > >
>> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id =>> > > S.INDEX_ID
>> > >
>> > >
>> > >
>> > > OPEN FRAG_CURSOR
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > >
>> > >
>> > > WHILE (@.@.FETCH_STATUS = 0)
>> > >
>> > > BEGIN
>> > >
>> > > print @.tableID
>> > >
>> > > SELECT @.table_name = name from DB_NAME.sys.objects where
>> > > object_id = @.tableID and type = 'U'
>> > >
>> > > IF (@.frag_percent <=30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON
>> > > '+@.table_name+'
>> > > REORGANIZE'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > ELSE IF (@.frag_percent > 30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON
>> > > '+@.table_name+'
>> > > REBUILD WITH (ONLINE = OFF)'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > SET @.SQL = NULL
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > > END
>> > >
>> > > CLOSE FRAG_CURSOR
>> > >
>> > > DEALLOCATE FRAG_CURSOR
>> > >
>> > > DROP TABLE #INDEX_STATS_TEMP
>> > >
>> > > END
>> > >
>> > > Regards
>> > >
>> > > Jaideep
>> > >
>> > >
>> > >
>> > >
>> >
>> >|||Are you perhaps using autogrowth to manage the size of your database? How
much free space do you have in the db? Without a LOT of free space,
reorg/rebuild cannot actually accomplish their tasks effectively because
there is no contiguous block of empty space to lay down the pages in order.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||... and for an elaboration of this, read about extents, shared extents etc in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OHaB1DSbIHA.5208@.TK2MSFTNGP04.phx.gbl...
> In a trivially sized index (< 8 pages) the fragmentation information is irrelevant. They will
> never be perfectly defragmented.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...
>> Thanks. I will update the statistics. What does the index size have to
>> defragmentation? Even if the size is less, but is spanning multiple pages,
>> the fragmentation can happen. I did not understand why did the management
>> view not get updated after the alter table? Is it solely because update
>> statistics was not run? Does anyone know how does the management view get
>> it's data?
>> Regards
>> Jaideep
>> "Hurme" wrote:
>> Try doing update statistics. Statistics will automatically get updated when
>> an index is rebuilt, but I don't think they do when an index is reorganized
>> or defragged.
>> --
>> MG
>>
>> "Geoff N. Hiten" wrote:
>> > Check the size of the index. I exclude indexes below a particular cutoff
>> > (don't exactly recall what it is) because fragmentation information was not
>> > meaningful.
>> >
>> > --
>> > Geoff N. Hiten
>> > Senior SQL Infrastructure Consultant
>> > Microsoft SQL Server MVP
>> >
>> >
>> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
>> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>> > >I have a sql script that I use to reorganize indexes with more than 5%
>> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
>> > > fragmentation. On running this, I found that, the script was working fine
>> > > but
>> > > every iteration updated about 550 indexes. After a run, if I queried the
>> > > dynamic management view, it still gave back indexes which were fragmented.
>> > >
>> > > My understanding is that after I do a reorg/rebuild, the entry should
>> > > disappear from sys.dm_db_index_physical_stats, if the alter table
>> > > succeeds.
>> > >
>> > > Question -- what can I do as a DBA, after the script runs to make sure
>> > > that
>> > > the management view gives back updated information and not state
>> > > information?
>> > >
>> > > My script --
>> > >
>> > > BEGIN
>> > >
>> > > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
>> > >
>> > > INTO #INDEX_STATS_TEMP
>> > >
>> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
>> > > NULL, NULL)
>> > >
>> > > WHERE avg_fragmentation_in_percent >= 5
>> > >
>> > > AND index_type_desc != 'HEAP'
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
>> > >
>> > > DECLARE @.dbID int,
>> > >
>> > > @.tableID int,
>> > >
>> > > @.indexID int,
>> > >
>> > > @.frag_percent float,
>> > >
>> > > @.index_name varchar(100),
>> > >
>> > > @.table_name varchar(100),
>> > >
>> > > @.sql varchar(1000)
>> > >
>> > >
>> > >
>> > > --DEFINE THE CURSOR
>> > >
>> > > DECLARE FRAG_CURSOR CURSOR
>> > >
>> > > FOR SELECT
>> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
>> > >
>> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
>> > >
>> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>> > >
>> > >
>> > >
>> > > OPEN FRAG_CURSOR
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > >
>> > >
>> > > WHILE (@.@.FETCH_STATUS = 0)
>> > >
>> > > BEGIN
>> > >
>> > > print @.tableID
>> > >
>> > > SELECT @.table_name = name from DB_NAME.sys.objects where
>> > > object_id = @.tableID and type = 'U'
>> > >
>> > > IF (@.frag_percent <=30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
>> > > REORGANIZE'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > ELSE IF (@.frag_percent > 30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
>> > > REBUILD WITH (ONLINE = OFF)'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > SET @.SQL = NULL
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > > END
>> > >
>> > > CLOSE FRAG_CURSOR
>> > >
>> > > DEALLOCATE FRAG_CURSOR
>> > >
>> > > DROP TABLE #INDEX_STATS_TEMP
>> > >
>> > > END
>> > >
>> > > Regards
>> > >
>> > > Jaideep
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>|||"bubai" wrote:
> I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure that
> the management view gives back updated information and not state information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||I am having the same problem, but I have a more specific example of the issue.
Here is some cut down output from dm_db_index_physical_stats
index_id alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent page_count
1 IN_ROW_DATA 3 0 50.16835017 297
1 IN_ROW_DATA 3 1 0 2
1 IN_ROW_DATA 3 2 0 1
2 IN_ROW_DATA 3 0 99.46236559 372
2 IN_ROW_DATA 3 1 0 3
2 IN_ROW_DATA 3 2 0 1
In case this doesn't display too well the key point is that at index level
zero there are 297 pages allocated to the clustered index and 372 pages to
the non clustered index.
Except the problem is that this table has no rows in it (although it used to
have)
I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
all to no avail,
However DBCC SHOWCONTIG returns the correct results.
Sorry I can't offer a solution but I hope this clarifies the issue I think
we both have.
Let me know if you find one.|||Sorry, just noticed a typo in the way I was doing this which explains the
results. Please ignore my previous post. Apologies again.
"Tim Walker" wrote:
> I am having the same problem, but I have a more specific example of the issue.
> Here is some cut down output from dm_db_index_physical_stats
> index_id alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent page_count
> 1 IN_ROW_DATA 3 0 50.16835017 297
> 1 IN_ROW_DATA 3 1 0 2
> 1 IN_ROW_DATA 3 2 0 1
> 2 IN_ROW_DATA 3 0 99.46236559 372
> 2 IN_ROW_DATA 3 1 0 3
> 2 IN_ROW_DATA 3 2 0 1
> In case this doesn't display too well the key point is that at index level
> zero there are 297 pages allocated to the clustered index and 372 pages to
> the non clustered index.
> Except the problem is that this table has no rows in it (although it used to
> have)
> I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
> all to no avail,
> However DBCC SHOWCONTIG returns the correct results.
> Sorry I can't offer a solution but I hope this clarifies the issue I think
> we both have.
> Let me know if you find one.
Showing posts with label online. Show all posts
Showing posts with label online. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
dm_db_index_physical_stats not getting updated
I have a sql script that I use to reorganize indexes with more than 5%
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.
My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?
My script --
BEGIN
SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
INTO #INDEX_STATS_TEMP
FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
NULL, NULL)
WHERE avg_fragmentation_in_percent >= 5
AND index_type_desc != 'HEAP'
-- DECLARE LOCAL VARIABLES FOR THE CURSOR
DECLARE @.dbID int,
@.tableID int,
@.indexID int,
@.frag_percent float,
@.index_name varchar(100),
@.table_name varchar(100),
@.sql varchar(1000)
--DEFINE THE CURSOR
DECLARE FRAG_CURSOR CURSOR
FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
print @.tableID
SELECT @.table_name = name from DB_NAME.sys.objects where
object_id = @.tableID and type = 'U'
IF (@.frag_percent <=30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REORGANIZE'
print @.SQL
exec (@.SQL)
END
ELSE IF (@.frag_percent > 30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REBUILD WITH (ONLINE = OFF)'
print @.SQL
exec (@.SQL)
END
SET @.SQL = NULL
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
DROP TABLE #INDEX_STATS_TEMP
END
Regards
Jaideep
Check the size of the index. I exclude indexes below a particular cutoff
(don't exactly recall what it is) because fragmentation information was not
meaningful.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||Try doing update statistics. Statistics will automatically get updated when
an index is rebuilt, but I don't think they do when an index is reorganized
or defragged.
MG
"Geoff N. Hiten" wrote:
> Check the size of the index. I exclude indexes below a particular cutoff
> (don't exactly recall what it is) because fragmentation information was not
> meaningful.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>
|||Thanks. I will update the statistics. What does the index size have to
defragmentation? Even if the size is less, but is spanning multiple pages,
the fragmentation can happen. I did not understand why did the management
view not get updated after the alter table? Is it solely because update
statistics was not run? Does anyone know how does the management view get
it's data?
Regards
Jaideep
"Hurme" wrote:
[vbcol=seagreen]
> Try doing update statistics. Statistics will automatically get updated when
> an index is rebuilt, but I don't think they do when an index is reorganized
> or defragged.
> --
> MG
>
> "Geoff N. Hiten" wrote:
|||In a trivially sized index (< 8 pages) the fragmentation information is
irrelevant. They will never be perfectly defragmented.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...[vbcol=seagreen]
> Thanks. I will update the statistics. What does the index size have to
> defragmentation? Even if the size is less, but is spanning multiple pages,
> the fragmentation can happen. I did not understand why did the management
> view not get updated after the alter table? Is it solely because update
> statistics was not run? Does anyone know how does the management view get
> it's data?
> Regards
> Jaideep
> "Hurme" wrote:
|||Are you perhaps using autogrowth to manage the size of your database? How
much free space do you have in the db? Without a LOT of free space,
reorg/rebuild cannot actually accomplish their tasks effectively because
there is no contiguous block of empty space to lay down the pages in order.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||"bubai" wrote:
> I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure that
> the management view gives back updated information and not state information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||I am having the same problem, but I have a more specific example of the issue.
Here is some cut down output from dm_db_index_physical_stats
index_idalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentpage_count
1IN_ROW_DATA3050.16835017297
1IN_ROW_DATA3102
1IN_ROW_DATA3201
2IN_ROW_DATA3099.46236559372
2IN_ROW_DATA3103
2IN_ROW_DATA3201
In case this doesn't display too well the key point is that at index level
zero there are 297 pages allocated to the clustered index and 372 pages to
the non clustered index.
Except the problem is that this table has no rows in it (although it used to
have)
I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
all to no avail,
However DBCC SHOWCONTIG returns the correct results.
Sorry I can't offer a solution but I hope this clarifies the issue I think
we both have.
Let me know if you find one.
|||Sorry, just noticed a typo in the way I was doing this which explains the
results. Please ignore my previous post. Apologies again.
"Tim Walker" wrote:
> I am having the same problem, but I have a more specific example of the issue.
> Here is some cut down output from dm_db_index_physical_stats
> index_idalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentpage_count
> 1IN_ROW_DATA3050.16835017297
> 1IN_ROW_DATA3102
> 1IN_ROW_DATA3201
> 2IN_ROW_DATA3099.46236559372
> 2IN_ROW_DATA3103
> 2IN_ROW_DATA3201
> In case this doesn't display too well the key point is that at index level
> zero there are 297 pages allocated to the clustered index and 372 pages to
> the non clustered index.
> Except the problem is that this table has no rows in it (although it used to
> have)
> I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
> all to no avail,
> However DBCC SHOWCONTIG returns the correct results.
> Sorry I can't offer a solution but I hope this clarifies the issue I think
> we both have.
> Let me know if you find one.
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.
My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?
My script --
BEGIN
SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
INTO #INDEX_STATS_TEMP
FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
NULL, NULL)
WHERE avg_fragmentation_in_percent >= 5
AND index_type_desc != 'HEAP'
-- DECLARE LOCAL VARIABLES FOR THE CURSOR
DECLARE @.dbID int,
@.tableID int,
@.indexID int,
@.frag_percent float,
@.index_name varchar(100),
@.table_name varchar(100),
@.sql varchar(1000)
--DEFINE THE CURSOR
DECLARE FRAG_CURSOR CURSOR
FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
print @.tableID
SELECT @.table_name = name from DB_NAME.sys.objects where
object_id = @.tableID and type = 'U'
IF (@.frag_percent <=30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REORGANIZE'
print @.SQL
exec (@.SQL)
END
ELSE IF (@.frag_percent > 30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REBUILD WITH (ONLINE = OFF)'
print @.SQL
exec (@.SQL)
END
SET @.SQL = NULL
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
DROP TABLE #INDEX_STATS_TEMP
END
Regards
Jaideep
Check the size of the index. I exclude indexes below a particular cutoff
(don't exactly recall what it is) because fragmentation information was not
meaningful.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||Try doing update statistics. Statistics will automatically get updated when
an index is rebuilt, but I don't think they do when an index is reorganized
or defragged.
MG
"Geoff N. Hiten" wrote:
> Check the size of the index. I exclude indexes below a particular cutoff
> (don't exactly recall what it is) because fragmentation information was not
> meaningful.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>
|||Thanks. I will update the statistics. What does the index size have to
defragmentation? Even if the size is less, but is spanning multiple pages,
the fragmentation can happen. I did not understand why did the management
view not get updated after the alter table? Is it solely because update
statistics was not run? Does anyone know how does the management view get
it's data?
Regards
Jaideep
"Hurme" wrote:
[vbcol=seagreen]
> Try doing update statistics. Statistics will automatically get updated when
> an index is rebuilt, but I don't think they do when an index is reorganized
> or defragged.
> --
> MG
>
> "Geoff N. Hiten" wrote:
|||In a trivially sized index (< 8 pages) the fragmentation information is
irrelevant. They will never be perfectly defragmented.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...[vbcol=seagreen]
> Thanks. I will update the statistics. What does the index size have to
> defragmentation? Even if the size is less, but is spanning multiple pages,
> the fragmentation can happen. I did not understand why did the management
> view not get updated after the alter table? Is it solely because update
> statistics was not run? Does anyone know how does the management view get
> it's data?
> Regards
> Jaideep
> "Hurme" wrote:
|||Are you perhaps using autogrowth to manage the size of your database? How
much free space do you have in the db? Without a LOT of free space,
reorg/rebuild cannot actually accomplish their tasks effectively because
there is no contiguous block of empty space to lay down the pages in order.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||"bubai" wrote:
> I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure that
> the management view gives back updated information and not state information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||I am having the same problem, but I have a more specific example of the issue.
Here is some cut down output from dm_db_index_physical_stats
index_idalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentpage_count
1IN_ROW_DATA3050.16835017297
1IN_ROW_DATA3102
1IN_ROW_DATA3201
2IN_ROW_DATA3099.46236559372
2IN_ROW_DATA3103
2IN_ROW_DATA3201
In case this doesn't display too well the key point is that at index level
zero there are 297 pages allocated to the clustered index and 372 pages to
the non clustered index.
Except the problem is that this table has no rows in it (although it used to
have)
I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
all to no avail,
However DBCC SHOWCONTIG returns the correct results.
Sorry I can't offer a solution but I hope this clarifies the issue I think
we both have.
Let me know if you find one.
|||Sorry, just noticed a typo in the way I was doing this which explains the
results. Please ignore my previous post. Apologies again.
"Tim Walker" wrote:
> I am having the same problem, but I have a more specific example of the issue.
> Here is some cut down output from dm_db_index_physical_stats
> index_idalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentpage_count
> 1IN_ROW_DATA3050.16835017297
> 1IN_ROW_DATA3102
> 1IN_ROW_DATA3201
> 2IN_ROW_DATA3099.46236559372
> 2IN_ROW_DATA3103
> 2IN_ROW_DATA3201
> In case this doesn't display too well the key point is that at index level
> zero there are 297 pages allocated to the clustered index and 372 pages to
> the non clustered index.
> Except the problem is that this table has no rows in it (although it used to
> have)
> I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
> all to no avail,
> However DBCC SHOWCONTIG returns the correct results.
> Sorry I can't offer a solution but I hope this clarifies the issue I think
> we both have.
> Let me know if you find one.
Labels:
30fragmentation,
5fragmentation,
database,
dm_db_index_physical_stats,
indexes,
microsoft,
mysql,
online,
oracle,
rebuild,
reorganize,
script,
server,
sql,
updated
Wednesday, March 7, 2012
distribution sync with backup
hi,
i am not certain what is purpose when the distribution
database is configured with sync with backup. i understand
from the help online that this will not truncate the
publication database log.
however, i can't achieve this.
this is what i did in my testing.
1) configure distribution and publication db with sych
with backup.
2) Backup the distribution db.
3) Add a record in the publication db.
4) Backup the publication db.
5) Make sure that the record is replicated in the
subscriber db.
6) delete the record in the subscriber db.
7) Restore the distribution db from the backup.
8) Backup the publication db.
i expect the missing record to be replicated to the
subscriber db. but it didn't. anything i missed out? or is
my understanding wrong?
thanks for any help.
-
leonghio (leonghio@.yahoo.com.sg)
This option prevents the backup of the distribution database getting ahead
of the backup of the publishing database. You need to add the record then do
the backups - publishing db first then distributor. Next delete the record
on the subscriber then do the restores and it should work ok with the new
record going down.
Regards,
Paul Ibison
|||i tried the following steps but still can't get the
results.
1) make sure both publication and distribution are sync
with backup
2) add a record to publication.
3) log backup publication.
4) full backup distribution.
5) delete the record from subscriber.
6) stop replication agents and restore distribution.
7) start replcation agents.
8) log backup publication again to re-initiate replication.
but record is not replicated again to the subscriber.
what seems to be the problem? am i getting the steps wrong?
thanks!
-
leonghio(leonghio@.yahoo.com.sg)
>--Original Message--
>This option prevents the backup of the distribution
database getting ahead
>of the backup of the publishing database. You need to add
the record then do
>the backups - publishing db first then distributor. Next
delete the record
>on the subscriber then do the restores and it should work
ok with the new
>record going down.
>Regards,
>Paul Ibison
>
>.
>
|||If the record has already gone to the subscriber it won't
be sent again. This is because MSreplication_subscriptions
holds a column called transaction_timestamp which is the
last one sent down. This value can be obtained from
msrepl_commands (the last 8 zeros aren't always seen), so
it would be possible to force the change by updating
MSreplication_subscriptions yourself to a previous
transaction if there is one.
HTH,
Paul Ibison
|||the way the sync with backup setting works is
1) a transaction is entered in the published databases
2) the transaction is written to the transaction log
3) the transaction log is dumped and during the dump the log reader will
then read the transaction from the log and write it to the distribution
database
"leonghio" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5001c4317f$efbf3630$7d02280a@.phx.gbl...
> hi,
> i am not certain what is purpose when the distribution
> database is configured with sync with backup. i understand
> from the help online that this will not truncate the
> publication database log.
> however, i can't achieve this.
> this is what i did in my testing.
> 1) configure distribution and publication db with sych
> with backup.
> 2) Backup the distribution db.
> 3) Add a record in the publication db.
> 4) Backup the publication db.
> 5) Make sure that the record is replicated in the
> subscriber db.
> 6) delete the record in the subscriber db.
> 7) Restore the distribution db from the backup.
> 8) Backup the publication db.
> i expect the missing record to be replicated to the
> subscriber db. but it didn't. anything i missed out? or is
> my understanding wrong?
> thanks for any help.
> -
> leonghio (leonghio@.yahoo.com.sg)
i am not certain what is purpose when the distribution
database is configured with sync with backup. i understand
from the help online that this will not truncate the
publication database log.
however, i can't achieve this.
this is what i did in my testing.
1) configure distribution and publication db with sych
with backup.
2) Backup the distribution db.
3) Add a record in the publication db.
4) Backup the publication db.
5) Make sure that the record is replicated in the
subscriber db.
6) delete the record in the subscriber db.
7) Restore the distribution db from the backup.
8) Backup the publication db.
i expect the missing record to be replicated to the
subscriber db. but it didn't. anything i missed out? or is
my understanding wrong?
thanks for any help.
-
leonghio (leonghio@.yahoo.com.sg)
This option prevents the backup of the distribution database getting ahead
of the backup of the publishing database. You need to add the record then do
the backups - publishing db first then distributor. Next delete the record
on the subscriber then do the restores and it should work ok with the new
record going down.
Regards,
Paul Ibison
|||i tried the following steps but still can't get the
results.
1) make sure both publication and distribution are sync
with backup
2) add a record to publication.
3) log backup publication.
4) full backup distribution.
5) delete the record from subscriber.
6) stop replication agents and restore distribution.
7) start replcation agents.
8) log backup publication again to re-initiate replication.
but record is not replicated again to the subscriber.
what seems to be the problem? am i getting the steps wrong?
thanks!
-
leonghio(leonghio@.yahoo.com.sg)
>--Original Message--
>This option prevents the backup of the distribution
database getting ahead
>of the backup of the publishing database. You need to add
the record then do
>the backups - publishing db first then distributor. Next
delete the record
>on the subscriber then do the restores and it should work
ok with the new
>record going down.
>Regards,
>Paul Ibison
>
>.
>
|||If the record has already gone to the subscriber it won't
be sent again. This is because MSreplication_subscriptions
holds a column called transaction_timestamp which is the
last one sent down. This value can be obtained from
msrepl_commands (the last 8 zeros aren't always seen), so
it would be possible to force the change by updating
MSreplication_subscriptions yourself to a previous
transaction if there is one.
HTH,
Paul Ibison
|||the way the sync with backup setting works is
1) a transaction is entered in the published databases
2) the transaction is written to the transaction log
3) the transaction log is dumped and during the dump the log reader will
then read the transaction from the log and write it to the distribution
database
"leonghio" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5001c4317f$efbf3630$7d02280a@.phx.gbl...
> hi,
> i am not certain what is purpose when the distribution
> database is configured with sync with backup. i understand
> from the help online that this will not truncate the
> publication database log.
> however, i can't achieve this.
> this is what i did in my testing.
> 1) configure distribution and publication db with sych
> with backup.
> 2) Backup the distribution db.
> 3) Add a record in the publication db.
> 4) Backup the publication db.
> 5) Make sure that the record is replicated in the
> subscriber db.
> 6) delete the record in the subscriber db.
> 7) Restore the distribution db from the backup.
> 8) Backup the publication db.
> i expect the missing record to be replicated to the
> subscriber db. but it didn't. anything i missed out? or is
> my understanding wrong?
> thanks for any help.
> -
> leonghio (leonghio@.yahoo.com.sg)
Labels:
backup,
certain,
configured,
database,
distribution,
distributiondatabase,
microsoft,
mysql,
online,
oracle,
purpose,
server,
sql,
sync,
understandfrom
Subscribe to:
Posts (Atom)