DBCC READPAGE

The syntax for the DBCC READPAGE command is
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt =
{ 0 | 1} ])
Does anyone know what the "formatstr" parameter is used for, and what are is
options?
Thanks
Barry McAuslinIt's an undocumented command used for testing internally - what are you
trying to do with it?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> The syntax for the DBCC READPAGE command is
> DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt =
> { 0 | 1} ])
> Does anyone know what the "formatstr" parameter is used for, and what are

---

is

quote:

---

> options?
> Thanks
> Barry McAuslin
>

---

|||I am trying to get the GAM in binary form. I am using DBCC PAGE but this
returns the whole page as text. This is proving to be a bit slow over a
WAN. I would like to get the GAM with as little network traffic as
possible.
So why do I want the GAM? I am working on a tool for SQL Server that Oracle
has that I found quite useful. If you go to www.sqlfe.com you will see what
it is.
Thanks
Barry McAuslin
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> It's an undocumented command used for testing internally - what are you
> trying to do with it?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no

---

rights.

quote:

---

> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
=[QUOTE]
are[QUOTE]
> is
>

---

|||DBCC READPAGE won't do what you want. Unfortunately there's no supported way
to get pages in binary form. I can forward you contact details if you're
interested in licensing on-disk internals information etc.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I am trying to get the GAM in binary form. I am using DBCC PAGE but this
> returns the whole page as text. This is proving to be a bit slow over a
> WAN. I would like to get the GAM with as little network traffic as
> possible.
> So why do I want the GAM? I am working on a tool for SQL Server that

---

Oracle

quote:

---

> has that I found quite useful. If you go to www.sqlfe.com you will see

---

what

quote:

---

> it is.
> Thanks
> Barry McAuslin
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> rights.
printopt[QUOTE]
> =
> are
>

---

|||Sure, you can contact me via the details on this page
http://www.sqlfe.com/contact.asp
Thanks
Barry
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...

quote:

---

> DBCC READPAGE won't do what you want. Unfortunately there's no supported

---

way

quote:

---

> to get pages in binary form. I can forward you contact details if you're
> interested in licensing on-disk internals information etc.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no

---

rights.

quote:

---

> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
this[QUOTE]
> Oracle
> what
you[QUOTE]
> printopt
what[QUOTE]
>

---

|||An update for you - the printopt parameter is explained on pg 249 of Inside
Microsoft SQL Server 2000. I checked about the licensing details and we're
not currently entering into any new agreements for this information.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:#yCW18i4DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Sure, you can contact me via the details on this page
> http://www.sqlfe.com/contact.asp
> Thanks
> Barry
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> way
> rights.
> this
a[QUOTE]
see[QUOTE]
> you
> what
>

---

DBCC READPAGE

The syntax for the DBCC READPAGE command is
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
Does anyone know what the "formatstr" parameter is used for, and what are is
options?
Thanks
Barry McAuslinIt's an undocumented command used for testing internally - what are you
trying to do with it?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The syntax for the DBCC READPAGE command is
> DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt => { 0 | 1} ])
> Does anyone know what the "formatstr" parameter is used for, and what are
is
> options?
> Thanks
> Barry McAuslin
>|||I am trying to get the GAM in binary form. I am using DBCC PAGE but this
returns the whole page as text. This is proving to be a bit slow over a
WAN. I would like to get the GAM with as little network traffic as
possible.
So why do I want the GAM? I am working on a tool for SQL Server that Oracle
has that I found quite useful. If you go to www.sqlfe.com you will see what
it is.
Thanks
Barry McAuslin
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> It's an undocumented command used for testing internally - what are you
> trying to do with it?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > The syntax for the DBCC READPAGE command is
> >
> > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt
=> > { 0 | 1} ])
> >
> > Does anyone know what the "formatstr" parameter is used for, and what
are
> is
> > options?
> >
> > Thanks
> >
> > Barry McAuslin
> >
> >
>|||DBCC READPAGE won't do what you want. Unfortunately there's no supported way
to get pages in binary form. I can forward you contact details if you're
interested in licensing on-disk internals information etc.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I am trying to get the GAM in binary form. I am using DBCC PAGE but this
> returns the whole page as text. This is proving to be a bit slow over a
> WAN. I would like to get the GAM with as little network traffic as
> possible.
> So why do I want the GAM? I am working on a tool for SQL Server that
Oracle
> has that I found quite useful. If you go to www.sqlfe.com you will see
what
> it is.
> Thanks
> Barry McAuslin
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > It's an undocumented command used for testing internally - what are you
> > trying to do with it?
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > The syntax for the DBCC READPAGE command is
> > >
> > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
printopt
> => > > { 0 | 1} ])
> > >
> > > Does anyone know what the "formatstr" parameter is used for, and what
> are
> > is
> > > options?
> > >
> > > Thanks
> > >
> > > Barry McAuslin
> > >
> > >
> >
> >
>|||Sure, you can contact me via the details on this page
http://www.sqlfe.com/contact.asp
Thanks
Barry
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> DBCC READPAGE won't do what you want. Unfortunately there's no supported
way
> to get pages in binary form. I can forward you contact details if you're
> interested in licensing on-disk internals information etc.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > I am trying to get the GAM in binary form. I am using DBCC PAGE but
this
> > returns the whole page as text. This is proving to be a bit slow over a
> > WAN. I would like to get the GAM with as little network traffic as
> > possible.
> >
> > So why do I want the GAM? I am working on a tool for SQL Server that
> Oracle
> > has that I found quite useful. If you go to www.sqlfe.com you will see
> what
> > it is.
> >
> > Thanks
> >
> > Barry McAuslin
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > It's an undocumented command used for testing internally - what are
you
> > > trying to do with it?
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > > The syntax for the DBCC READPAGE command is
> > > >
> > > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
> printopt
> > => > > > { 0 | 1} ])
> > > >
> > > > Does anyone know what the "formatstr" parameter is used for, and
what
> > are
> > > is
> > > > options?
> > > >
> > > > Thanks
> > > >
> > > > Barry McAuslin
> > > >
> > > >
> > >
> > >
> >
> >
>|||An update for you - the printopt parameter is explained on pg 249 of Inside
Microsoft SQL Server 2000. I checked about the licensing details and we're
not currently entering into any new agreements for this information.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:#yCW18i4DHA.2692@.TK2MSFTNGP09.phx.gbl...
> Sure, you can contact me via the details on this page
> http://www.sqlfe.com/contact.asp
> Thanks
> Barry
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> > DBCC READPAGE won't do what you want. Unfortunately there's no supported
> way
> > to get pages in binary form. I can forward you contact details if you're
> > interested in licensing on-disk internals information etc.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > > I am trying to get the GAM in binary form. I am using DBCC PAGE but
> this
> > > returns the whole page as text. This is proving to be a bit slow over
a
> > > WAN. I would like to get the GAM with as little network traffic as
> > > possible.
> > >
> > > So why do I want the GAM? I am working on a tool for SQL Server that
> > Oracle
> > > has that I found quite useful. If you go to www.sqlfe.com you will
see
> > what
> > > it is.
> > >
> > > Thanks
> > >
> > > Barry McAuslin
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > > It's an undocumented command used for testing internally - what are
> you
> > > > trying to do with it?
> > > > --
> > > > Paul Randal
> > > > Dev Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > > > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > > > The syntax for the DBCC READPAGE command is
> > > > >
> > > > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
> > printopt
> > > => > > > > { 0 | 1} ])
> > > > >
> > > > > Does anyone know what the "formatstr" parameter is used for, and
> what
> > > are
> > > > is
> > > > > options?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Barry McAuslin
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

DBCC LOGINFO('DBname')

When I run DBCC LOGINFO('DBname') I get 2 and 0 for Status column. How do I
make value 2 as zero before I can truncate the logfile?
ThanksTry to backup the transaction log. then look again.

>--Original Message--
>When I run DBCC LOGINFO('DBname') I get 2 and 0 for
Status column. How do I make value 2 as zero before I can
truncate the logfile?
>Thanks
>.
>

DBCC DBREINDEX and FillFactor

My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 90% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks.
"John" <john@.aicinc.com> wrote in message
news:%23ss5hYFbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 90% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
>
The SHRINKDATABASE by default will leave 10% of your database as free space.
It also does a few other things similar to a defrag on a hard disk, but for
you database objects.
The DBREINDEX command that you specified tells the indexes to be rebuilt
with a fillfactor of 90%. This means that each index will use 90% of a
data page and leave 10% as free space for future growth. For non-clustered
indexes, this does do a whole lot to your overall database growth. For
clustered indexes however, this forces 10% free space at the leaf level of
the index (which is the actual table itself.).
Every table that you created a primary key on using default settings will
create a clustered index on that primary key column. So in essence, by
setting the fill-factor of those indexes to 90% forces up to a 10% growth in
the size of each table.
I hope this explanation helps.
Rick Sawtell
MCT, MCSD, MCDBA
|||Rick,
Sorry but that's a little misleading.

> The SHRINKDATABASE by default will leave 10% of your database as free
space.
> It also does a few other things similar to a defrag on a hard disk, but
for
> you database objects.
Actually a Shrink operation will fragment the objects not defrag. Shrinking
a database is the worst thing to do if your concerned with fragmentation and
the objects being contiguous on disk. Another thing, SHRINKDATABASE will
shrink both the Data files and the Log file to get the target amount and
does not discriminate between how much of each it will shrink . You should
consider using SHRINKFILE if shrinking is absolutely necessary so as you get
the control you want.

> The DBREINDEX command that you specified tells the indexes to be rebuilt
> with a fillfactor of 90%. This means that each index will use 90% of a
> data page and leave 10% as free space for future growth. For
non-clustered
> indexes, this does do a whole lot to your overall database growth.
A non clustered index is simply implemented behind the scenes as a table
with a clustered index using the data from the column(s) in the index
expression with the addition of the Clustered Index key value appended. It
will adhere to the same fill factor and actually requires less space and
trouble to rebuild than a clustered index.

> For
> clustered indexes however, this forces 10% free space at the leaf level of
> the index (which is the actual table itself.).
> Every table that you created a primary key on using default settings will
> create a clustered index on that primary key column. So in essence, by
> setting the fill-factor of those indexes to 90% forces up to a 10% growth
in
> the size of each table.
Any time you rebuild a clustered index you need at least 1.2 times the size
of the table in free space in the database or it will grow. And each
nonclustered index requires similar free space equal to the size of the
index. This is why it is so important to have lots of free space in the
database at all times and especially before reindexing. By reindexing all
the tables in a database with little free space it will force multiple
autogrows and end up with numbers like John reported.
Andrew J. Kelly SQL MVP
|||Thanks Andrew. I am still confused why it is doubling the space in my data
file (log file) instead of somewhere around 10% increase. We do have a lot
of indexes on each table.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> space.
> for
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get[vbcol=seagreen]
> the control you want.
> non-clustered
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
of[vbcol=seagreen]
will[vbcol=seagreen]
growth
> in
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>
|||Thanks Andrew. I read your reply again and I think I got it.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> space.
> for
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get[vbcol=seagreen]
> the control you want.
> non-clustered
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
of[vbcol=seagreen]
will[vbcol=seagreen]
growth
> in
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>
|||John,

> Thanks Andrew. I am still confused why it is doubling the space in my data
> file (log file) instead of somewhere around 10% increase. We do have a lot
> of indexes on each table.
OK do you mean data file or Log file? You say both. Data files hold the
actual data and have extensions such as .mdf and .ndf. Log files hold
transaction information and have a .ldf extension. And when you talk about
the database size are you referring to just the data file size or both the
data and Log files? You really need to treat them as two completely
separate things. It is likely your data file autogrew many times during the
reindexing operation and not just once. Each time it autogrows it gets
larger and then it will grow more the next time. 10% free space is usually
no where near the amount of free space required to do a Reindex properly.
Another thing to keep in mind is that the total free space in the database
does not really mean you can use it. For instance if your tables have a lot
of fragmentation or a very low fill factor that free space on those pages is
not useable for anything other than inserts to that particular table. A
reindex in table A can not use the free space from pages on table B and so
on.
Andrew J. Kelly SQL MVP
|||If not, please let us know what we can help with.
Andrew J. Kelly SQL MVP
"John" <john@.aicinc.com> wrote in message
news:eny1c3NbEHA.3792@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Andrew. I read your reply again and I think I got it.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
but[vbcol=seagreen]
> Shrinking
> and
will[vbcol=seagreen]
> should
> get
rebuilt[vbcol=seagreen]
a[vbcol=seagreen]
It[vbcol=seagreen]
level[vbcol=seagreen]
> of
> will
by[vbcol=seagreen]
> growth
> size
all
>

DBCC DBREINDEX and FillFactor

My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 90% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks."John" <john@.aicinc.com> wrote in message
news:%23ss5hYFbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 90% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
>
The SHRINKDATABASE by default will leave 10% of your database as free space.
It also does a few other things similar to a defrag on a hard disk, but for
you database objects.
The DBREINDEX command that you specified tells the indexes to be rebuilt
with a fillfactor of 90%. This means that each index will use 90% of a
data page and leave 10% as free space for future growth. For non-clustered
indexes, this does do a whole lot to your overall database growth. For
clustered indexes however, this forces 10% free space at the leaf level of
the index (which is the actual table itself.).
Every table that you created a primary key on using default settings will
create a clustered index on that primary key column. So in essence, by
setting the fill-factor of those indexes to 90% forces up to a 10% growth in
the size of each table.
I hope this explanation helps.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick,
Sorry but that's a little misleading.

> The SHRINKDATABASE by default will leave 10% of your database as free
space.
> It also does a few other things similar to a defrag on a hard disk, but
for
> you database objects.
Actually a Shrink operation will fragment the objects not defrag. Shrinking
a database is the worst thing to do if your concerned with fragmentation and
the objects being contiguous on disk. Another thing, SHRINKDATABASE will
shrink both the Data files and the Log file to get the target amount and
does not discriminate between how much of each it will shrink . You should
consider using SHRINKFILE if shrinking is absolutely necessary so as you get
the control you want.

> The DBREINDEX command that you specified tells the indexes to be rebuilt
> with a fillfactor of 90%. This means that each index will use 90% of a
> data page and leave 10% as free space for future growth. For
non-clustered
> indexes, this does do a whole lot to your overall database growth.
A non clustered index is simply implemented behind the scenes as a table
with a clustered index using the data from the column(s) in the index
expression with the addition of the Clustered Index key value appended. It
will adhere to the same fill factor and actually requires less space and
trouble to rebuild than a clustered index.

> For
> clustered indexes however, this forces 10% free space at the leaf level of
> the index (which is the actual table itself.).
> Every table that you created a primary key on using default settings will
> create a clustered index on that primary key column. So in essence, by
> setting the fill-factor of those indexes to 90% forces up to a 10% growth
in
> the size of each table.
Any time you rebuild a clustered index you need at least 1.2 times the size
of the table in free space in the database or it will grow. And each
nonclustered index requires similar free space equal to the size of the
index. This is why it is so important to have lots of free space in the
database at all times and especially before reindexing. By reindexing all
the tables in a database with little free space it will force multiple
autogrows and end up with numbers like John reported.
Andrew J. Kelly SQL MVP|||Thanks Andrew. I am still confused why it is doubling the space in my data
file (log file) instead of somewhere around 10% increase. We do have a lot
of indexes on each table.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> space.
> for
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get
> the control you want.
>
> non-clustered
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
>
of[vbcol=seagreen]
will[vbcol=seagreen]
growth[vbcol=seagreen]
> in
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>|||Thanks Andrew. I read your reply again and I think I got it.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> space.
> for
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get
> the control you want.
>
> non-clustered
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
>
of[vbcol=seagreen]
will[vbcol=seagreen]
growth[vbcol=seagreen]
> in
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>|||John,

> Thanks Andrew. I am still confused why it is doubling the space in my data
> file (log file) instead of somewhere around 10% increase. We do have a lot
> of indexes on each table.
OK do you mean data file or Log file? You say both. Data files hold the
actual data and have extensions such as .mdf and .ndf. Log files hold
transaction information and have a .ldf extension. And when you talk about
the database size are you referring to just the data file size or both the
data and Log files? You really need to treat them as two completely
separate things. It is likely your data file autogrew many times during the
reindexing operation and not just once. Each time it autogrows it gets
larger and then it will grow more the next time. 10% free space is usually
no where near the amount of free space required to do a Reindex properly.
Another thing to keep in mind is that the total free space in the database
does not really mean you can use it. For instance if your tables have a lot
of fragmentation or a very low fill factor that free space on those pages is
not useable for anything other than inserts to that particular table. A
reindex in table A can not use the free space from pages on table B and so
on.
Andrew J. Kelly SQL MVP|||If not, please let us know what we can help with.
Andrew J. Kelly SQL MVP
"John" <john@.aicinc.com> wrote in message
news:eny1c3NbEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Thanks Andrew. I read your reply again and I think I got it.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
but[vbcol=seagreen]
> Shrinking
> and
will[vbcol=seagreen]
> should
> get
rebuilt[vbcol=seagreen]
a[vbcol=seagreen]
It[vbcol=seagreen]
level[vbcol=seagreen]
> of
> will
by[vbcol=seagreen]
> growth
> size
all[vbcol=seagreen]
>

DBCC DBREINDEX and FillFactor

My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 90% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks."John" <john@.aicinc.com> wrote in message
news:%23ss5hYFbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 90% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
>
The SHRINKDATABASE by default will leave 10% of your database as free space.
It also does a few other things similar to a defrag on a hard disk, but for
you database objects.
The DBREINDEX command that you specified tells the indexes to be rebuilt
with a fillfactor of 90%. This means that each index will use 90% of a
data page and leave 10% as free space for future growth. For non-clustered
indexes, this does do a whole lot to your overall database growth. For
clustered indexes however, this forces 10% free space at the leaf level of
the index (which is the actual table itself.).
Every table that you created a primary key on using default settings will
create a clustered index on that primary key column. So in essence, by
setting the fill-factor of those indexes to 90% forces up to a 10% growth in
the size of each table.
I hope this explanation helps.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick,
Sorry but that's a little misleading.
> The SHRINKDATABASE by default will leave 10% of your database as free
space.
> It also does a few other things similar to a defrag on a hard disk, but
for
> you database objects.
Actually a Shrink operation will fragment the objects not defrag. Shrinking
a database is the worst thing to do if your concerned with fragmentation and
the objects being contiguous on disk. Another thing, SHRINKDATABASE will
shrink both the Data files and the Log file to get the target amount and
does not discriminate between how much of each it will shrink . You should
consider using SHRINKFILE if shrinking is absolutely necessary so as you get
the control you want.
> The DBREINDEX command that you specified tells the indexes to be rebuilt
> with a fillfactor of 90%. This means that each index will use 90% of a
> data page and leave 10% as free space for future growth. For
non-clustered
> indexes, this does do a whole lot to your overall database growth.
A non clustered index is simply implemented behind the scenes as a table
with a clustered index using the data from the column(s) in the index
expression with the addition of the Clustered Index key value appended. It
will adhere to the same fill factor and actually requires less space and
trouble to rebuild than a clustered index.
> For
> clustered indexes however, this forces 10% free space at the leaf level of
> the index (which is the actual table itself.).
> Every table that you created a primary key on using default settings will
> create a clustered index on that primary key column. So in essence, by
> setting the fill-factor of those indexes to 90% forces up to a 10% growth
in
> the size of each table.
Any time you rebuild a clustered index you need at least 1.2 times the size
of the table in free space in the database or it will grow. And each
nonclustered index requires similar free space equal to the size of the
index. This is why it is so important to have lots of free space in the
database at all times and especially before reindexing. By reindexing all
the tables in a database with little free space it will force multiple
autogrows and end up with numbers like John reported.
Andrew J. Kelly SQL MVP|||Thanks Andrew. I am still confused why it is doubling the space in my data
file (log file) instead of somewhere around 10% increase. We do have a lot
of indexes on each table.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> > The SHRINKDATABASE by default will leave 10% of your database as free
> space.
> > It also does a few other things similar to a defrag on a hard disk, but
> for
> > you database objects.
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get
> the control you want.
> >
> > The DBREINDEX command that you specified tells the indexes to be rebuilt
> > with a fillfactor of 90%. This means that each index will use 90% of a
> > data page and leave 10% as free space for future growth. For
> non-clustered
> > indexes, this does do a whole lot to your overall database growth.
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
> > For
> > clustered indexes however, this forces 10% free space at the leaf level
of
> > the index (which is the actual table itself.).
> >
> > Every table that you created a primary key on using default settings
will
> > create a clustered index on that primary key column. So in essence, by
> > setting the fill-factor of those indexes to 90% forces up to a 10%
growth
> in
> > the size of each table.
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>|||Thanks Andrew. I read your reply again and I think I got it.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> > The SHRINKDATABASE by default will leave 10% of your database as free
> space.
> > It also does a few other things similar to a defrag on a hard disk, but
> for
> > you database objects.
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get
> the control you want.
> >
> > The DBREINDEX command that you specified tells the indexes to be rebuilt
> > with a fillfactor of 90%. This means that each index will use 90% of a
> > data page and leave 10% as free space for future growth. For
> non-clustered
> > indexes, this does do a whole lot to your overall database growth.
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
> > For
> > clustered indexes however, this forces 10% free space at the leaf level
of
> > the index (which is the actual table itself.).
> >
> > Every table that you created a primary key on using default settings
will
> > create a clustered index on that primary key column. So in essence, by
> > setting the fill-factor of those indexes to 90% forces up to a 10%
growth
> in
> > the size of each table.
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>|||John,
> Thanks Andrew. I am still confused why it is doubling the space in my data
> file (log file) instead of somewhere around 10% increase. We do have a lot
> of indexes on each table.
OK do you mean data file or Log file? You say both. Data files hold the
actual data and have extensions such as .mdf and .ndf. Log files hold
transaction information and have a .ldf extension. And when you talk about
the database size are you referring to just the data file size or both the
data and Log files? You really need to treat them as two completely
separate things. It is likely your data file autogrew many times during the
reindexing operation and not just once. Each time it autogrows it gets
larger and then it will grow more the next time. 10% free space is usually
no where near the amount of free space required to do a Reindex properly.
Another thing to keep in mind is that the total free space in the database
does not really mean you can use it. For instance if your tables have a lot
of fragmentation or a very low fill factor that free space on those pages is
not useable for anything other than inserts to that particular table. A
reindex in table A can not use the free space from pages on table B and so
on.
--
Andrew J. Kelly SQL MVP|||If not, please let us know what we can help with.
--
Andrew J. Kelly SQL MVP
"John" <john@.aicinc.com> wrote in message
news:eny1c3NbEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Thanks Andrew. I read your reply again and I think I got it.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> > Rick,
> >
> > Sorry but that's a little misleading.
> >
> >
> > > The SHRINKDATABASE by default will leave 10% of your database as free
> > space.
> > > It also does a few other things similar to a defrag on a hard disk,
but
> > for
> > > you database objects.
> >
> > Actually a Shrink operation will fragment the objects not defrag.
> Shrinking
> > a database is the worst thing to do if your concerned with fragmentation
> and
> > the objects being contiguous on disk. Another thing, SHRINKDATABASE
will
> > shrink both the Data files and the Log file to get the target amount and
> > does not discriminate between how much of each it will shrink . You
> should
> > consider using SHRINKFILE if shrinking is absolutely necessary so as you
> get
> > the control you want.
> >
> > >
> > > The DBREINDEX command that you specified tells the indexes to be
rebuilt
> > > with a fillfactor of 90%. This means that each index will use 90% of
a
> > > data page and leave 10% as free space for future growth. For
> > non-clustered
> > > indexes, this does do a whole lot to your overall database growth.
> >
> > A non clustered index is simply implemented behind the scenes as a table
> > with a clustered index using the data from the column(s) in the index
> > expression with the addition of the Clustered Index key value appended.
It
> > will adhere to the same fill factor and actually requires less space and
> > trouble to rebuild than a clustered index.
> >
> > > For
> > > clustered indexes however, this forces 10% free space at the leaf
level
> of
> > > the index (which is the actual table itself.).
> > >
> > > Every table that you created a primary key on using default settings
> will
> > > create a clustered index on that primary key column. So in essence,
by
> > > setting the fill-factor of those indexes to 90% forces up to a 10%
> growth
> > in
> > > the size of each table.
> >
> >
> > Any time you rebuild a clustered index you need at least 1.2 times the
> size
> > of the table in free space in the database or it will grow. And each
> > nonclustered index requires similar free space equal to the size of the
> > index. This is why it is so important to have lots of free space in the
> > database at all times and especially before reindexing. By reindexing
all
> > the tables in a database with little free space it will force multiple
> > autogrows and end up with numbers like John reported.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> >
>

DBCC CHECKDB

Hello NG,
I need some assistance in the subject "repairing".
I always thought,
DBCC CHECKDB (' dbname ', REPAIR_ALLOW_DATA_LOSS)
would be the worst, which one could do to an SQL server data base.
But our installation (8.00.760 = SQL2000 with SP3 on W2000Server)
refuses accomplishing the repair even with a maximum loss and
answers:
[ 4 ] Data base XenoxDB: Data and
Indexverkn?pfung?berpr?fen... [ Microsoft SQL DMO (ODBC SQLState:
42000) ] Error 8929: [ Microsoft][ODBC SQL server Driver][SQL
Server]Objekt ID 2: Error in text ID 195789324288 found, their owner
data record RID = (1:1317:3) ID = 861506398 and indid = 1 is. [
Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
ID 2. The text -, ntext or image knots on side (1:5999), Slot 0, text
ID 195789324288 does not tune with its reference from side (1:10742),
Slot 10?berein. [ Microsoft][ODBC SQL server Driver][SQL
Server]Tabellenfehler: Object ID 2. The text -, ntext or image knots
on side (1:10742), Slot 10, text ID 58350370816 does not tune with its
reference from side (1:1317), Slot 3?berein. [ Microsoft][ODBC SQL
server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
caused that this elimination of errors became to?bergangen. [
Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
the DBCC instruction caused that this elimination of errors became
to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL server ] the RH
Pa IR stage in the DBCC instruction caused that this elimination of
errors became to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL
Server]CHECKDB found 0 reservation errors and 3 consistency error in
the sysindexes table (object ID 2). [ Microsoft][ODBC SQL server
Driver][SQL Server]CHECKDB found 0 reservation errors and 3
consistency error in the XenoxDB data base.
The following errors arose:
[ Microsoft][ODBC SQL server Driver][SQL Server]Objekt ID 2: Error in
text ID 195789324288 found, their owner data record RID = (1:1317:3)
ID = 861506398 and indid = 1 is. [ Microsoft][ODBC SQL server
Driver][SQL Server]Tabellenfehler: Object ID 2. The text -, ntext or
image knots on side (1:5999), Slot 0, text ID 195789324288 does not
tune with its reference from side (1:10742), Slot 10?berein. [
Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
ID 2. The text -, ntext or image knots on side (1:10742), Slot 10,
text ID 58350370816 does not tune with its reference from side
(1:1317), Slot 3?berein. [ Microsoft][ODBC SQL server Driver][SQL
server ] the RH Pa IR stage in the DBCC instruction caused that this
elimination of errors became to?bergangen. [ Microsoft][ODBC SQL
server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
caused that this elimination of errors became to?bergangen. [
Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
the DBCC instruction caused that this elimination of errors became
to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL Server]CHECKDB
found 0 reservation errors and 3 consistency error in the sysindexes
table (object ID 2). [ Microsoft][ODBC SQL server Driver][SQL
Server]CHECKDB found 0 reservation errors and 3 consistency error in
the XenoxDB data base. ** Ausf?hrungsdauer: 0 Std, 0 min, 3 sec. **
Someone an idea, how to repair the index nevertheless - perhaps
also on detours?
Best Regards
Thomas
Thomas,
I assume that you went through the process describe in the Books Online for
error 8929. At the end, after checking hardware, restoring a good backup,
and running DBCC with the appropriate repair option, it says:
"If running DBCC CHECKDB with one of the repair clauses does not correct the
problem, contact your primary support provider."
Russell Fields
"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
news:4124ADCB.4080006@.rwth-aachen.de...
> Hello NG,
> I need some assistance in the subject "repairing".
> I always thought,
> --
> DBCC CHECKDB (' dbname ', REPAIR_ALLOW_DATA_LOSS)
> --
> would be the worst, which one could do to an SQL server data base.
> But our installation (8.00.760 = SQL2000 with SP3 on W2000Server)
> refuses accomplishing the repair even with a maximum loss and
> answers:
> --
> [ 4 ] Data base XenoxDB: Data and
> Indexverkn?pfung?berpr?fen... [ Microsoft SQL DMO (ODBC SQLState:
> 42000) ] Error 8929: [ Microsoft][ODBC SQL server Driver][SQL
> Server]Objekt ID 2: Error in text ID 195789324288 found, their owner
> data record RID = (1:1317:3) ID = 861506398 and indid = 1 is. [
> Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
> ID 2. The text -, ntext or image knots on side (1:5999), Slot 0, text
> ID 195789324288 does not tune with its reference from side (1:10742),
> Slot 10?berein. [ Microsoft][ODBC SQL server Driver][SQL
> Server]Tabellenfehler: Object ID 2. The text -, ntext or image knots
> on side (1:10742), Slot 10, text ID 58350370816 does not tune with its
> reference from side (1:1317), Slot 3?berein. [ Microsoft][ODBC SQL
> server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
> caused that this elimination of errors became to?bergangen. [
> Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
> the DBCC instruction caused that this elimination of errors became
> to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL server ] the RH
> Pa IR stage in the DBCC instruction caused that this elimination of
> errors became to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL
> Server]CHECKDB found 0 reservation errors and 3 consistency error in
> the sysindexes table (object ID 2). [ Microsoft][ODBC SQL server
> Driver][SQL Server]CHECKDB found 0 reservation errors and 3
> consistency error in the XenoxDB data base.
> The following errors arose:
> [ Microsoft][ODBC SQL server Driver][SQL Server]Objekt ID 2: Error in
> text ID 195789324288 found, their owner data record RID = (1:1317:3)
> ID = 861506398 and indid = 1 is. [ Microsoft][ODBC SQL server
> Driver][SQL Server]Tabellenfehler: Object ID 2. The text -, ntext or
> image knots on side (1:5999), Slot 0, text ID 195789324288 does not
> tune with its reference from side (1:10742), Slot 10?berein. [
> Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
> ID 2. The text -, ntext or image knots on side (1:10742), Slot 10,
> text ID 58350370816 does not tune with its reference from side
> (1:1317), Slot 3?berein. [ Microsoft][ODBC SQL server Driver][SQL
> server ] the RH Pa IR stage in the DBCC instruction caused that this
> elimination of errors became to?bergangen. [ Microsoft][ODBC SQL
> server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
> caused that this elimination of errors became to?bergangen. [
> Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
> the DBCC instruction caused that this elimination of errors became
> to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL Server]CHECKDB
> found 0 reservation errors and 3 consistency error in the sysindexes
> table (object ID 2). [ Microsoft][ODBC SQL server Driver][SQL
> Server]CHECKDB found 0 reservation errors and 3 consistency error in
> the XenoxDB data base. ** Ausf?hrungsdauer: 0 Std, 0 min, 3 sec. **
> Someone an idea, how to repair the index nevertheless - perhaps
> also on detours?
> Best Regards
> Thomas
>
|||Russel,
I would have been the happiest man alive, if Id found any of that
books, you mention.
I didnt find any suggestions, how to get rid of errror 8929 :-(
I checked hardware, but the restore of a good backup would cause the
same data loss, that checkdb threatens with.
So, the contact your provider-option seemed to be the "last resort" -
but the answers of MS Germany didnt help us at all :-((
Best Regards
Thomas
Russell Fields schrieb:

>Thomas,
>I assume that you went through the process describe in the Books Online for
>error 8929. At the end, after checking hardware, restoring a good backup,
>and running DBCC with the appropriate repair option, it says:
>"If running DBCC CHECKDB with one of the repair clauses does not correct the
>problem, contact your primary support provider."
>Russell Fields
>"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
>news:4124ADCB.4080006@.rwth-aachen.de...
>
>
>
|||Have you tried running checkdb several times in succession? There are some
error combinations that the SQL Server 2000 repair isn't able to fix on the
first attempt (as other errors prvent the fix from running). I don't see
anything in the output you've posted that would suggest checkdb would have a
problem repairing these. Can you post the output from repair?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
news:4124BADD.4040502@.rwth-aachen.de...[vbcol=seagreen]
> Russel,
> I would have been the happiest man alive, if Id found any of that
> books, you mention.
> I didnt find any suggestions, how to get rid of errror 8929 :-(
> I checked hardware, but the restore of a good backup would cause the
> same data loss, that checkdb threatens with.
> So, the contact your provider-option seemed to be the "last resort" -
> but the answers of MS Germany didnt help us at all :-((
> Best Regards
> Thomas
>
> Russell Fields schrieb:
for[vbcol=seagreen]
backup,[vbcol=seagreen]
the
>
|||Paul S Randal [MS] schrieb:
> Have you tried running checkdb several times in succession? There are some
> error combinations that the SQL Server 2000 repair isn't able to fix on the
> first attempt (as other errors prvent the fix from running). I don't see
> anything in the output you've posted that would suggest checkdb would have a
> problem repairing these. Can you post the output from repair?
> Regards.
>
Paul,
hope youre still watching, so you can see what our SQL Server did
to me. I dont know, how many times I ran the procedures in
succussion :-(
Best Regards
Thomas
FYI:
--CHECKDB without repair--
Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
seinem Verweis von Seite (1:10742), Slot 10 berein.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
seinem Verweis von Seite (1:1317), Slot 3 berein.
DBCC-Ergebnis fr 'XenoxDB'.
DBCC-Ergebnis fr 'sysobjects'.
Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
DBCC-Ergebnis fr 'sysindexes'.
Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
sysindexes-Tabelle gefunden (Objekt-ID 2).
DBCC-Ergebnis fr 'syscolumns'.
Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
DBCC-Ergebnis fr 'systypes'.
Es sind 26 Zeilen in 1 Seiten fr das systypes-Objekt vorhanden.
DBCC-Ergebnis fr 'syscomments'.
Es sind 2828 Zeilen in 63 Seiten fr das syscomments-Objekt vorhanden.
----and so on ...
--CHECKDB repair_allow_data_loss--
Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
seinem Verweis von Seite (1:10742), Slot 10 berein.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
seinem Verweis von Seite (1:1317), Slot 3 berein.
DBCC-Ergebnis fr 'XenoxDB'.
DBCC-Ergebnis fr 'sysobjects'.
Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
DBCC-Ergebnis fr 'sysindexes'.
Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
diese Fehlerbehebung bergangen wurde.
Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
diese Fehlerbehebung bergangen wurde.
Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
diese Fehlerbehebung bergangen wurde.
Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
sysindexes-Tabelle gefunden (Objekt-ID 2).
DBCC-Ergebnis fr 'syscolumns'.
Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
DBCC-Ergebnis fr 'systypes'.
--Rest is similar to above output !
|||Ah - the errors are in sysindexes so that may be stopping from repairing
them. You're going to need to call Product Support to help you as we can't
do this through Usenet.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
news:41347038.8040909@.rwth-aachen.de...[vbcol=seagreen]
> Paul S Randal [MS] schrieb:
some[vbcol=seagreen]
the[vbcol=seagreen]
have a
> Paul,
> hope youre still watching, so you can see what our SQL Server did
> to me. I dont know, how many times I ran the procedures in
> succussion :-(
> Best Regards
> Thomas
>
> FYI:
> --CHECKDB without repair--
> Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
> Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
> Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
> seinem Verweis von Seite (1:10742), Slot 10 berein.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
> seinem Verweis von Seite (1:1317), Slot 3 berein.
> DBCC-Ergebnis fr 'XenoxDB'.
> DBCC-Ergebnis fr 'sysobjects'.
> Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
> DBCC-Ergebnis fr 'sysindexes'.
> Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
> CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
> sysindexes-Tabelle gefunden (Objekt-ID 2).
> DBCC-Ergebnis fr 'syscolumns'.
> Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
> DBCC-Ergebnis fr 'systypes'.
> Es sind 26 Zeilen in 1 Seiten fr das systypes-Objekt vorhanden.
> DBCC-Ergebnis fr 'syscomments'.
> Es sind 2828 Zeilen in 63 Seiten fr das syscomments-Objekt vorhanden.
> ----and so on ...
>
> --CHECKDB repair_allow_data_loss--
> Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
> Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
> Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
> seinem Verweis von Seite (1:10742), Slot 10 berein.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
> seinem Verweis von Seite (1:1317), Slot 3 berein.
> DBCC-Ergebnis fr 'XenoxDB'.
> DBCC-Ergebnis fr 'sysobjects'.
> Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
> DBCC-Ergebnis fr 'sysindexes'.
> Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
> diese Fehlerbehebung bergangen wurde.
> Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
> diese Fehlerbehebung bergangen wurde.
> Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
> diese Fehlerbehebung bergangen wurde.
> Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
> CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
> sysindexes-Tabelle gefunden (Objekt-ID 2).
> DBCC-Ergebnis fr 'syscolumns'.
> Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
> DBCC-Ergebnis fr 'systypes'.
> --Rest is similar to above output !
>