Hi friends,
Is it save to reindex (dbcc reindex) table system in db
master, msdb, model ?
Can we truncate datafile & log in tempdb ?
How to determine size of db in tempdb ?
Please advice.
Thanks.> Is it save to reindex (dbcc reindex) table system in db
> master, msdb, model ?
I wouldn't recommend this unless you have a very good backup in place. Why
do you think you need to reindex these tables?
> Can we truncate datafile & log in tempdb ?
Sure, see http://www.aspfaq.com/2446
> How to determine size of db in tempdb ?
Using Query Analyzer:
USE tempdb
GO
EXEC sp_spaceused|||Not only that but DBCC DBREINDEX does not allow system tables to be
reindexed (in SQL Server 2000)
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:e1bDwGooDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > Is it save to reindex (dbcc reindex) table system in db
> > master, msdb, model ?
> I wouldn't recommend this unless you have a very good backup in place.
Why
> do you think you need to reindex these tables?
> > Can we truncate datafile & log in tempdb ?
> Sure, see http://www.aspfaq.com/2446
> > How to determine size of db in tempdb ?
> Using Query Analyzer:
> USE tempdb
> GO
> EXEC sp_spaceused
>|||But I found , they are fragmented, this is one of them in
master db:
DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00%
[1:5]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 4760.0
- Avg. Page Density (full)................: 41.19%
If we cannot use reindex (dbcc reindex), can we defrag it
with dbcc defrag ? or just leave it as it is.
Thanks.
>--Original Message--
>Not only that but DBCC DBREINDEX does not allow system
tables to be
>reindexed (in SQL Server 2000)
>--
>Paul Randal
>DBCC Technical Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in
message
>news:e1bDwGooDHA.1096@.TK2MSFTNGP11.phx.gbl...
>> > Is it save to reindex (dbcc reindex) table system in
db
>> > master, msdb, model ?
>> I wouldn't recommend this unless you have a very good
backup in place.
>Why
>> do you think you need to reindex these tables?
>> > Can we truncate datafile & log in tempdb ?
>> Sure, see http://www.aspfaq.com/2446
>> > How to determine size of db in tempdb ?
>> Using Query Analyzer:
>> USE tempdb
>> GO
>> EXEC sp_spaceused
>>
>
>.
>|||There are only 8 pages in this table. You shouldn't worry about
fragmentation until you get several dozen pages at least.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kresna rudy kurniawan" <anonymous@.discussions.microsoft.com> wrote in
message news:096c01c3a28a$d412bc10$a401280a@.phx.gbl...
> But I found , they are fragmented, this is one of them in
> master db:
> DBCC SHOWCONTIG scanning 'sysaltfiles' table...
> Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
> TABLE level scan performed.
> - Pages Scanned........................: 8
> - Extents Scanned.......................: 5
> - Extent Switches.......................: 4
> - Avg. Pages per Extent..................: 1.6
> - Scan Density [Best Count:Actual Count]......: 20.00%
> [1:5]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 80.00%
> - Avg. Bytes Free per Page................: 4760.0
> - Avg. Page Density (full)................: 41.19%
> If we cannot use reindex (dbcc reindex), can we defrag it
> with dbcc defrag ? or just leave it as it is.
> Thanks.
>
> >--Original Message--
> >Not only that but DBCC DBREINDEX does not allow system
> tables to be
> >reindexed (in SQL Server 2000)
> >
> >--
> >Paul Randal
> >DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in
> message
> >news:e1bDwGooDHA.1096@.TK2MSFTNGP11.phx.gbl...
> >> > Is it save to reindex (dbcc reindex) table system in
> db
> >> > master, msdb, model ?
> >>
> >> I wouldn't recommend this unless you have a very good
> backup in place.
> >Why
> >> do you think you need to reindex these tables?
> >>
> >> > Can we truncate datafile & log in tempdb ?
> >>
> >> Sure, see http://www.aspfaq.com/2446
> >>
> >> > How to determine size of db in tempdb ?
> >>
> >> Using Query Analyzer:
> >>
> >> USE tempdb
> >> GO
> >> EXEC sp_spaceused
> >>
> >>
> >
> >
> >.
> >sql
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Issues with DBCC SHOWCONTIG
I am trying to resolve database fragmentation and I am having two issues wit
h
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is n
ot an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from
next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues wi
th
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, runnin
g
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
h
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is n
ot an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from
next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues wi
th
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, runnin
g
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
Issues with DBCC SHOWCONTIG
I am trying to resolve database fragmentation and I am having two issues with
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?
1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?
1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
Issues with DBCC SHOWCONTIG
I am trying to resolve database fragmentation and I am having two issues with
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
Friday, February 24, 2012
Issue in setting up the database Mirrioring environment for SQL Server 2005.
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
You must start up the sql service with trace flag 1400. It is not enough to use DBCC TRACE ON.
Thanks,
Mark
|||I've been encountering the same issue. I've set the trace flag on startup, and still have no luck.I've gone through every step in the online help, verified that BOTH server instances are listening (the pids corresponds to the ports) and I cannot shake this message:
"Database Mirroring Transport is disabled in the endpoint configuration."
The error comes following the command:
alter database AdventureWorks SET PARTNER = 'TCP://10.0.25.72:5022'
go
It does not seem to matter what port I enter, and it doesn't matter what the IP address is. I've tried this on both the Mirror and on the Primary (ie - in every order).
The mirroring endpoints seem be created properly. I've explored all steps in the msdn "Troubleshooting Database Mirroring Setup" document, checking the various system tables for status, etc. but it is to no avail.
I disabled the 'VIA' protocol in the Configuartion Manager, one of the hints I found from the various newsgroups i've browsed.
I am trying to mirror (without a witness) between two instances on the same server. I am well aware that this is not supported, but I need to kick the tires in a testing playground.
Is this a common problem? The lack of postings with this issue indicate that it may not be, but honestly, i'm stymied.
If anyone has encountered and solved this, please respond, and hopefully it will help others as well.
Cheers,
brentj
|||Can you just please tell use how to start the sql service with trace flag 1400.|||
chanmm wrote:
Can you just please tell use how to start the sql service with trace flag 1400.
You don't need the trace flag if you install SP1 which has now released.
Otherwise if SP1 isn't yet an option (which it really should be if you want to use db mirroring), you can add -T1400 as a startup parameter via the SQL Configuration Manager, properties of the SQL Server service -> advanced tab ->Startup Parameters.
it's not particularly well indexed or cross-referenced but BOL has details here :
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c82d19e5-5557-4235-9a70-37983607a1fd.htm
thx, Simon.
|||I'm wondering if the thread started for this post resolved this issue? I have set Trace Flag 1400 "-T1400;..." in startup configuration options, rebooted and restarted servers and still not able to mirror. Also given the error "Database Mirroring Transport is disabled."Anyone figure this out? Next I install SQL 2005 SP1.|||To update on my experience:
I installed SP1 and interestingly it even strips the Trace Flag 1400 out of the startup options for you so you don't leave that remnant of a failed Mirroring Attempt - good work guys!
I got an asynchronous mirror working after installing SQL 2005 SP1 but have not fully tested it.|||Sample is there in the updated SQL BOL docs. No gotchas except the fact that security configuration is the MOST critical. If you get that done, the rest is a breeze.
Issue in setting up the database Mirrioring environment for SQL Server 2005.
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
You must start up the sql service with trace flag 1400. It is not enough to use DBCC TRACE ON.
Thanks,
Mark
|||I've been encountering the same issue. I've set the trace flag on startup, and still have no luck.I've gone through every step in the online help, verified that BOTH server instances are listening (the pids corresponds to the ports) and I cannot shake this message:
"Database Mirroring Transport is disabled in the endpoint configuration."
The error comes following the command:
alter database AdventureWorks SET PARTNER = 'TCP://10.0.25.72:5022'
go
It does not seem to matter what port I enter, and it doesn't matter what the IP address is. I've tried this on both the Mirror and on the Primary (ie - in every order).
The mirroring endpoints seem be created properly. I've explored all steps in the msdn "Troubleshooting Database Mirroring Setup" document, checking the various system tables for status, etc. but it is to no avail.
I disabled the 'VIA' protocol in the Configuartion Manager, one of the hints I found from the various newsgroups i've browsed.
I am trying to mirror (without a witness) between two instances on the same server. I am well aware that this is not supported, but I need to kick the tires in a testing playground.
Is this a common problem? The lack of postings with this issue indicate that it may not be, but honestly, i'm stymied.
If anyone has encountered and solved this, please respond, and hopefully it will help others as well.
Cheers,
brentj
|||Can you just please tell use how to start the sql service with trace flag 1400.|||
Can you just please tell use how to start the sql service with trace flag 1400.
You don't need the trace flag if you install SP1 which has now released.
Otherwise if SP1 isn't yet an option (which it really should be if you want to use db mirroring), you can add -T1400 as a startup parameter via the SQL Configuration Manager, properties of the SQL Server service -> advanced tab ->Startup Parameters.
it's not particularly well indexed or cross-referenced but BOL has details here :
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c82d19e5-5557-4235-9a70-37983607a1fd.htm
thx, Simon.
|||I'm wondering if the thread started for this post resolved this issue? I have set Trace Flag 1400 "-T1400;..." in startup configuration options, rebooted and restarted servers and still not able to mirror. Also given the error "Database Mirroring Transport is disabled."Anyone figure this out? Next I install SQL 2005 SP1.|||To update on my experience:
I installed SP1 and interestingly it even strips the Trace Flag 1400 out of the startup options for you so you don't leave that remnant of a failed Mirroring Attempt - good work guys!
I got an asynchronous mirror working after installing SQL 2005 SP1 but have not fully tested it.|||Sample is there in the updated SQL BOL docs. No gotchas except the fact that security configuration is the MOST critical. If you get that done, the rest is a breeze.
Issue in setting up the database Mirrioring environment for SQL Server 2005.
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
You must start up the sql service with trace flag 1400. It is not enough to use DBCC TRACE ON.
Thanks,
Mark
|||I've been encountering the same issue. I've set the trace flag on startup, and still have no luck.I've gone through every step in the online help, verified that BOTH server instances are listening (the pids corresponds to the ports) and I cannot shake this message:
"Database Mirroring Transport is disabled in the endpoint configuration."
The error comes following the command:
alter database AdventureWorks SET PARTNER = 'TCP://10.0.25.72:5022'
go
It does not seem to matter what port I enter, and it doesn't matter what the IP address is. I've tried this on both the Mirror and on the Primary (ie - in every order).
The mirroring endpoints seem be created properly. I've explored all steps in the msdn "Troubleshooting Database Mirroring Setup" document, checking the various system tables for status, etc. but it is to no avail.
I disabled the 'VIA' protocol in the Configuartion Manager, one of the hints I found from the various newsgroups i've browsed.
I am trying to mirror (without a witness) between two instances on the same server. I am well aware that this is not supported, but I need to kick the tires in a testing playground.
Is this a common problem? The lack of postings with this issue indicate that it may not be, but honestly, i'm stymied.
If anyone has encountered and solved this, please respond, and hopefully it will help others as well.
Cheers,
brentj
|||Can you just please tell use how to start the sql service with trace flag 1400.|||
chanmm wrote:
Can you just please tell use how to start the sql service with trace flag 1400.
You don't need the trace flag if you install SP1 which has now released.
Otherwise if SP1 isn't yet an option (which it really should be if you want to use db mirroring), you can add -T1400 as a startup parameter via the SQL Configuration Manager, properties of the SQL Server service -> advanced tab ->Startup Parameters.
it's not particularly well indexed or cross-referenced but BOL has details here :
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c82d19e5-5557-4235-9a70-37983607a1fd.htm
thx, Simon.
|||I'm wondering if the thread started for this post resolved this issue? I have set Trace Flag 1400 "-T1400;..." in startup configuration options, rebooted and restarted servers and still not able to mirror. Also given the error "Database Mirroring Transport is disabled."Anyone figure this out? Next I install SQL 2005 SP1.|||To update on my experience:
I installed SP1 and interestingly it even strips the Trace Flag 1400 out of the startup options for you so you don't leave that remnant of a failed Mirroring Attempt - good work guys!
I got an asynchronous mirror working after installing SQL 2005 SP1 but have not fully tested it.|||Sample is there in the updated SQL BOL docs. No gotchas except the fact that security configuration is the MOST critical. If you get that done, the rest is a breeze.
Issue in setting up the database Mirrioring environment for SQL Server 2005.
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
You must start up the sql service with trace flag 1400. It is not enough to use DBCC TRACE ON.
Thanks,
Mark
|||I've been encountering the same issue. I've set the trace flag on startup, and still have no luck.I've gone through every step in the online help, verified that BOTH server instances are listening (the pids corresponds to the ports) and I cannot shake this message:
"Database Mirroring Transport is disabled in the endpoint configuration."
The error comes following the command:
alter database AdventureWorks SET PARTNER = 'TCP://10.0.25.72:5022'
go
It does not seem to matter what port I enter, and it doesn't matter what the IP address is. I've tried this on both the Mirror and on the Primary (ie - in every order).
The mirroring endpoints seem be created properly. I've explored all steps in the msdn "Troubleshooting Database Mirroring Setup" document, checking the various system tables for status, etc. but it is to no avail.
I disabled the 'VIA' protocol in the Configuartion Manager, one of the hints I found from the various newsgroups i've browsed.
I am trying to mirror (without a witness) between two instances on the same server. I am well aware that this is not supported, but I need to kick the tires in a testing playground.
Is this a common problem? The lack of postings with this issue indicate that it may not be, but honestly, i'm stymied.
If anyone has encountered and solved this, please respond, and hopefully it will help others as well.
Cheers,
brentj
|||Can you just please tell use how to start the sql service with trace flag 1400.|||
Can you just please tell use how to start the sql service with trace flag 1400.
You don't need the trace flag if you install SP1 which has now released.
Otherwise if SP1 isn't yet an option (which it really should be if you want to use db mirroring), you can add -T1400 as a startup parameter via the SQL Configuration Manager, properties of the SQL Server service -> advanced tab ->Startup Parameters.
it's not particularly well indexed or cross-referenced but BOL has details here :
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c82d19e5-5557-4235-9a70-37983607a1fd.htm
thx, Simon.
|||I'm wondering if the thread started for this post resolved this issue? I have set Trace Flag 1400 "-T1400;..." in startup configuration options, rebooted and restarted servers and still not able to mirror. Also given the error "Database Mirroring Transport is disabled."Anyone figure this out? Next I install SQL 2005 SP1.|||To update on my experience:
I installed SP1 and interestingly it even strips the Trace Flag 1400 out of the startup options for you so you don't leave that remnant of a failed Mirroring Attempt - good work guys!
I got an asynchronous mirror working after installing SQL 2005 SP1 but have not fully tested it.|||Sample is there in the updated SQL BOL docs. No gotchas except the fact that security configuration is the MOST critical. If you get that done, the rest is a breeze.
Issue in setting up the database Mirrioring environment for SQL Server 2005.
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
You must start up the sql service with trace flag 1400. It is not enough to use DBCC TRACE ON.
Thanks,
Mark
|||I've been encountering the same issue. I've set the trace flag on startup, and still have no luck.I've gone through every step in the online help, verified that BOTH server instances are listening (the pids corresponds to the ports) and I cannot shake this message:
"Database Mirroring Transport is disabled in the endpoint configuration."
The error comes following the command:
alter database AdventureWorks SET PARTNER = 'TCP://10.0.25.72:5022'
go
It does not seem to matter what port I enter, and it doesn't matter what the IP address is. I've tried this on both the Mirror and on the Primary (ie - in every order).
The mirroring endpoints seem be created properly. I've explored all steps in the msdn "Troubleshooting Database Mirroring Setup" document, checking the various system tables for status, etc. but it is to no avail.
I disabled the 'VIA' protocol in the Configuartion Manager, one of the hints I found from the various newsgroups i've browsed.
I am trying to mirror (without a witness) between two instances on the same server. I am well aware that this is not supported, but I need to kick the tires in a testing playground.
Is this a common problem? The lack of postings with this issue indicate that it may not be, but honestly, i'm stymied.
If anyone has encountered and solved this, please respond, and hopefully it will help others as well.
Cheers,
brentj
|||Can you just please tell use how to start the sql service with trace flag 1400.|||
Can you just please tell use how to start the sql service with trace flag 1400.
You don't need the trace flag if you install SP1 which has now released.
Otherwise if SP1 isn't yet an option (which it really should be if you want to use db mirroring), you can add -T1400 as a startup parameter via the SQL Configuration Manager, properties of the SQL Server service -> advanced tab ->Startup Parameters.
it's not particularly well indexed or cross-referenced but BOL has details here :
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c82d19e5-5557-4235-9a70-37983607a1fd.htm
thx, Simon.
|||I'm wondering if the thread started for this post resolved this issue? I have set Trace Flag 1400 "-T1400;..." in startup configuration options, rebooted and restarted servers and still not able to mirror. Also given the error "Database Mirroring Transport is disabled."Anyone figure this out? Next I install SQL 2005 SP1.|||To update on my experience:
I installed SP1 and interestingly it even strips the Trace Flag 1400 out of the startup options for you so you don't leave that remnant of a failed Mirroring Attempt - good work guys!
I got an asynchronous mirror working after installing SQL 2005 SP1 but have not fully tested it.|||Sample is there in the updated SQL BOL docs. No gotchas except the fact that security configuration is the MOST critical. If you get that done, the rest is a breeze.
Subscribe to:
Posts (Atom)