DBCC Page does not appear to functon?

I have a corrupt sql server 2000 database table, dbcc checktable yields:

Server: Msg 8929, Level 16, State 1, Line 1

Object ID 738101670: Errors found in text ID 1922475229184 owned by data record identified by RID = (1:5663655:28) PageId = 53918671.

Server: Msg 8965, Level 16, State 1, Line 1

Table error: Object ID 738101670. The text, ntext, or image node at page (1:5663737), slot 0, text ID 1922475229184 is referenced by page (1:4527446), slot 9, but was not seen in the scan.

When I try to use dbcc page( testdb, page#,1 ) where I plug in any of the reported page#'s above I always get the following:

"Server: Msg 8968, Level 16, State 1, Line 2
Table error: DBCC PAGE page (41728:1) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator."

The only page value which does not yield this error is page 1, which is the only pageno I found on any examples on the web. I guess this is because this is the only page it works on?

Can you post the exact command your using?

Also, can you tell me the result of 'SELECT DB_ID()'?

Thanks,|||Looks like you've got your parameters swapped. It should be dbcc page(testdb, 1, page#). The second parameter is the file number (which will always be 1 if your database has no secondary data files).
|||

You are correct! The format I originally found on the internet was either for a prior version or something else unexplained. I found the correct format in one of Ken Henderson's books.

Thanks!

DBCC Page does not appear to function?

I have a corrupt sql server 2000 database table, dbcc checktable yields:

Server: Msg 8929, Level 16, State 1, Line 1

Object ID 738101670: Errors found in text ID 1922475229184 owned by data record identified by RID = (1:5663655:28) PageId = 53918671.

Server: Msg 8965, Level 16, State 1, Line 1

Table error: Object ID 738101670. The text, ntext, or image node at page (1:5663737), slot 0, text ID 1922475229184 is referenced by page (1:4527446), slot 9, but was not seen in the scan.

When I try to use dbcc page( testdb, page#,1 ) where I plug in any of the reported page#'s above I always get the following:

"Server: Msg 8968, Level 16, State 1, Line 2
Table error: DBCC PAGE page (41728:1) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator."

The only page value which does not yield this error is page 1, which is the only pageno I found on any examples on the web. I guess this is because this is the only page it works on?

Can you post the exact command your using?

Also, can you tell me the result of 'SELECT DB_ID()'?

Thanks,|||Looks like you've got your parameters swapped. It should be dbcc page(testdb, 1, page#). The second parameter is the file number (which will always be 1 if your database has no secondary data files).
|||

You are correct! The format I originally found on the internet was either for a prior version or something else unexplained. I found the correct format in one of Ken Henderson's books.

Thanks!

sql

dbcc page

Hi,
How can i check witch object in my database is associated with this page
number?
PAG: 7:1:3794813
Outup of DBCC TRACEON (1204,3605,-1)
Im' having a lot of deadlock problems, any good links ?
Thanks in advance
Celso CorreiaThe output of DBCC PAGE includes a row with
Metadata: ObjectId = number
Use this number as an argument to the OBJECT_NAME function:
SELECT OBJECT_NAME(number)
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"CC" <CC@.discussions.microsoft.com> wrote in message
news:D338F279-6871-4657-BB76-FCAFEFF37DF4@.microsoft.com...
> Hi,
> How can i check witch object in my database is associated with this page
> number?
> PAG: 7:1:3794813
> Outup of DBCC TRACEON (1204,3605,-1)
> Im' having a lot of deadlock problems, any good links ?
> Thanks in advance
> Celso Correia

dbcc page

Hi,
How can i check witch object in my database is associated with this page
number?
PAG: 7:1:3794813
Outup of DBCC TRACEON (1204,3605,-1)
Im' having a lot of deadlock problems, any good links ?
Thanks in advance
Celso Correia
The output of DBCC PAGE includes a row with
Metadata: ObjectId = number
Use this number as an argument to the OBJECT_NAME function:
SELECT OBJECT_NAME(number)
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"CC" <CC@.discussions.microsoft.com> wrote in message
news:D338F279-6871-4657-BB76-FCAFEFF37DF4@.microsoft.com...
> Hi,
> How can i check witch object in my database is associated with this page
> number?
> PAG: 7:1:3794813
> Outup of DBCC TRACEON (1204,3605,-1)
> Im' having a lot of deadlock problems, any good links ?
> Thanks in advance
> Celso Correia

dbcc page

Hi,
How can i check witch object in my database is associated with this page
number?
PAG: 7:1:3794813
Outup of DBCC TRACEON (1204,3605,-1)
Im' having a lot of deadlock problems, any good links ?
Thanks in advance
Celso CorreiaThe output of DBCC PAGE includes a row with
Metadata: ObjectId = number
Use this number as an argument to the OBJECT_NAME function:
SELECT OBJECT_NAME(number)
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"CC" <CC@.discussions.microsoft.com> wrote in message
news:D338F279-6871-4657-BB76-FCAFEFF37DF4@.microsoft.com...
> Hi,
> How can i check witch object in my database is associated with this page
> number?
> PAG: 7:1:3794813
> Outup of DBCC TRACEON (1204,3605,-1)
> Im' having a lot of deadlock problems, any good links ?
> Thanks in advance
> Celso Correiasql

DBCC memusage

DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as our
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data is
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then you
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> > DBCC memusage reports the buffers used by a particalur object to be far
> > and
> > away more than any other object. I'm surpised as this table is a
> > historical
> > system message table for the app that resides on the DB. How does an
> > object
> > get buffers(I assume reading and writing to the object)? All the views in
> > the system do select * from tablename, so why this object and not another
> > transactional table? The ratio of buffers between the 1st and 2nd object
> > is
> > like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> > our
> > system is IO bound, disk channels are pegged at 100% and all of it is read
> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> > channels RAID5, quad procs with hyperthreading and performance is dismal.
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>> Paul's question would be my first guess as well. If that isn't it then
>> you
>> should run a trace to see how that table is being accessed and how often.
>> If it is being queried enough and it does a scan it can certainly lead to
>> this type behavior. When you say "4 disk channels RAID 5" do you mean
>> you
>> actually have 4 different RAID 5 arrays each on their own channel? Or a
>> 4
>> disk RAID 5?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>> > DBCC memusage reports the buffers used by a particalur object to be far
>> > and
>> > away more than any other object. I'm surpised as this table is a
>> > historical
>> > system message table for the app that resides on the DB. How does an
>> > object
>> > get buffers(I assume reading and writing to the object)? All the views
>> > in
>> > the system do select * from tablename, so why this object and not
>> > another
>> > transactional table? The ratio of buffers between the 1st and 2nd
>> > object
>> > is
>> > like 170,000 to 3000. I'm hoping to get clearance to purge this table
>> > as
>> > our
>> > system is IO bound, disk channels are pegged at 100% and all of it is
>> > read
>> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
>> > disk
>> > channels RAID5, quad procs with hyperthreading and performance is
>> > dismal.
>>|||I found that the table in question is accessed by every transaction that hits
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there an
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seeks
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
> > Negative on the pin table aspect although I've been comtemplating pinning
> > a
> > table myself. I got clearance to purge the table and after purging 60
> > days
> > worth of data, other objects are starting to show more than a few hundred
> > cache buffers. However, another object that holds useless historical data
> > is
> > showing as the top object in memusage. The first table is accessed for
> > evey
> > message the app server generates but the base view is a select *. Same
> > for
> > evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> > array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> > All data was on one logical drive pegged at 100% utilization and long disk
> > queues. After moving data around, the three channels are pegged or nearly
> > pegged all the time. Reads and readaheds are accounting for the usage.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Paul's question would be my first guess as well. If that isn't it then
> >> you
> >> should run a trace to see how that table is being accessed and how often.
> >> If it is being queried enough and it does a scan it can certainly lead to
> >> this type behavior. When you say "4 disk channels RAID 5" do you mean
> >> you
> >> actually have 4 different RAID 5 arrays each on their own channel? Or a
> >> 4
> >> disk RAID 5?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> >> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> >> > DBCC memusage reports the buffers used by a particalur object to be far
> >> > and
> >> > away more than any other object. I'm surpised as this table is a
> >> > historical
> >> > system message table for the app that resides on the DB. How does an
> >> > object
> >> > get buffers(I assume reading and writing to the object)? All the views
> >> > in
> >> > the system do select * from tablename, so why this object and not
> >> > another
> >> > transactional table? The ratio of buffers between the 1st and 2nd
> >> > object
> >> > is
> >> > like 170,000 to 3000. I'm hoping to get clearance to purge this table
> >> > as
> >> > our
> >> > system is IO bound, disk channels are pegged at 100% and all of it is
> >> > read
> >> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
> >> > disk
> >> > channels RAID5, quad procs with hyperthreading and performance is
> >> > dismal.
> >>
> >>
> >>
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>> If you have 3 separate arrays and all the channels are pegged you are
>> probably doing way too much access. You must be scanning most tables (or
>> at
>> least these big ones you are mentioning) a lot. By the way pinning the
>> table is usually not a good idea and will go away in 2005 anyway. Sounds
>> like you just need to optimize your code and or tables so you do more
>> seeks
>> than scans. You would be amazed that most systems hve just a few calls
>> or
>> sps that eat up most of the I/O. Once you tackle those others will pop
>> to
>> the top but you can do a lot of damage control by tuning the top x calls.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>> > Negative on the pin table aspect although I've been comtemplating
>> > pinning
>> > a
>> > table myself. I got clearance to purge the table and after purging 60
>> > days
>> > worth of data, other objects are starting to show more than a few
>> > hundred
>> > cache buffers. However, another object that holds useless historical
>> > data
>> > is
>> > showing as the top object in memusage. The first table is accessed for
>> > evey
>> > message the app server generates but the base view is a select *. Same
>> > for
>> > evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
>> > array.(Dell 6600 with a 22Os and two dual channel 2960 PERC
>> > controllers.)
>> > All data was on one logical drive pegged at 100% utilization and long
>> > disk
>> > queues. After moving data around, the three channels are pegged or
>> > nearly
>> > pegged all the time. Reads and readaheds are accounting for the usage.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Paul's question would be my first guess as well. If that isn't it
>> >> then
>> >> you
>> >> should run a trace to see how that table is being accessed and how
>> >> often.
>> >> If it is being queried enough and it does a scan it can certainly lead
>> >> to
>> >> this type behavior. When you say "4 disk channels RAID 5" do you mean
>> >> you
>> >> actually have 4 different RAID 5 arrays each on their own channel? Or
>> >> a
>> >> 4
>> >> disk RAID 5?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>> >> > DBCC memusage reports the buffers used by a particalur object to be
>> >> > far
>> >> > and
>> >> > away more than any other object. I'm surpised as this table is a
>> >> > historical
>> >> > system message table for the app that resides on the DB. How does
>> >> > an
>> >> > object
>> >> > get buffers(I assume reading and writing to the object)? All the
>> >> > views
>> >> > in
>> >> > the system do select * from tablename, so why this object and not
>> >> > another
>> >> > transactional table? The ratio of buffers between the 1st and 2nd
>> >> > object
>> >> > is
>> >> > like 170,000 to 3000. I'm hoping to get clearance to purge this
>> >> > table
>> >> > as
>> >> > our
>> >> > system is IO bound, disk channels are pegged at 100% and all of it
>> >> > is
>> >> > read
>> >> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
>> >> > disk
>> >> > channels RAID5, quad procs with hyperthreading and performance is
>> >> > dismal.
>> >>
>> >>
>> >>
>>

DBCC memusage

DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as ou
r
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data i
s
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:

> Paul's question would be my first guess as well. If that isn't it then yo
u
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>|||I found that the table in question is accessed by every transaction that hit
s
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there a
n
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:

> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or
at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seek
s
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>

DBCC memusage

DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as our
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.
Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.
|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.
|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data is
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:

> Paul's question would be my first guess as well. If that isn't it then you
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>
|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
|||I found that the table in question is accessed by every transaction that hits
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there an
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:

> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seeks
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>
|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:

dbcc error 2511

We had a dbcc error 2511 on a user table - non-clustered
index.
>>There are 0 rows in 1 pages for
object 'sched_order_item'.
Msg 2511, Level 16, State 1, Server DCECANP1, Procedure ,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Table
Corrupt: Object
ID 1421716613, Index ID 5. Keys out of order on page
(1:758631), slots
120 and 121.<<
dbcc checktable(table_name,repair_rebuild) in single user
mode was run, but the error is back the night after. By
the way, table doesn't have any data in it. if anybody
have any previous experiance with it and could share it
with me, I would appreciate it. Thanks very muchBOL shows it to be an index out of order problem. Strange
as you have no rows. I think I have come across this where
table changes are made and not reflected in the index
because there is no data.
I would try scripting out the table, drop it and then
recreate it.
Regards
John

DBCC DBREINDEX

DBCC CHECKDB;
GO
Result:
DBCC results for 'AgentsStateChanges'.
There are 2422697 rows in 46484 pages for object 'AgentsStateChanges'.
CHECKDB found 0 allocation errors and 92 consistency errors in table 'AgentsStateChanges' (object ID 2025058250).

Server: Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 53575229, index ID 1. The high key value on page (1:193625) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:269532).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. The previous link (1:269531) on page (1:269532) does not match the previous page (1:193625) that the parent (1:272396), slot 252 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. B-tree chain linkage mismatch. (1:193625)->next = (1:269532), but (1:269532)->Prev = (1:269531).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 14 and 15.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 154 and 155.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266869), slots 49 and 50.

DBCC DBREINDEX ('Vestel.dbo.AgentsStateChanges', PK_LLAgentFlowSummary, 0);
GO
Result:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'type 24, len 16'.
The statement has been terminated.

PLEASE HELP!;

GO

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

|||

Gary Wells wrote:

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

Hi Gary,

Yes, of course.

Thanks & Regards,

Melih

|||

I ran the query below to find the duplicate values and I got the error message.

select Msg_ID, count(Msg_ID)
from AgentsStateChanges
group by Msg_ID
having count(Msg_ID)>1

Server: Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.

|||
Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.
|||

Sankar Reddy wrote:

Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.

Yes, Sankar Reddy

We are using Sql2k. So, how can I solve this issue?

Thanks&Regards,

Melih

|||

OK. No dublicate values:
select Msg_ID, count(*)
from AgentsStateChanges
group by Msg_ID
having count(*)>1
result:
(0 row(s) affected)

But, I'm trying this and I'm finding 79 pieces dublicate values

select cast(Msg_ID as varchar(36)), count(*)
from AgentsStateChanges
group by cast(Msg_ID as varchar(36))
having count(*)>1

result:

(79 row(s) affected)

I'm trying some values from duplicate values and I get 0 row message interestingly

select * FROM AgentsStateChanges where Msg_ID=

'100CEB2E-6B69-0058-0001-000000000001'

result:
(0 row(s) affected)

I dont understood anything.

Melih

DBCC DBREINDEX

DBCC CHECKDB;
GO
Result:
DBCC results for 'AgentsStateChanges'.
There are 2422697 rows in 46484 pages for object 'AgentsStateChanges'.
CHECKDB found 0 allocation errors and 92 consistency errors in table 'AgentsStateChanges' (object ID 2025058250).

Server: Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 53575229, index ID 1. The high key value on page (1:193625) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:269532).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. The previous link (1:269531) on page (1:269532) does not match the previous page (1:193625) that the parent (1:272396), slot 252 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. B-tree chain linkage mismatch. (1:193625)->next = (1:269532), but (1:269532)->Prev = (1:269531).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 14 and 15.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 154 and 155.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266869), slots 49 and 50.

DBCC DBREINDEX ('Vestel.dbo.AgentsStateChanges', PK_LLAgentFlowSummary, 0);
GO
Result:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'type 24, len 16'.
The statement has been terminated.

PLEASE HELP!;

GO

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

|||

Gary Wells wrote:

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

Hi Gary,

Yes, of course.

Thanks & Regards,

Melih

|||

I ran the query below to find the duplicate values and I got the error message.

select Msg_ID, count(Msg_ID)
from AgentsStateChanges
group by Msg_ID
having count(Msg_ID)>1

Server: Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.

|||
Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.
|||

Sankar Reddy wrote:

Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.

Yes, Sankar Reddy

We are using Sql2k. So, how can I solve this issue?

Thanks&Regards,

Melih

|||

OK. No dublicate values:
select Msg_ID, count(*)
from AgentsStateChanges
group by Msg_ID
having count(*)>1
result:
(0 row(s) affected)

But, I'm trying this and I'm finding 79 pieces dublicate values

select cast(Msg_ID as varchar(36)), count(*)
from AgentsStateChanges
group by cast(Msg_ID as varchar(36))
having count(*)>1

result:

(79 row(s) affected)

I'm trying some values from duplicate values and I get 0 row message interestingly

select * FROM AgentsStateChanges where Msg_ID=

'100CEB2E-6B69-0058-0001-000000000001'

result:
(0 row(s) affected)

I dont understood anything.

Melih

DBCC CHECKTABLE

I am trying to attach a database and i get this msg:

Could not find row in sysindexes for database id 11, object id 1, index id 1.

Run DBCC CHECKTABLE on sysindexes

i dont know what dbcc checktable is or how to run.

pls help

This is fully documented in the Books Online.

You might even want to run a DBCC CHECKDB to make sure everything is alright.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||i ran DBCC CHECKDB on master and model and have no problems

I looked at bol for checktable and dont understand, i have not worked with SQL Srv or another db before. i dont know how to run on sysindexes as the msg states in the 1st post
how do i run it with REPAIR_ALLOW_DATA_LOSS , REPAIR_FAST or REPAIR_REBUILD
here is the whole msg:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BUSTED'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindexes for database ID 11, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'Asset5'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

BUTTONS:

OK

|||

i think you are trying to attach database from SQL Server 2005 to SQL Server 2000 which is not possible. Please check the version of source and target. If its from 2005 to 2000 then its not possible.

Madhu

|||

For the source database that you are trying to attach, run DBCC CHECKDB on that database. You need to check and see if the database you are trying to attach is corrupted. You would do that by executing the DBCC on the source database, not on the server you are trying to attach to.

Log onto the server where the source database is and change the database context to that database. Then execute

DBCC CHECKDB

You don't want to run with repair options to start with - you just want to do a checkdb first with no repair options and go from there. Using the repair allow data loss option isn't something you generally want to do anyway. There are other options before going down that road that should be explored. It all depends on what messages are returned when you do the DBCC CHECKDB

-Sue

DBCC CHECKDB Reporting Errors in - SQL Server 2005

HI,
I am getting this ttype of errors in SQL Server 2005, This table have xml
column.
Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
not referenced. [SQLSTATE 42000]
Please give some help on this , what will be the route cause of this problem?
how to resolve it.Hi
You may want to check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_0prl.asp
You may also want to check that this has not been re-introduced
http://support.microsoft.com/default.aspx?scid=kb;en-us;281287
Try dropping and re-creating the index. If the error persists then you
should log it with PSS.
John
"Srikanth" wrote:
> HI,
> I am getting this ttype of errors in SQL Server 2005, This table have xml
> column.
> Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
> ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
> LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
> not referenced. [SQLSTATE 42000]
> Please give some help on this , what will be the route cause of this problem?
> how to resolve it.
>
>

DBCC CHECKDB Reporting Errors in - SQL Server 2005

HI,
I am getting this ttype of errors in SQL Server 2005, This table have xml
column.
Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
not referenced. [SQLSTATE 42000]
Please give some help on this , what will be the route cause of this problem?
how to resolve it.
Hi
You may want to check out:
http://msdn.microsoft.com/library/de...err_2_0prl.asp
You may also want to check that this has not been re-introduced
http://support.microsoft.com/default...b;en-us;281287
Try dropping and re-creating the index. If the error persists then you
should log it with PSS.
John
"Srikanth" wrote:

> HI,
> I am getting this ttype of errors in SQL Server 2005, This table have xml
> column.
> Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
> ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
> LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
> not referenced. [SQLSTATE 42000]
> Please give some help on this , what will be the route cause of this problem?
> how to resolve it.
>
>

DBCC CHECKDB Reporting Errors in - SQL Server 2005

HI,
I am getting this ttype of errors in SQL Server 2005, This table have xml
column.
Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
not referenced. [SQLSTATE 42000]
Please give some help on this , what will be the route cause of this problem
?
how to resolve it.Hi
You may want to check out:
http://msdn.microsoft.com/library/d...serr_2_0prl.asp
You may also want to check that this has not been re-introduced
http://support.microsoft.com/defaul...kb;en-us;281287
Try dropping and re-creating the index. If the error persists then you
should log it with PSS.
John
"Srikanth" wrote:

> HI,
> I am getting this ttype of errors in SQL Server 2005, This table have xml
> column.
> Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, inde
x
> ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (typ
e
> LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512
is
> not referenced. [SQLSTATE 42000]
> Please give some help on this , what will be the route cause of this probl
em?
> how to resolve it.
>
>

DBCC checkdb error Msg 2574, Level 16, State 1, Line 1

Hi, All.
I am wondering if anyone has seen this error before:
dbcc checkdb ("EMIOPER")
Server: Msg 2574, Level 16, State 1, Line 1
Object ID 1112703362, index ID 1: Page (1:1688519) is empty. This is not
permitted at level 2 of the B-tree.
DBCC results for 'EMIOPER'.
select object_name(1112703362) returns spaces.
Thanks in advance,
Koni.Hi,
Looks like this error is come thing new:-
1. backup the database and try to drop and recreate all the indexes
2. After that try to execute the DBCC Checkdb again
If the problem still persits then try to restore from the latest good backup
or open a case with Microsoft PSS.
Thanks
Hari
SQL Server MVP
"Koni Kogan" <kkogan@.haiint.com> wrote in message
news:uvtELVfZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> Hi, All.
> I am wondering if anyone has seen this error before:
> dbcc checkdb ("EMIOPER")
> Server: Msg 2574, Level 16, State 1, Line 1
> Object ID 1112703362, index ID 1: Page (1:1688519) is empty. This is not
> permitted at level 2 of the B-tree.
> DBCC results for 'EMIOPER'.
> select object_name(1112703362) returns spaces.
> Thanks in advance,
> Koni.|||This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C56634.20C6A8F0
Content-Type: text/plain;
format=flowed;
charset="iso-8859-1";
reply-type=response
Content-Transfer-Encoding: 8bit
> select object_name(1112703362) returns spaces.
Are you in the right database when running above? If you spot the table, it sounds like dropping the
clustered index should cut it. But BOL has another oppinion (see BOL text below). Of course, you
want to know why this happened in the first place:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
And here's BOL recommendations for 2574:
Severity Level 10
Message Text
Object ID O_ID, index ID I_ID: Page P_ID is empty. This is not permitted at level LEVEL of the
B-tree.
Explanation
A B-tree page above the leaf level is empty (that is, it has no rows). This used to be possible for
leaf level pages, but has never been possible in tree levels.
Action
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
Performing a repair will rebuild the index. If running DBCC CHECKDB with one of the repair clauses
does not correct the problem, contact your primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Koni Kogan" <kkogan@.haiint.com> wrote in message news:uvtELVfZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> Hi, All.
> I am wondering if anyone has seen this error before:
> dbcc checkdb ("EMIOPER")
> Server: Msg 2574, Level 16, State 1, Line 1
> Object ID 1112703362, index ID 1: Page (1:1688519) is empty. This is not permitted at level 2 of
> the B-tree.
> DBCC results for 'EMIOPER'.
> select object_name(1112703362) returns spaces.
> Thanks in advance,
> Koni.
--=_NextPart_000_002E_01C56634.20C6A8F0
Content-Type: image/gif;
name="caution.gif"
Content-Transfer-Encoding: base64
Content-ID: <002a01c56623$5d3c5250$0200a8c0@.tibwork>
R0lGODlhDAALAPcAAAAAADMAAGYAAJkAAMwAAP8AAAAzADMzAGYzAJkzAMwzAP8zAABmADNmAGZm
AJlmAMxmAP9mAACZADOZAGaZAJmZAMyZAP+ZAADMADPMAGbMAJnMAMzMAP/MAAD/ADP/AGb/AJn/
AMz/AP//AAAAMzMAM2YAM5kAM8wAM/8AMwAzMzMzM2YzM5kzM8wzM/8zMwBmMzNmM2ZmM5lmM8xm
M/9mMwCZMzOZM2aZM5mZM8yZM/+ZMwDMMzPMM2bMM5nMM8zMM//MMwD/MzP/M2b/M5n/M8z/M///
MwAAZjMAZmYAZpkAZswAZv8AZgAzZjMzZmYzZpkzZswzZv8zZgBmZjNmZmZmZplmZsxmZv9mZgCZ
ZjOZZmaZZpmZZsyZZv+ZZgDMZjPMZmbMZpnMZszMZv/MZgD/ZjP/Zmb/Zpn/Zsz/Zv//ZgAAmTMA
mWYAmZkAmcwAmf8AmQAzmTMzmWYzmZkzmcwzmf8zmQBmmTNmmWZmmZlmmcxmmf9mmQCZmTOZmWaZ
mZmZmcyZmf+ZmQDMmTPMmWbMmZnMmczMmf/MmQD/mTP/mWb/mZn/mcz/mf//mQAAzDMAzGYAzJkA
zMwAzP8AzAAzzDMzzGYzzJkzzMwzzP8zzABmzDNmzGZmzJlmzMxmzP9mzACZzDOZzGaZzJmZzMyZ
zP+ZzADMzDPMzGbMzJnMzMzMzP/MzAD/zDP/zGb/zJn/zMz/zP//zAAA/zMA/2YA/5kA/8wA//8A
/wAz/zMz/2Yz/5kz/8wz//8z/wBm/zNm/2Zm/5lm/8xm//9m/wCZ/zOZ/2aZ/5mZ/8yZ//+Z/wDM
/zPM/2bM/5nM/8zM///M/wD//zP//2b//5n//8z//////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAKwALAAAAAAMAAsA
QAgiAFkJHEiwoMGDrAooRJhwIUKFBRhCZEhR4MSHDitijDgwIAA7
--=_NextPart_000_002E_01C56634.20C6A8F0--|||This is can be a hardware issue.
There are some explanations and workarounds
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_60l0.asp
Regards.
"Koni Kogan" wrote:
> Hi, All.
> I am wondering if anyone has seen this error before:
> dbcc checkdb ("EMIOPER")
> Server: Msg 2574, Level 16, State 1, Line 1
> Object ID 1112703362, index ID 1: Page (1:1688519) is empty. This is not
> permitted at level 2 of the B-tree.
> DBCC results for 'EMIOPER'.
> select object_name(1112703362) returns spaces.
> Thanks in advance,
> Koni.
>

DBCC CHECKDB

We have a number of databases where we are getting
consistency errors when we run DBCC CHECKDB. A sample
error is as follows:
Table error: Object ID 1961774046, index ID 0, page
(1:11920), row 1. Record check (Valid SqlVariant) failed.
Values are 4 and 0.
Server: Msg 2537, Level 16, State 1, Line 1
Table error: Object ID 1961774046, index ID 0, page
(1:11920), row 2. Record check (innerLength <= innerColInfo.CbMaxLen () && innerPCol != NULL) failed.
Values are 17 and 5.
.
CHECKDB found 0 allocation errors and 15011 consistency
errors in table 'SurveyDataElement' (object ID
1961774046).
We can't use any of the fix options as this results in
considerable data loss. Restoring backups is not an
option.
The problem itself also seems to be inconsistent. If we
detach the problem database and then attach it on another
server, running DBCC gives us error messages but for
different tables.
Any help would be seriously appreciated.
MartinFurther to previous post:
SQL Server 2000 SP3|||Martin,
If you can't restore from a backup then you don't have many options other
than to do some type of repair. This may indeed result in data loss but
that is why it is so important to do regular backups and DBCC's to catch
these types of issues before they get to this stage. I suggest you give MS
PSS a call and see if there is anything they can do as your options are now
limited.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"MartinMcD" <MMcDonnell@.dial.pipex.com> wrote in message
news:01b801c379d5$d64c5eb0$a001280a@.phx.gbl...
> We have a number of databases where we are getting
> consistency errors when we run DBCC CHECKDB. A sample
> error is as follows:
> Table error: Object ID 1961774046, index ID 0, page
> (1:11920), row 1. Record check (Valid SqlVariant) failed.
> Values are 4 and 0.
> Server: Msg 2537, Level 16, State 1, Line 1
> Table error: Object ID 1961774046, index ID 0, page
> (1:11920), row 2. Record check (innerLength <=> innerColInfo.CbMaxLen () && innerPCol != NULL) failed.
> Values are 17 and 5.
> .
> CHECKDB found 0 allocation errors and 15011 consistency
> errors in table 'SurveyDataElement' (object ID
> 1961774046).
> We can't use any of the fix options as this results in
> considerable data loss. Restoring backups is not an
> option.
> The problem itself also seems to be inconsistent. If we
> detach the problem database and then attach it on another
> server, running DBCC gives us error messages but for
> different tables.
> Any help would be seriously appreciated.
> Martin|||Andrew,
Thanks for the reply, we'll try PSS and we do have some backups - it
will just take days to sort through them.
Any idea what might cause there errors as one of the databases was
only built a week or so ago whilst others have been around for months?
I also don't understand why the same database gives different checkdb
results when attached to different servers. I am concerned that we
avoid these problems in future but as its happened on over 50% of our
databases I don't have a great deal of faith that it won't.
Martin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Martin,
>
>If you can't restore from a backup then you don't have many options other
>than to do some type of repair. This may indeed result in data loss but
>that is why it is so important to do regular backups and DBCC's to catch
>these types of issues before they get to this stage. I suggest you give MS
>PSS a call and see if there is anything they can do as your options are now
>limited.
>http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
>http://www.mssqlserver.com/faq/general-pss.asp MS PSS