I receive the following after running checktable:
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'RequestWords' (object ID 910678342).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (synergy.dbo.RequestWords ).
I have a backup and would like to know the best way to proceed to correct
the consistency errors...also where can I find the sp_repair_allow_data_loss?
Thanks> I have a backup and would like to know the best way to proceed to correct
> the consistency errors...also where can I find the
> sp_repair_allow_data_loss?
Restoring from your last known good backup is usually the best option rather
than allowing data loss. There may be other methods depending on the
specific types of corruption and affected object types.
REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC CHECKTABLE
commands. See the Books online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>I receive the following after running checktable:
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'RequestWords' (object ID 910678342).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> I have a backup and would like to know the best way to proceed to correct
> the consistency errors...also where can I find the
> sp_repair_allow_data_loss?
> Thanks|||Not sure when my last known good is and it could take quite awhile to try and
find it...is there a way to see what data would be lost first before running
REPAIR_ALLOW_DATA_LOSS ?
"Dan Guzman" wrote:
> > I have a backup and would like to know the best way to proceed to correct
> > the consistency errors...also where can I find the
> > sp_repair_allow_data_loss?
> Restoring from your last known good backup is usually the best option rather
> than allowing data loss. There may be other methods depending on the
> specific types of corruption and affected object types.
> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC CHECKTABLE
> commands. See the Books online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
> >I receive the following after running checktable:
> >
> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> > 'RequestWords' (object ID 910678342).
> > repair_allow_data_loss is the minimum repair level for the errors found by
> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >
> > I have a backup and would like to know the best way to proceed to correct
> > the consistency errors...also where can I find the
> > sp_repair_allow_data_loss?
> >
> > Thanks
>|||> Not sure when my last known good is and it could take quite awhile to try
> and
> find it...is there a way to see what data would be lost first before
> running
> REPAIR_ALLOW_DATA_LOSS ?
The DBCC CHECKTABLE output should specify the problem pages and error
details. You can use DBCC PAGE (google is your friend) to examine the page
contents and get an idea of data might be affected. I don't believe it's
possible to provide exact details of lost data beforehand; I would think
DBCC could recover the data without loss if that were possible.
You might try posting the DBCC error details in case we can provide an
alternate solution. You could also try running the DBCC CHECKTABLE WITH
REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
the problem and identify lost data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
> Not sure when my last known good is and it could take quite awhile to try
> and
> find it...is there a way to see what data would be lost first before
> running
> REPAIR_ALLOW_DATA_LOSS ?
> "Dan Guzman" wrote:
>> > I have a backup and would like to know the best way to proceed to
>> > correct
>> > the consistency errors...also where can I find the
>> > sp_repair_allow_data_loss?
>> Restoring from your last known good backup is usually the best option
>> rather
>> than allowing data loss. There may be other methods depending on the
>> specific types of corruption and affected object types.
>> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
>> CHECKTABLE
>> commands. See the Books online for details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>> >I receive the following after running checktable:
>> >
>> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
>> > 'RequestWords' (object ID 910678342).
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by
>> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >
>> > I have a backup and would like to know the best way to proceed to
>> > correct
>> > the consistency errors...also where can I find the
>> > sp_repair_allow_data_loss?
>> >
>> > Thanks|||Dan, I am going to paste the following detail, the table is a simple 2 column
that is used by a full text indexing job, it seems, the first field is just
text and the second is a guid...I went to record 128, but saw nothing
funny.....appreciate all your help
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 910678342, index ID 0: Page (1:369538) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 910678342, index ID 0, page (1:369538), row 125. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
DBCC results for 'RequestWords'.
There are 1313272 rows in 11072 pages for object 'RequestWords'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'RequestWords' (object ID 910678342).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (synergy.dbo.RequestWords ).
"Dan Guzman" wrote:
> > Not sure when my last known good is and it could take quite awhile to try
> > and
> > find it...is there a way to see what data would be lost first before
> > running
> > REPAIR_ALLOW_DATA_LOSS ?
> The DBCC CHECKTABLE output should specify the problem pages and error
> details. You can use DBCC PAGE (google is your friend) to examine the page
> contents and get an idea of data might be affected. I don't believe it's
> possible to provide exact details of lost data beforehand; I would think
> DBCC could recover the data without loss if that were possible.
> You might try posting the DBCC error details in case we can provide an
> alternate solution. You could also try running the DBCC CHECKTABLE WITH
> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
> the problem and identify lost data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
> > Not sure when my last known good is and it could take quite awhile to try
> > and
> > find it...is there a way to see what data would be lost first before
> > running
> > REPAIR_ALLOW_DATA_LOSS ?
> >
> > "Dan Guzman" wrote:
> >
> >> > I have a backup and would like to know the best way to proceed to
> >> > correct
> >> > the consistency errors...also where can I find the
> >> > sp_repair_allow_data_loss?
> >>
> >> Restoring from your last known good backup is usually the best option
> >> rather
> >> than allowing data loss. There may be other methods depending on the
> >> specific types of corruption and affected object types.
> >>
> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
> >> CHECKTABLE
> >> commands. See the Books online for details.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
> >> >I receive the following after running checktable:
> >> >
> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> >> > 'RequestWords' (object ID 910678342).
> >> > repair_allow_data_loss is the minimum repair level for the errors found
> >> > by
> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >> >
> >> > I have a backup and would like to know the best way to proceed to
> >> > correct
> >> > the consistency errors...also where can I find the
> >> > sp_repair_allow_data_loss?
> >> >
> >> > Thanks
> >>
>|||The DBCC error indicates bad column offsets, which will prevent the problem
row from being parsed. I think DBCC will need to delete that row to remove
the error. You can probably fix the error with a normal delete command if
you can identify the key value of the problem row from the raw DBCC PAGE
output.
If you haven't already done so, you might try DBCC PAGE print option 3
(http://support.microsoft.com/kb/83065) to print the individual column
values (example below). I don't know how this will behave with bad column
offsets but I'm curious to find out, if you don't mind giving that a try.
The DBCC PAGE dump is probably the only data you'll have to salvage the
deleted row, unless you contact Microsoft PSS.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:042EAEE7-D5C2-4847-9AF9-DFC92E3885FB@.microsoft.com...
> Dan, I am going to paste the following detail, the table is a simple 2
> column
> that is used by a full text indexing job, it seems, the first field is
> just
> text and the second is a guid...I went to record 128, but saw nothing
> funny.....appreciate all your help
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 910678342, index ID 0: Page (1:369538) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 910678342, index ID 0, page (1:369538), row 125.
> Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
> DBCC results for 'RequestWords'.
> There are 1313272 rows in 11072 pages for object 'RequestWords'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'RequestWords' (object ID 910678342).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>
>
> "Dan Guzman" wrote:
>> > Not sure when my last known good is and it could take quite awhile to
>> > try
>> > and
>> > find it...is there a way to see what data would be lost first before
>> > running
>> > REPAIR_ALLOW_DATA_LOSS ?
>> The DBCC CHECKTABLE output should specify the problem pages and error
>> details. You can use DBCC PAGE (google is your friend) to examine the
>> page
>> contents and get an idea of data might be affected. I don't believe it's
>> possible to provide exact details of lost data beforehand; I would think
>> DBCC could recover the data without loss if that were possible.
>> You might try posting the DBCC error details in case we can provide an
>> alternate solution. You could also try running the DBCC CHECKTABLE WITH
>> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
>> the problem and identify lost data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
>> > Not sure when my last known good is and it could take quite awhile to
>> > try
>> > and
>> > find it...is there a way to see what data would be lost first before
>> > running
>> > REPAIR_ALLOW_DATA_LOSS ?
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > I have a backup and would like to know the best way to proceed to
>> >> > correct
>> >> > the consistency errors...also where can I find the
>> >> > sp_repair_allow_data_loss?
>> >>
>> >> Restoring from your last known good backup is usually the best option
>> >> rather
>> >> than allowing data loss. There may be other methods depending on the
>> >> specific types of corruption and affected object types.
>> >>
>> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
>> >> CHECKTABLE
>> >> commands. See the Books online for details.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>> >> >I receive the following after running checktable:
>> >> >
>> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in
>> >> > table
>> >> > 'RequestWords' (object ID 910678342).
>> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> > found
>> >> > by
>> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >> >
>> >> > I have a backup and would like to know the best way to proceed to
>> >> > correct
>> >> > the consistency errors...also where can I find the
>> >> > sp_repair_allow_data_loss?
>> >> >
>> >> > Thanks
>> >>|||Dan, sorry took so long to get back...
I was able to locate the corrupt record in the referenced table...it was a
null id that had a guid assigned to it from another table...very weird...the
delete statement would not work on it....had to finally run
repair_allow_data_loss and then repair_fast. I lost 132 records but they
were all bogus references to the corruption...so it turned out all good.
Thanks for your assistance.
"Dan Guzman" wrote:
> The DBCC error indicates bad column offsets, which will prevent the problem
> row from being parsed. I think DBCC will need to delete that row to remove
> the error. You can probably fix the error with a normal delete command if
> you can identify the key value of the problem row from the raw DBCC PAGE
> output.
> If you haven't already done so, you might try DBCC PAGE print option 3
> (http://support.microsoft.com/kb/83065) to print the individual column
> values (example below). I don't know how this will behave with bad column
> offsets but I'm curious to find out, if you don't mind giving that a try.
> The DBCC PAGE dump is probably the only data you'll have to salvage the
> deleted row, unless you contact Microsoft PSS.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:042EAEE7-D5C2-4847-9AF9-DFC92E3885FB@.microsoft.com...
> > Dan, I am going to paste the following detail, the table is a simple 2
> > column
> > that is used by a full text indexing job, it seems, the first field is
> > just
> > text and the second is a guid...I went to record 128, but saw nothing
> > funny.....appreciate all your help
> >
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 910678342, index ID 0: Page (1:369538) could not be processed.
> > See
> > other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 1
> > Table error: Object ID 910678342, index ID 0, page (1:369538), row 125.
> > Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
> > DBCC results for 'RequestWords'.
> > There are 1313272 rows in 11072 pages for object 'RequestWords'.
> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> > 'RequestWords' (object ID 910678342).
> > repair_allow_data_loss is the minimum repair level for the errors found by
> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > Not sure when my last known good is and it could take quite awhile to
> >> > try
> >> > and
> >> > find it...is there a way to see what data would be lost first before
> >> > running
> >> > REPAIR_ALLOW_DATA_LOSS ?
> >>
> >> The DBCC CHECKTABLE output should specify the problem pages and error
> >> details. You can use DBCC PAGE (google is your friend) to examine the
> >> page
> >> contents and get an idea of data might be affected. I don't believe it's
> >> possible to provide exact details of lost data beforehand; I would think
> >> DBCC could recover the data without loss if that were possible.
> >>
> >> You might try posting the DBCC error details in case we can provide an
> >> alternate solution. You could also try running the DBCC CHECKTABLE WITH
> >> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
> >> the problem and identify lost data.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
> >> > Not sure when my last known good is and it could take quite awhile to
> >> > try
> >> > and
> >> > find it...is there a way to see what data would be lost first before
> >> > running
> >> > REPAIR_ALLOW_DATA_LOSS ?
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > I have a backup and would like to know the best way to proceed to
> >> >> > correct
> >> >> > the consistency errors...also where can I find the
> >> >> > sp_repair_allow_data_loss?
> >> >>
> >> >> Restoring from your last known good backup is usually the best option
> >> >> rather
> >> >> than allowing data loss. There may be other methods depending on the
> >> >> specific types of corruption and affected object types.
> >> >>
> >> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
> >> >> CHECKTABLE
> >> >> commands. See the Books online for details.
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
> >> >> >I receive the following after running checktable:
> >> >> >
> >> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in
> >> >> > table
> >> >> > 'RequestWords' (object ID 910678342).
> >> >> > repair_allow_data_loss is the minimum repair level for the errors
> >> >> > found
> >> >> > by
> >> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >> >> >
> >> >> > I have a backup and would like to know the best way to proceed to
> >> >> > correct
> >> >> > the consistency errors...also where can I find the
> >> >> > sp_repair_allow_data_loss?
> >> >> >
> >> >> > Thanks
> >> >>
> >>
>|||I'm glad you were able to get your database fixed.
--
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8525A1AF-4A88-47B3-AA8F-1144B9814DA8@.microsoft.com...
> Dan, sorry took so long to get back...
> I was able to locate the corrupt record in the referenced table...it was
> a
> null id that had a guid assigned to it from another table...very
> weird...the
> delete statement would not work on it....had to finally run
> repair_allow_data_loss and then repair_fast. I lost 132 records but they
> were all bogus references to the corruption...so it turned out all good.
> Thanks for your assistance.
> "Dan Guzman" wrote:
>> The DBCC error indicates bad column offsets, which will prevent the
>> problem
>> row from being parsed. I think DBCC will need to delete that row to
>> remove
>> the error. You can probably fix the error with a normal delete command
>> if
>> you can identify the key value of the problem row from the raw DBCC PAGE
>> output.
>> If you haven't already done so, you might try DBCC PAGE print option 3
>> (http://support.microsoft.com/kb/83065) to print the individual column
>> values (example below). I don't know how this will behave with bad
>> column
>> offsets but I'm curious to find out, if you don't mind giving that a try.
>> The DBCC PAGE dump is probably the only data you'll have to salvage the
>> deleted row, unless you contact Microsoft PSS.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:042EAEE7-D5C2-4847-9AF9-DFC92E3885FB@.microsoft.com...
>> > Dan, I am going to paste the following detail, the table is a simple 2
>> > column
>> > that is used by a full text indexing job, it seems, the first field is
>> > just
>> > text and the second is a guid...I went to record 128, but saw nothing
>> > funny.....appreciate all your help
>> >
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 910678342, index ID 0: Page (1:369538) could not be
>> > processed.
>> > See
>> > other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 1
>> > Table error: Object ID 910678342, index ID 0, page (1:369538), row 125.
>> > Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
>> > DBCC results for 'RequestWords'.
>> > There are 1313272 rows in 11072 pages for object 'RequestWords'.
>> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
>> > 'RequestWords' (object ID 910678342).
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by
>> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >
>> >
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Not sure when my last known good is and it could take quite awhile
>> >> > to
>> >> > try
>> >> > and
>> >> > find it...is there a way to see what data would be lost first before
>> >> > running
>> >> > REPAIR_ALLOW_DATA_LOSS ?
>> >>
>> >> The DBCC CHECKTABLE output should specify the problem pages and error
>> >> details. You can use DBCC PAGE (google is your friend) to examine the
>> >> page
>> >> contents and get an idea of data might be affected. I don't believe
>> >> it's
>> >> possible to provide exact details of lost data beforehand; I would
>> >> think
>> >> DBCC could recover the data without loss if that were possible.
>> >>
>> >> You might try posting the DBCC error details in case we can provide an
>> >> alternate solution. You could also try running the DBCC CHECKTABLE
>> >> WITH
>> >> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to
>> >> correct
>> >> the problem and identify lost data.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
>> >> > Not sure when my last known good is and it could take quite awhile
>> >> > to
>> >> > try
>> >> > and
>> >> > find it...is there a way to see what data would be lost first before
>> >> > running
>> >> > REPAIR_ALLOW_DATA_LOSS ?
>> >> >
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> > I have a backup and would like to know the best way to proceed to
>> >> >> > correct
>> >> >> > the consistency errors...also where can I find the
>> >> >> > sp_repair_allow_data_loss?
>> >> >>
>> >> >> Restoring from your last known good backup is usually the best
>> >> >> option
>> >> >> rather
>> >> >> than allowing data loss. There may be other methods depending on
>> >> >> the
>> >> >> specific types of corruption and affected object types.
>> >> >>
>> >> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
>> >> >> CHECKTABLE
>> >> >> commands. See the Books online for details.
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>> >> >> >I receive the following after running checktable:
>> >> >> >
>> >> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in
>> >> >> > table
>> >> >> > 'RequestWords' (object ID 910678342).
>> >> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> >> > found
>> >> >> > by
>> >> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >> >> >
>> >> >> > I have a backup and would like to know the best way to proceed to
>> >> >> > correct
>> >> >> > the consistency errors...also where can I find the
>> >> >> > sp_repair_allow_data_loss?
>> >> >> >
>> >> >> > Thanks
>> >> >>
>> >>
Showing posts with label errors. Show all posts
Showing posts with label errors. Show all posts
Thursday, March 8, 2012
Tuesday, February 14, 2012
Checking all views for errors after upgrade
I just upgraded from SQL 7.0 to 2000. I know I have an error with the
CAST and CONVERT functions I used in some views. Anyway, is there a
sp I can run that will run all my views and give me back a list of
those which have an error instead of going through each view one by
one?
CAST and CONVERT functions I used in some views. Anyway, is there a
sp I can run that will run all my views and give me back a list of
those which have an error instead of going through each view one by
one?
Thanks.
Sherry[posted and mailed, please reply in news]
sherkozmo (skosmicki@.sfmc-gi.org) writes:
> I just upgraded from SQL 7.0 to 2000. I know I have an error with the
> CAST and CONVERT functions I used in some views. Anyway, is there a
> sp I can run that will run all my views and give me back a list of
> those which have an error instead of going through each view one by
> one?
SELECT 'SELECT * FROM ' + name + char(13) + char(10) + 'go'
FROM sysobjects
WHERE xtype = 'V'
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY name
Run from Query Analyzer with results set to text. Cut and paste result
into query window.
I added the go between each SELECT, as it is likely that SQL Server will
abort the batch in case of an error and not run remaining views.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"sherkozmo" <skosmicki@.sfmc-gi.org> wrote in message
news:3ff08a65.0401060912.728b917@.posting.google.co m...
> I just upgraded from SQL 7.0 to 2000. I know I have an error with the
> CAST and CONVERT functions I used in some views. Anyway, is there a
> sp I can run that will run all my views and give me back a list of
> those which have an error instead of going through each view one by
> one?
You can bundle all your views into one script file by EM scripting.
Whether this is an advantage in examining them all at once?
This can be tested by creating a test database etc.
Pete Brown
Falls Creek
Oz
Checking a database for physical damage
Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?
If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>
|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegro ups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?
If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>
|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegro ups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Checking a database for physical damage
Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> > Hi,
> >
> > Today I had one disk of my server array crashed, it is on raid 5 with
> > hot spare disks.
> > SQL Server reported some errors on event logger but apparently the hot
> > spare disks were used correctly since it is working right now.
> >
> > So far is fine, but just in case I ran this command:
> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> >
> > And it showed no errors.
> > Are there any other commands that I can run to check for integrity in a
> > database in a case like this?
> >|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>> If the CHECKDB was clean, you are in good shape. You might also include
>> DBCC CHECKCATALOG as part of your scheduled integrity checks but it
>> unlikely
>> that a hardware error will introduce catalog problems.
>> You mention SQL Server errors but SQL Server should be oblivious to the
>> problem, assuming the controller gracefully handles such errors. If the
>> errors are due to the I/O subsystem become sluggish during the rebuild,
>> check to see if your array controller allows you to configure the rebuild
>> priority.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <craigkenisston@.hotmail.com> wrote in message
>> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Today I had one disk of my server array crashed, it is on raid 5 with
>> > hot spare disks.
>> > SQL Server reported some errors on event logger but apparently the hot
>> > spare disks were used correctly since it is working right now.
>> >
>> > So far is fine, but just in case I ran this command:
>> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
>> >
>> > And it showed no errors.
>> > Are there any other commands that I can run to check for integrity in a
>> > database in a case like this?
>> >
>
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> > Hi,
> >
> > Today I had one disk of my server array crashed, it is on raid 5 with
> > hot spare disks.
> > SQL Server reported some errors on event logger but apparently the hot
> > spare disks were used correctly since it is working right now.
> >
> > So far is fine, but just in case I ran this command:
> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> >
> > And it showed no errors.
> > Are there any other commands that I can run to check for integrity in a
> > database in a case like this?
> >|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>> If the CHECKDB was clean, you are in good shape. You might also include
>> DBCC CHECKCATALOG as part of your scheduled integrity checks but it
>> unlikely
>> that a hardware error will introduce catalog problems.
>> You mention SQL Server errors but SQL Server should be oblivious to the
>> problem, assuming the controller gracefully handles such errors. If the
>> errors are due to the I/O subsystem become sluggish during the rebuild,
>> check to see if your array controller allows you to configure the rebuild
>> priority.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <craigkenisston@.hotmail.com> wrote in message
>> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Today I had one disk of my server array crashed, it is on raid 5 with
>> > hot spare disks.
>> > SQL Server reported some errors on event logger but apparently the hot
>> > spare disks were used correctly since it is working right now.
>> >
>> > So far is fine, but just in case I ran this command:
>> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
>> >
>> > And it showed no errors.
>> > Are there any other commands that I can run to check for integrity in a
>> > database in a case like this?
>> >
>
Checking a database for physical damage
Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlike
ly
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlike
ly
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
CHECKDB on master ?
Can I get data loss from running CHECKDB on master (or user databases)
if the consistency errors are in indexes?
Here are the results of CHECKDB on master:
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 12, index ID 0: Page (1:364) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 2
Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1272 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4884 rows in 85 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2024 rows in 952 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 788 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5282 rows in 28 pages for object 'sysdepends'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sysdepends' (object ID 12).
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 309 rows in 13 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 465 rows in 14 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3831 rows in 165 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 7 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 618 rows in 123 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 2 consistency errors in database
'master'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
for more details.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over :(
Paul S Randal [MS] wrote:
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> for more details.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> > Can I get data loss from running CHECKDB on master (or user databases)
> > if the consistency errors are in indexes?
> >
> >
> > Here are the results of CHECKDB on master:
> >
> > Server: Msg 8928, Level 16, State 1, Line 2
> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> > other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 2
> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> > DBCC results for 'master'.
> > DBCC results for 'sysobjects'.
> > There are 1272 rows in 23 pages for object 'sysobjects'.
> > DBCC results for 'sysindexes'.
> > There are 104 rows in 4 pages for object 'sysindexes'.
> > DBCC results for 'syscolumns'.
> > There are 4884 rows in 85 pages for object 'syscolumns'.
> > DBCC results for 'systypes'.
> > There are 26 rows in 1 pages for object 'systypes'.
> > DBCC results for 'syscomments'.
> > There are 2024 rows in 952 pages for object 'syscomments'.
> > DBCC results for 'sysfiles1'.
> > There are 2 rows in 1 pages for object 'sysfiles1'.
> > DBCC results for 'syspermissions'.
> > There are 788 rows in 4 pages for object 'syspermissions'.
> > DBCC results for 'sysusers'.
> > There are 17 rows in 1 pages for object 'sysusers'.
> > DBCC results for 'sysproperties'.
> > There are 0 rows in 0 pages for object 'sysproperties'.
> > DBCC results for 'sysdepends'.
> > There are 5282 rows in 28 pages for object 'sysdepends'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> > 'sysdepends' (object ID 12).
> > DBCC results for 'sysreferences'.
> > There are 0 rows in 0 pages for object 'sysreferences'.
> > DBCC results for 'sysfulltextcatalogs'.
> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> > DBCC results for 'sysfulltextnotify'.
> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> > DBCC results for 'sysdatabases'.
> > There are 309 rows in 13 pages for object 'sysdatabases'.
> > DBCC results for 'sysxlogins'.
> > There are 465 rows in 14 pages for object 'sysxlogins'.
> > DBCC results for 'sysdevices'.
> > There are 8 rows in 1 pages for object 'sysdevices'.
> > DBCC results for 'sysmessages'.
> > There are 3831 rows in 165 pages for object 'sysmessages'.
> > DBCC results for 'sysconfigures'.
> > There are 38 rows in 1 pages for object 'sysconfigures'.
> > DBCC results for 'sysservers'.
> > There are 7 rows in 1 pages for object 'sysservers'.
> > DBCC results for 'syslanguages'.
> > There are 33 rows in 3 pages for object 'syslanguages'.
> > DBCC results for 'syscharsets'.
> > There are 114 rows in 33 pages for object 'syscharsets'.
> > DBCC results for 'sysaltfiles'.
> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> > DBCC results for 'sysfilegroups'.
> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> > DBCC results for 'spt_monitor'.
> > There are 1 rows in 1 pages for object 'spt_monitor'.
> > DBCC results for 'spt_values'.
> > There are 730 rows in 7 pages for object 'spt_values'.
> > DBCC results for 'spt_fallback_db'.
> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> > DBCC results for 'spt_fallback_dev'.
> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> > DBCC results for 'spt_fallback_usg'.
> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> > DBCC results for 'spt_provider_types'.
> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> > DBCC results for 'spt_datatype_info_ext'.
> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> > DBCC results for 'MSreplication_options'.
> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> > DBCC results for 'spt_datatype_info'.
> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> > DBCC results for 'spt_server_info'.
> > There are 29 rows in 1 pages for object 'spt_server_info'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in database
> > 'master'.
> > repair_allow_data_loss is the minimum repair level for the errors found
> > by DBCC CHECKDB (master ).
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >|||Before you undertake the effort of restoring your currently damaged master db
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over :(
>
> Paul S Randal [MS] wrote:
>> If the corruption is limited to non-clustered indexes (with index ID >
>> 1),
>> then you won't lose data. In your case, the corrupt page is in the
>> sysdepends heap so you will lose data from that table. This system table
>> stores info about the dependencies between views, triggers, sprocs and
>> the
>> objects they reference. I'm not sure how the system will behave if you
>> run
>> repair - which will delete the page (because the internal pointer in the
>> record that points to the variable-length column offset table is pointing
>> off the end of the record - and so the page could be grossly corrupt).
>> A far better course of action is to run restore in this case. Do you have
>> a
>> valid backup?
>> See my blog post at
>> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
>> for more details.
>> Thanks
>> --
>> Paul Randal
>> Lead Program Manager, Microsoft SQL Server Storage Engine
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave" <daveg.01@.gmail.com> wrote in message
>> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
>> > Can I get data loss from running CHECKDB on master (or user databases)
>> > if the consistency errors are in indexes?
>> >
>> >
>> > Here are the results of CHECKDB on master:
>> >
>> > Server: Msg 8928, Level 16, State 1, Line 2
>> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
>> > other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 2
>> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
>> > DBCC results for 'master'.
>> > DBCC results for 'sysobjects'.
>> > There are 1272 rows in 23 pages for object 'sysobjects'.
>> > DBCC results for 'sysindexes'.
>> > There are 104 rows in 4 pages for object 'sysindexes'.
>> > DBCC results for 'syscolumns'.
>> > There are 4884 rows in 85 pages for object 'syscolumns'.
>> > DBCC results for 'systypes'.
>> > There are 26 rows in 1 pages for object 'systypes'.
>> > DBCC results for 'syscomments'.
>> > There are 2024 rows in 952 pages for object 'syscomments'.
>> > DBCC results for 'sysfiles1'.
>> > There are 2 rows in 1 pages for object 'sysfiles1'.
>> > DBCC results for 'syspermissions'.
>> > There are 788 rows in 4 pages for object 'syspermissions'.
>> > DBCC results for 'sysusers'.
>> > There are 17 rows in 1 pages for object 'sysusers'.
>> > DBCC results for 'sysproperties'.
>> > There are 0 rows in 0 pages for object 'sysproperties'.
>> > DBCC results for 'sysdepends'.
>> > There are 5282 rows in 28 pages for object 'sysdepends'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'sysdepends' (object ID 12).
>> > DBCC results for 'sysreferences'.
>> > There are 0 rows in 0 pages for object 'sysreferences'.
>> > DBCC results for 'sysfulltextcatalogs'.
>> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>> > DBCC results for 'sysfulltextnotify'.
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>> > DBCC results for 'sysdatabases'.
>> > There are 309 rows in 13 pages for object 'sysdatabases'.
>> > DBCC results for 'sysxlogins'.
>> > There are 465 rows in 14 pages for object 'sysxlogins'.
>> > DBCC results for 'sysdevices'.
>> > There are 8 rows in 1 pages for object 'sysdevices'.
>> > DBCC results for 'sysmessages'.
>> > There are 3831 rows in 165 pages for object 'sysmessages'.
>> > DBCC results for 'sysconfigures'.
>> > There are 38 rows in 1 pages for object 'sysconfigures'.
>> > DBCC results for 'sysservers'.
>> > There are 7 rows in 1 pages for object 'sysservers'.
>> > DBCC results for 'syslanguages'.
>> > There are 33 rows in 3 pages for object 'syslanguages'.
>> > DBCC results for 'syscharsets'.
>> > There are 114 rows in 33 pages for object 'syscharsets'.
>> > DBCC results for 'sysaltfiles'.
>> > There are 618 rows in 123 pages for object 'sysaltfiles'.
>> > DBCC results for 'sysfilegroups'.
>> > There are 1 rows in 1 pages for object 'sysfilegroups'.
>> > DBCC results for 'spt_monitor'.
>> > There are 1 rows in 1 pages for object 'spt_monitor'.
>> > DBCC results for 'spt_values'.
>> > There are 730 rows in 7 pages for object 'spt_values'.
>> > DBCC results for 'spt_fallback_db'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
>> > DBCC results for 'spt_fallback_dev'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>> > DBCC results for 'spt_fallback_usg'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>> > DBCC results for 'spt_provider_types'.
>> > There are 25 rows in 1 pages for object 'spt_provider_types'.
>> > DBCC results for 'spt_datatype_info_ext'.
>> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>> > DBCC results for 'MSreplication_options'.
>> > There are 2 rows in 1 pages for object 'MSreplication_options'.
>> > DBCC results for 'spt_datatype_info'.
>> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
>> > DBCC results for 'spt_server_info'.
>> > There are 29 rows in 1 pages for object 'spt_server_info'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in database
>> > 'master'.
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by DBCC CHECKDB (master ).
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>|||Paul
Yes I can select * from sysdepends
Are you saying that I can repair the master database or I cannot?
It looks like our backups are corrupt too. I am not sure when the
original errors were introduced. It looks like I might have to use a
procedure similar to what Sassan described above.
Paul S Randal [MS] wrote:
> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
> documented every error message that CHECKDB can return (SQL Server 2005 ones
> are done but haven't made it out to the wild yet)
> Can you select * from sysdepends in master? You may be lucky and there's
> nothing there, in which case (once you've made sure you know exactly why the
> problem occured and have taken steps to make sure it doesn't happen again),
> you should be able to get away with running repair rather than going back to
> your old backups. And, of course, get a much better backup strategy.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> > Thanks Paul!!!!!
> >
> > I am trying to understand how to interpret the results of CHECKDB. I
> > can't seem to find enough detail to understand what is going on in BOL.
> >
> > I think we are going to open a ticket with Microsoft support to assist
> > us.
> >
> > Our latest backup overlaps the disk failure. Based off time, it looks
> > like the backup was 80-90% complete before the disk went bad.
> >
> > The next backup we have is over a week old. It looks like our backup
> > to tape got bumped by other jobs which ran over :(
> >
> >
> > Paul S Randal [MS] wrote:
> >> If the corruption is limited to non-clustered indexes (with index ID >
> >> 1),
> >> then you won't lose data. In your case, the corrupt page is in the
> >> sysdepends heap so you will lose data from that table. This system table
> >> stores info about the dependencies between views, triggers, sprocs and
> >> the
> >> objects they reference. I'm not sure how the system will behave if you
> >> run
> >> repair - which will delete the page (because the internal pointer in the
> >> record that points to the variable-length column offset table is pointing
> >> off the end of the record - and so the page could be grossly corrupt).
> >>
> >> A far better course of action is to run restore in this case. Do you have
> >> a
> >> valid backup?
> >>
> >> See my blog post at
> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> >> for more details.
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> >> > Can I get data loss from running CHECKDB on master (or user databases)
> >> > if the consistency errors are in indexes?
> >> >
> >> >
> >> > Here are the results of CHECKDB on master:
> >> >
> >> > Server: Msg 8928, Level 16, State 1, Line 2
> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> >> > other errors for details.
> >> > Server: Msg 8944, Level 16, State 1, Line 2
> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> >> > DBCC results for 'master'.
> >> > DBCC results for 'sysobjects'.
> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
> >> > DBCC results for 'sysindexes'.
> >> > There are 104 rows in 4 pages for object 'sysindexes'.
> >> > DBCC results for 'syscolumns'.
> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
> >> > DBCC results for 'systypes'.
> >> > There are 26 rows in 1 pages for object 'systypes'.
> >> > DBCC results for 'syscomments'.
> >> > There are 2024 rows in 952 pages for object 'syscomments'.
> >> > DBCC results for 'sysfiles1'.
> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
> >> > DBCC results for 'syspermissions'.
> >> > There are 788 rows in 4 pages for object 'syspermissions'.
> >> > DBCC results for 'sysusers'.
> >> > There are 17 rows in 1 pages for object 'sysusers'.
> >> > DBCC results for 'sysproperties'.
> >> > There are 0 rows in 0 pages for object 'sysproperties'.
> >> > DBCC results for 'sysdepends'.
> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> >> > 'sysdepends' (object ID 12).
> >> > DBCC results for 'sysreferences'.
> >> > There are 0 rows in 0 pages for object 'sysreferences'.
> >> > DBCC results for 'sysfulltextcatalogs'.
> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> >> > DBCC results for 'sysfulltextnotify'.
> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> >> > DBCC results for 'sysdatabases'.
> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
> >> > DBCC results for 'sysxlogins'.
> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
> >> > DBCC results for 'sysdevices'.
> >> > There are 8 rows in 1 pages for object 'sysdevices'.
> >> > DBCC results for 'sysmessages'.
> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
> >> > DBCC results for 'sysconfigures'.
> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
> >> > DBCC results for 'sysservers'.
> >> > There are 7 rows in 1 pages for object 'sysservers'.
> >> > DBCC results for 'syslanguages'.
> >> > There are 33 rows in 3 pages for object 'syslanguages'.
> >> > DBCC results for 'syscharsets'.
> >> > There are 114 rows in 33 pages for object 'syscharsets'.
> >> > DBCC results for 'sysaltfiles'.
> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> >> > DBCC results for 'sysfilegroups'.
> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> >> > DBCC results for 'spt_monitor'.
> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
> >> > DBCC results for 'spt_values'.
> >> > There are 730 rows in 7 pages for object 'spt_values'.
> >> > DBCC results for 'spt_fallback_db'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> >> > DBCC results for 'spt_fallback_dev'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> >> > DBCC results for 'spt_fallback_usg'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> >> > DBCC results for 'spt_provider_types'.
> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> >> > DBCC results for 'spt_datatype_info_ext'.
> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> >> > DBCC results for 'MSreplication_options'.
> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> >> > DBCC results for 'spt_datatype_info'.
> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> >> > DBCC results for 'spt_server_info'.
> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
> >> > CHECKDB found 0 allocation errors and 2 consistency errors in database
> >> > 'master'.
> >> > repair_allow_data_loss is the minimum repair level for the errors found
> >> > by DBCC CHECKDB (master ).
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >|||I suggest you take a backup of master and try running repair, and also the
other stuff I suggest below.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150905664.179677.169920@.c74g2000cwc.googlegroups.com...
> Paul
> Yes I can select * from sysdepends
> Are you saying that I can repair the master database or I cannot?
>
> It looks like our backups are corrupt too. I am not sure when the
> original errors were introduced. It looks like I might have to use a
> procedure similar to what Sassan described above.
>
>
>
> Paul S Randal [MS] wrote:
>> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes.
>> I
>> documented every error message that CHECKDB can return (SQL Server 2005
>> ones
>> are done but haven't made it out to the wild yet)
>> Can you select * from sysdepends in master? You may be lucky and there's
>> nothing there, in which case (once you've made sure you know exactly why
>> the
>> problem occured and have taken steps to make sure it doesn't happen
>> again),
>> you should be able to get away with running repair rather than going back
>> to
>> your old backups. And, of course, get a much better backup strategy.
>> Thanks
>> --
>> Paul Randal
>> Lead Program Manager, Microsoft SQL Server Storage Engine
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave" <daveg.01@.gmail.com> wrote in message
>> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
>> > Thanks Paul!!!!!
>> >
>> > I am trying to understand how to interpret the results of CHECKDB. I
>> > can't seem to find enough detail to understand what is going on in BOL.
>> >
>> > I think we are going to open a ticket with Microsoft support to assist
>> > us.
>> >
>> > Our latest backup overlaps the disk failure. Based off time, it looks
>> > like the backup was 80-90% complete before the disk went bad.
>> >
>> > The next backup we have is over a week old. It looks like our backup
>> > to tape got bumped by other jobs which ran over :(
>> >
>> >
>> > Paul S Randal [MS] wrote:
>> >> If the corruption is limited to non-clustered indexes (with index ID >
>> >> 1),
>> >> then you won't lose data. In your case, the corrupt page is in the
>> >> sysdepends heap so you will lose data from that table. This system
>> >> table
>> >> stores info about the dependencies between views, triggers, sprocs and
>> >> the
>> >> objects they reference. I'm not sure how the system will behave if you
>> >> run
>> >> repair - which will delete the page (because the internal pointer in
>> >> the
>> >> record that points to the variable-length column offset table is
>> >> pointing
>> >> off the end of the record - and so the page could be grossly corrupt).
>> >>
>> >> A far better course of action is to run restore in this case. Do you
>> >> have
>> >> a
>> >> valid backup?
>> >>
>> >> See my blog post at
>> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
>> >> for more details.
>> >>
>> >> Thanks
>> >>
>> >> --
>> >> Paul Randal
>> >> Lead Program Manager, Microsoft SQL Server Storage Engine
>> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >> "Dave" <daveg.01@.gmail.com> wrote in message
>> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
>> >> > Can I get data loss from running CHECKDB on master (or user
>> >> > databases)
>> >> > if the consistency errors are in indexes?
>> >> >
>> >> >
>> >> > Here are the results of CHECKDB on master:
>> >> >
>> >> > Server: Msg 8928, Level 16, State 1, Line 2
>> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
>> >> > other errors for details.
>> >> > Server: Msg 8944, Level 16, State 1, Line 2
>> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
>> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
>> >> > DBCC results for 'master'.
>> >> > DBCC results for 'sysobjects'.
>> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
>> >> > DBCC results for 'sysindexes'.
>> >> > There are 104 rows in 4 pages for object 'sysindexes'.
>> >> > DBCC results for 'syscolumns'.
>> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
>> >> > DBCC results for 'systypes'.
>> >> > There are 26 rows in 1 pages for object 'systypes'.
>> >> > DBCC results for 'syscomments'.
>> >> > There are 2024 rows in 952 pages for object 'syscomments'.
>> >> > DBCC results for 'sysfiles1'.
>> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
>> >> > DBCC results for 'syspermissions'.
>> >> > There are 788 rows in 4 pages for object 'syspermissions'.
>> >> > DBCC results for 'sysusers'.
>> >> > There are 17 rows in 1 pages for object 'sysusers'.
>> >> > DBCC results for 'sysproperties'.
>> >> > There are 0 rows in 0 pages for object 'sysproperties'.
>> >> > DBCC results for 'sysdepends'.
>> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
>> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> >> > 'sysdepends' (object ID 12).
>> >> > DBCC results for 'sysreferences'.
>> >> > There are 0 rows in 0 pages for object 'sysreferences'.
>> >> > DBCC results for 'sysfulltextcatalogs'.
>> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>> >> > DBCC results for 'sysfulltextnotify'.
>> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>> >> > DBCC results for 'sysdatabases'.
>> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
>> >> > DBCC results for 'sysxlogins'.
>> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
>> >> > DBCC results for 'sysdevices'.
>> >> > There are 8 rows in 1 pages for object 'sysdevices'.
>> >> > DBCC results for 'sysmessages'.
>> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
>> >> > DBCC results for 'sysconfigures'.
>> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
>> >> > DBCC results for 'sysservers'.
>> >> > There are 7 rows in 1 pages for object 'sysservers'.
>> >> > DBCC results for 'syslanguages'.
>> >> > There are 33 rows in 3 pages for object 'syslanguages'.
>> >> > DBCC results for 'syscharsets'.
>> >> > There are 114 rows in 33 pages for object 'syscharsets'.
>> >> > DBCC results for 'sysaltfiles'.
>> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
>> >> > DBCC results for 'sysfilegroups'.
>> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
>> >> > DBCC results for 'spt_monitor'.
>> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
>> >> > DBCC results for 'spt_values'.
>> >> > There are 730 rows in 7 pages for object 'spt_values'.
>> >> > DBCC results for 'spt_fallback_db'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
>> >> > DBCC results for 'spt_fallback_dev'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>> >> > DBCC results for 'spt_fallback_usg'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>> >> > DBCC results for 'spt_provider_types'.
>> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
>> >> > DBCC results for 'spt_datatype_info_ext'.
>> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>> >> > DBCC results for 'MSreplication_options'.
>> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
>> >> > DBCC results for 'spt_datatype_info'.
>> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
>> >> > DBCC results for 'spt_server_info'.
>> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
>> >> > CHECKDB found 0 allocation errors and 2 consistency errors in
>> >> > database
>> >> > 'master'.
>> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> > found
>> >> > by DBCC CHECKDB (master ).
>> >> > DBCC execution completed. If DBCC printed error messages, contact
>> >> > your
>> >> > system administrator.
>> >> >
>> >
>|||PSS asked me to rebuild it. We have our application back up and
running now.
Thanks for all the help guys! I have a much better understanding of
database corruption, backup, and restore now.
I will make sure we get on a sensible backup program.
Paul S Randal [MS] wrote:
> I suggest you take a backup of master and try running repair, and also the
> other stuff I suggest below.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150905664.179677.169920@.c74g2000cwc.googlegroups.com...
> > Paul
> >
> > Yes I can select * from sysdepends
> >
> > Are you saying that I can repair the master database or I cannot?
> >
> >
> > It looks like our backups are corrupt too. I am not sure when the
> > original errors were introduced. It looks like I might have to use a
> > procedure similar to what Sassan described above.
> >
> >
> >
> >
> >
> >
> > Paul S Randal [MS] wrote:
> >> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes.
> >> I
> >> documented every error message that CHECKDB can return (SQL Server 2005
> >> ones
> >> are done but haven't made it out to the wild yet)
> >>
> >> Can you select * from sysdepends in master? You may be lucky and there's
> >> nothing there, in which case (once you've made sure you know exactly why
> >> the
> >> problem occured and have taken steps to make sure it doesn't happen
> >> again),
> >> you should be able to get away with running repair rather than going back
> >> to
> >> your old backups. And, of course, get a much better backup strategy.
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> >> > Thanks Paul!!!!!
> >> >
> >> > I am trying to understand how to interpret the results of CHECKDB. I
> >> > can't seem to find enough detail to understand what is going on in BOL.
> >> >
> >> > I think we are going to open a ticket with Microsoft support to assist
> >> > us.
> >> >
> >> > Our latest backup overlaps the disk failure. Based off time, it looks
> >> > like the backup was 80-90% complete before the disk went bad.
> >> >
> >> > The next backup we have is over a week old. It looks like our backup
> >> > to tape got bumped by other jobs which ran over :(
> >> >
> >> >
> >> > Paul S Randal [MS] wrote:
> >> >> If the corruption is limited to non-clustered indexes (with index ID >
> >> >> 1),
> >> >> then you won't lose data. In your case, the corrupt page is in the
> >> >> sysdepends heap so you will lose data from that table. This system
> >> >> table
> >> >> stores info about the dependencies between views, triggers, sprocs and
> >> >> the
> >> >> objects they reference. I'm not sure how the system will behave if you
> >> >> run
> >> >> repair - which will delete the page (because the internal pointer in
> >> >> the
> >> >> record that points to the variable-length column offset table is
> >> >> pointing
> >> >> off the end of the record - and so the page could be grossly corrupt).
> >> >>
> >> >> A far better course of action is to run restore in this case. Do you
> >> >> have
> >> >> a
> >> >> valid backup?
> >> >>
> >> >> See my blog post at
> >> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> >> >> for more details.
> >> >>
> >> >> Thanks
> >> >>
> >> >> --
> >> >> Paul Randal
> >> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> >> >> > Can I get data loss from running CHECKDB on master (or user
> >> >> > databases)
> >> >> > if the consistency errors are in indexes?
> >> >> >
> >> >> >
> >> >> > Here are the results of CHECKDB on master:
> >> >> >
> >> >> > Server: Msg 8928, Level 16, State 1, Line 2
> >> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> >> >> > other errors for details.
> >> >> > Server: Msg 8944, Level 16, State 1, Line 2
> >> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> >> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> >> >> > DBCC results for 'master'.
> >> >> > DBCC results for 'sysobjects'.
> >> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
> >> >> > DBCC results for 'sysindexes'.
> >> >> > There are 104 rows in 4 pages for object 'sysindexes'.
> >> >> > DBCC results for 'syscolumns'.
> >> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
> >> >> > DBCC results for 'systypes'.
> >> >> > There are 26 rows in 1 pages for object 'systypes'.
> >> >> > DBCC results for 'syscomments'.
> >> >> > There are 2024 rows in 952 pages for object 'syscomments'.
> >> >> > DBCC results for 'sysfiles1'.
> >> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
> >> >> > DBCC results for 'syspermissions'.
> >> >> > There are 788 rows in 4 pages for object 'syspermissions'.
> >> >> > DBCC results for 'sysusers'.
> >> >> > There are 17 rows in 1 pages for object 'sysusers'.
> >> >> > DBCC results for 'sysproperties'.
> >> >> > There are 0 rows in 0 pages for object 'sysproperties'.
> >> >> > DBCC results for 'sysdepends'.
> >> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
> >> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> >> >> > 'sysdepends' (object ID 12).
> >> >> > DBCC results for 'sysreferences'.
> >> >> > There are 0 rows in 0 pages for object 'sysreferences'.
> >> >> > DBCC results for 'sysfulltextcatalogs'.
> >> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> >> >> > DBCC results for 'sysfulltextnotify'.
> >> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> >> >> > DBCC results for 'sysdatabases'.
> >> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
> >> >> > DBCC results for 'sysxlogins'.
> >> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
> >> >> > DBCC results for 'sysdevices'.
> >> >> > There are 8 rows in 1 pages for object 'sysdevices'.
> >> >> > DBCC results for 'sysmessages'.
> >> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
> >> >> > DBCC results for 'sysconfigures'.
> >> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
> >> >> > DBCC results for 'sysservers'.
> >> >> > There are 7 rows in 1 pages for object 'sysservers'.
> >> >> > DBCC results for 'syslanguages'.
> >> >> > There are 33 rows in 3 pages for object 'syslanguages'.
> >> >> > DBCC results for 'syscharsets'.
> >> >> > There are 114 rows in 33 pages for object 'syscharsets'.
> >> >> > DBCC results for 'sysaltfiles'.
> >> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> >> >> > DBCC results for 'sysfilegroups'.
> >> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> >> >> > DBCC results for 'spt_monitor'.
> >> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
> >> >> > DBCC results for 'spt_values'.
> >> >> > There are 730 rows in 7 pages for object 'spt_values'.
> >> >> > DBCC results for 'spt_fallback_db'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> >> >> > DBCC results for 'spt_fallback_dev'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> >> >> > DBCC results for 'spt_fallback_usg'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> >> >> > DBCC results for 'spt_provider_types'.
> >> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> >> >> > DBCC results for 'spt_datatype_info_ext'.
> >> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> >> >> > DBCC results for 'MSreplication_options'.
> >> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> >> >> > DBCC results for 'spt_datatype_info'.
> >> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> >> >> > DBCC results for 'spt_server_info'.
> >> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
> >> >> > CHECKDB found 0 allocation errors and 2 consistency errors in
> >> >> > database
> >> >> > 'master'.
> >> >> > repair_allow_data_loss is the minimum repair level for the errors
> >> >> > found
> >> >> > by DBCC CHECKDB (master ).
> >> >> > DBCC execution completed. If DBCC printed error messages, contact
> >> >> > your
> >> >> > system administrator.
> >> >> >
> >> >
> >
if the consistency errors are in indexes?
Here are the results of CHECKDB on master:
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 12, index ID 0: Page (1:364) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 2
Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1272 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4884 rows in 85 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2024 rows in 952 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 788 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5282 rows in 28 pages for object 'sysdepends'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sysdepends' (object ID 12).
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 309 rows in 13 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 465 rows in 14 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3831 rows in 165 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 7 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 618 rows in 123 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 2 consistency errors in database
'master'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
for more details.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over :(
Paul S Randal [MS] wrote:
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> for more details.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> > Can I get data loss from running CHECKDB on master (or user databases)
> > if the consistency errors are in indexes?
> >
> >
> > Here are the results of CHECKDB on master:
> >
> > Server: Msg 8928, Level 16, State 1, Line 2
> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> > other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 2
> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> > DBCC results for 'master'.
> > DBCC results for 'sysobjects'.
> > There are 1272 rows in 23 pages for object 'sysobjects'.
> > DBCC results for 'sysindexes'.
> > There are 104 rows in 4 pages for object 'sysindexes'.
> > DBCC results for 'syscolumns'.
> > There are 4884 rows in 85 pages for object 'syscolumns'.
> > DBCC results for 'systypes'.
> > There are 26 rows in 1 pages for object 'systypes'.
> > DBCC results for 'syscomments'.
> > There are 2024 rows in 952 pages for object 'syscomments'.
> > DBCC results for 'sysfiles1'.
> > There are 2 rows in 1 pages for object 'sysfiles1'.
> > DBCC results for 'syspermissions'.
> > There are 788 rows in 4 pages for object 'syspermissions'.
> > DBCC results for 'sysusers'.
> > There are 17 rows in 1 pages for object 'sysusers'.
> > DBCC results for 'sysproperties'.
> > There are 0 rows in 0 pages for object 'sysproperties'.
> > DBCC results for 'sysdepends'.
> > There are 5282 rows in 28 pages for object 'sysdepends'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> > 'sysdepends' (object ID 12).
> > DBCC results for 'sysreferences'.
> > There are 0 rows in 0 pages for object 'sysreferences'.
> > DBCC results for 'sysfulltextcatalogs'.
> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> > DBCC results for 'sysfulltextnotify'.
> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> > DBCC results for 'sysdatabases'.
> > There are 309 rows in 13 pages for object 'sysdatabases'.
> > DBCC results for 'sysxlogins'.
> > There are 465 rows in 14 pages for object 'sysxlogins'.
> > DBCC results for 'sysdevices'.
> > There are 8 rows in 1 pages for object 'sysdevices'.
> > DBCC results for 'sysmessages'.
> > There are 3831 rows in 165 pages for object 'sysmessages'.
> > DBCC results for 'sysconfigures'.
> > There are 38 rows in 1 pages for object 'sysconfigures'.
> > DBCC results for 'sysservers'.
> > There are 7 rows in 1 pages for object 'sysservers'.
> > DBCC results for 'syslanguages'.
> > There are 33 rows in 3 pages for object 'syslanguages'.
> > DBCC results for 'syscharsets'.
> > There are 114 rows in 33 pages for object 'syscharsets'.
> > DBCC results for 'sysaltfiles'.
> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> > DBCC results for 'sysfilegroups'.
> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> > DBCC results for 'spt_monitor'.
> > There are 1 rows in 1 pages for object 'spt_monitor'.
> > DBCC results for 'spt_values'.
> > There are 730 rows in 7 pages for object 'spt_values'.
> > DBCC results for 'spt_fallback_db'.
> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> > DBCC results for 'spt_fallback_dev'.
> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> > DBCC results for 'spt_fallback_usg'.
> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> > DBCC results for 'spt_provider_types'.
> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> > DBCC results for 'spt_datatype_info_ext'.
> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> > DBCC results for 'MSreplication_options'.
> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> > DBCC results for 'spt_datatype_info'.
> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> > DBCC results for 'spt_server_info'.
> > There are 29 rows in 1 pages for object 'spt_server_info'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in database
> > 'master'.
> > repair_allow_data_loss is the minimum repair level for the errors found
> > by DBCC CHECKDB (master ).
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >|||Before you undertake the effort of restoring your currently damaged master db
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over :(
>
> Paul S Randal [MS] wrote:
>> If the corruption is limited to non-clustered indexes (with index ID >
>> 1),
>> then you won't lose data. In your case, the corrupt page is in the
>> sysdepends heap so you will lose data from that table. This system table
>> stores info about the dependencies between views, triggers, sprocs and
>> the
>> objects they reference. I'm not sure how the system will behave if you
>> run
>> repair - which will delete the page (because the internal pointer in the
>> record that points to the variable-length column offset table is pointing
>> off the end of the record - and so the page could be grossly corrupt).
>> A far better course of action is to run restore in this case. Do you have
>> a
>> valid backup?
>> See my blog post at
>> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
>> for more details.
>> Thanks
>> --
>> Paul Randal
>> Lead Program Manager, Microsoft SQL Server Storage Engine
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave" <daveg.01@.gmail.com> wrote in message
>> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
>> > Can I get data loss from running CHECKDB on master (or user databases)
>> > if the consistency errors are in indexes?
>> >
>> >
>> > Here are the results of CHECKDB on master:
>> >
>> > Server: Msg 8928, Level 16, State 1, Line 2
>> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
>> > other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 2
>> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
>> > DBCC results for 'master'.
>> > DBCC results for 'sysobjects'.
>> > There are 1272 rows in 23 pages for object 'sysobjects'.
>> > DBCC results for 'sysindexes'.
>> > There are 104 rows in 4 pages for object 'sysindexes'.
>> > DBCC results for 'syscolumns'.
>> > There are 4884 rows in 85 pages for object 'syscolumns'.
>> > DBCC results for 'systypes'.
>> > There are 26 rows in 1 pages for object 'systypes'.
>> > DBCC results for 'syscomments'.
>> > There are 2024 rows in 952 pages for object 'syscomments'.
>> > DBCC results for 'sysfiles1'.
>> > There are 2 rows in 1 pages for object 'sysfiles1'.
>> > DBCC results for 'syspermissions'.
>> > There are 788 rows in 4 pages for object 'syspermissions'.
>> > DBCC results for 'sysusers'.
>> > There are 17 rows in 1 pages for object 'sysusers'.
>> > DBCC results for 'sysproperties'.
>> > There are 0 rows in 0 pages for object 'sysproperties'.
>> > DBCC results for 'sysdepends'.
>> > There are 5282 rows in 28 pages for object 'sysdepends'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'sysdepends' (object ID 12).
>> > DBCC results for 'sysreferences'.
>> > There are 0 rows in 0 pages for object 'sysreferences'.
>> > DBCC results for 'sysfulltextcatalogs'.
>> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>> > DBCC results for 'sysfulltextnotify'.
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>> > DBCC results for 'sysdatabases'.
>> > There are 309 rows in 13 pages for object 'sysdatabases'.
>> > DBCC results for 'sysxlogins'.
>> > There are 465 rows in 14 pages for object 'sysxlogins'.
>> > DBCC results for 'sysdevices'.
>> > There are 8 rows in 1 pages for object 'sysdevices'.
>> > DBCC results for 'sysmessages'.
>> > There are 3831 rows in 165 pages for object 'sysmessages'.
>> > DBCC results for 'sysconfigures'.
>> > There are 38 rows in 1 pages for object 'sysconfigures'.
>> > DBCC results for 'sysservers'.
>> > There are 7 rows in 1 pages for object 'sysservers'.
>> > DBCC results for 'syslanguages'.
>> > There are 33 rows in 3 pages for object 'syslanguages'.
>> > DBCC results for 'syscharsets'.
>> > There are 114 rows in 33 pages for object 'syscharsets'.
>> > DBCC results for 'sysaltfiles'.
>> > There are 618 rows in 123 pages for object 'sysaltfiles'.
>> > DBCC results for 'sysfilegroups'.
>> > There are 1 rows in 1 pages for object 'sysfilegroups'.
>> > DBCC results for 'spt_monitor'.
>> > There are 1 rows in 1 pages for object 'spt_monitor'.
>> > DBCC results for 'spt_values'.
>> > There are 730 rows in 7 pages for object 'spt_values'.
>> > DBCC results for 'spt_fallback_db'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
>> > DBCC results for 'spt_fallback_dev'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>> > DBCC results for 'spt_fallback_usg'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>> > DBCC results for 'spt_provider_types'.
>> > There are 25 rows in 1 pages for object 'spt_provider_types'.
>> > DBCC results for 'spt_datatype_info_ext'.
>> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>> > DBCC results for 'MSreplication_options'.
>> > There are 2 rows in 1 pages for object 'MSreplication_options'.
>> > DBCC results for 'spt_datatype_info'.
>> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
>> > DBCC results for 'spt_server_info'.
>> > There are 29 rows in 1 pages for object 'spt_server_info'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in database
>> > 'master'.
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by DBCC CHECKDB (master ).
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>|||Paul
Yes I can select * from sysdepends
Are you saying that I can repair the master database or I cannot?
It looks like our backups are corrupt too. I am not sure when the
original errors were introduced. It looks like I might have to use a
procedure similar to what Sassan described above.
Paul S Randal [MS] wrote:
> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
> documented every error message that CHECKDB can return (SQL Server 2005 ones
> are done but haven't made it out to the wild yet)
> Can you select * from sysdepends in master? You may be lucky and there's
> nothing there, in which case (once you've made sure you know exactly why the
> problem occured and have taken steps to make sure it doesn't happen again),
> you should be able to get away with running repair rather than going back to
> your old backups. And, of course, get a much better backup strategy.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> > Thanks Paul!!!!!
> >
> > I am trying to understand how to interpret the results of CHECKDB. I
> > can't seem to find enough detail to understand what is going on in BOL.
> >
> > I think we are going to open a ticket with Microsoft support to assist
> > us.
> >
> > Our latest backup overlaps the disk failure. Based off time, it looks
> > like the backup was 80-90% complete before the disk went bad.
> >
> > The next backup we have is over a week old. It looks like our backup
> > to tape got bumped by other jobs which ran over :(
> >
> >
> > Paul S Randal [MS] wrote:
> >> If the corruption is limited to non-clustered indexes (with index ID >
> >> 1),
> >> then you won't lose data. In your case, the corrupt page is in the
> >> sysdepends heap so you will lose data from that table. This system table
> >> stores info about the dependencies between views, triggers, sprocs and
> >> the
> >> objects they reference. I'm not sure how the system will behave if you
> >> run
> >> repair - which will delete the page (because the internal pointer in the
> >> record that points to the variable-length column offset table is pointing
> >> off the end of the record - and so the page could be grossly corrupt).
> >>
> >> A far better course of action is to run restore in this case. Do you have
> >> a
> >> valid backup?
> >>
> >> See my blog post at
> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> >> for more details.
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> >> > Can I get data loss from running CHECKDB on master (or user databases)
> >> > if the consistency errors are in indexes?
> >> >
> >> >
> >> > Here are the results of CHECKDB on master:
> >> >
> >> > Server: Msg 8928, Level 16, State 1, Line 2
> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> >> > other errors for details.
> >> > Server: Msg 8944, Level 16, State 1, Line 2
> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> >> > DBCC results for 'master'.
> >> > DBCC results for 'sysobjects'.
> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
> >> > DBCC results for 'sysindexes'.
> >> > There are 104 rows in 4 pages for object 'sysindexes'.
> >> > DBCC results for 'syscolumns'.
> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
> >> > DBCC results for 'systypes'.
> >> > There are 26 rows in 1 pages for object 'systypes'.
> >> > DBCC results for 'syscomments'.
> >> > There are 2024 rows in 952 pages for object 'syscomments'.
> >> > DBCC results for 'sysfiles1'.
> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
> >> > DBCC results for 'syspermissions'.
> >> > There are 788 rows in 4 pages for object 'syspermissions'.
> >> > DBCC results for 'sysusers'.
> >> > There are 17 rows in 1 pages for object 'sysusers'.
> >> > DBCC results for 'sysproperties'.
> >> > There are 0 rows in 0 pages for object 'sysproperties'.
> >> > DBCC results for 'sysdepends'.
> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> >> > 'sysdepends' (object ID 12).
> >> > DBCC results for 'sysreferences'.
> >> > There are 0 rows in 0 pages for object 'sysreferences'.
> >> > DBCC results for 'sysfulltextcatalogs'.
> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> >> > DBCC results for 'sysfulltextnotify'.
> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> >> > DBCC results for 'sysdatabases'.
> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
> >> > DBCC results for 'sysxlogins'.
> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
> >> > DBCC results for 'sysdevices'.
> >> > There are 8 rows in 1 pages for object 'sysdevices'.
> >> > DBCC results for 'sysmessages'.
> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
> >> > DBCC results for 'sysconfigures'.
> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
> >> > DBCC results for 'sysservers'.
> >> > There are 7 rows in 1 pages for object 'sysservers'.
> >> > DBCC results for 'syslanguages'.
> >> > There are 33 rows in 3 pages for object 'syslanguages'.
> >> > DBCC results for 'syscharsets'.
> >> > There are 114 rows in 33 pages for object 'syscharsets'.
> >> > DBCC results for 'sysaltfiles'.
> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> >> > DBCC results for 'sysfilegroups'.
> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> >> > DBCC results for 'spt_monitor'.
> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
> >> > DBCC results for 'spt_values'.
> >> > There are 730 rows in 7 pages for object 'spt_values'.
> >> > DBCC results for 'spt_fallback_db'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> >> > DBCC results for 'spt_fallback_dev'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> >> > DBCC results for 'spt_fallback_usg'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> >> > DBCC results for 'spt_provider_types'.
> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> >> > DBCC results for 'spt_datatype_info_ext'.
> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> >> > DBCC results for 'MSreplication_options'.
> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> >> > DBCC results for 'spt_datatype_info'.
> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> >> > DBCC results for 'spt_server_info'.
> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
> >> > CHECKDB found 0 allocation errors and 2 consistency errors in database
> >> > 'master'.
> >> > repair_allow_data_loss is the minimum repair level for the errors found
> >> > by DBCC CHECKDB (master ).
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >|||I suggest you take a backup of master and try running repair, and also the
other stuff I suggest below.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150905664.179677.169920@.c74g2000cwc.googlegroups.com...
> Paul
> Yes I can select * from sysdepends
> Are you saying that I can repair the master database or I cannot?
>
> It looks like our backups are corrupt too. I am not sure when the
> original errors were introduced. It looks like I might have to use a
> procedure similar to what Sassan described above.
>
>
>
> Paul S Randal [MS] wrote:
>> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes.
>> I
>> documented every error message that CHECKDB can return (SQL Server 2005
>> ones
>> are done but haven't made it out to the wild yet)
>> Can you select * from sysdepends in master? You may be lucky and there's
>> nothing there, in which case (once you've made sure you know exactly why
>> the
>> problem occured and have taken steps to make sure it doesn't happen
>> again),
>> you should be able to get away with running repair rather than going back
>> to
>> your old backups. And, of course, get a much better backup strategy.
>> Thanks
>> --
>> Paul Randal
>> Lead Program Manager, Microsoft SQL Server Storage Engine
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave" <daveg.01@.gmail.com> wrote in message
>> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
>> > Thanks Paul!!!!!
>> >
>> > I am trying to understand how to interpret the results of CHECKDB. I
>> > can't seem to find enough detail to understand what is going on in BOL.
>> >
>> > I think we are going to open a ticket with Microsoft support to assist
>> > us.
>> >
>> > Our latest backup overlaps the disk failure. Based off time, it looks
>> > like the backup was 80-90% complete before the disk went bad.
>> >
>> > The next backup we have is over a week old. It looks like our backup
>> > to tape got bumped by other jobs which ran over :(
>> >
>> >
>> > Paul S Randal [MS] wrote:
>> >> If the corruption is limited to non-clustered indexes (with index ID >
>> >> 1),
>> >> then you won't lose data. In your case, the corrupt page is in the
>> >> sysdepends heap so you will lose data from that table. This system
>> >> table
>> >> stores info about the dependencies between views, triggers, sprocs and
>> >> the
>> >> objects they reference. I'm not sure how the system will behave if you
>> >> run
>> >> repair - which will delete the page (because the internal pointer in
>> >> the
>> >> record that points to the variable-length column offset table is
>> >> pointing
>> >> off the end of the record - and so the page could be grossly corrupt).
>> >>
>> >> A far better course of action is to run restore in this case. Do you
>> >> have
>> >> a
>> >> valid backup?
>> >>
>> >> See my blog post at
>> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
>> >> for more details.
>> >>
>> >> Thanks
>> >>
>> >> --
>> >> Paul Randal
>> >> Lead Program Manager, Microsoft SQL Server Storage Engine
>> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >> "Dave" <daveg.01@.gmail.com> wrote in message
>> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
>> >> > Can I get data loss from running CHECKDB on master (or user
>> >> > databases)
>> >> > if the consistency errors are in indexes?
>> >> >
>> >> >
>> >> > Here are the results of CHECKDB on master:
>> >> >
>> >> > Server: Msg 8928, Level 16, State 1, Line 2
>> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
>> >> > other errors for details.
>> >> > Server: Msg 8944, Level 16, State 1, Line 2
>> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
>> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
>> >> > DBCC results for 'master'.
>> >> > DBCC results for 'sysobjects'.
>> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
>> >> > DBCC results for 'sysindexes'.
>> >> > There are 104 rows in 4 pages for object 'sysindexes'.
>> >> > DBCC results for 'syscolumns'.
>> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
>> >> > DBCC results for 'systypes'.
>> >> > There are 26 rows in 1 pages for object 'systypes'.
>> >> > DBCC results for 'syscomments'.
>> >> > There are 2024 rows in 952 pages for object 'syscomments'.
>> >> > DBCC results for 'sysfiles1'.
>> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
>> >> > DBCC results for 'syspermissions'.
>> >> > There are 788 rows in 4 pages for object 'syspermissions'.
>> >> > DBCC results for 'sysusers'.
>> >> > There are 17 rows in 1 pages for object 'sysusers'.
>> >> > DBCC results for 'sysproperties'.
>> >> > There are 0 rows in 0 pages for object 'sysproperties'.
>> >> > DBCC results for 'sysdepends'.
>> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
>> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> >> > 'sysdepends' (object ID 12).
>> >> > DBCC results for 'sysreferences'.
>> >> > There are 0 rows in 0 pages for object 'sysreferences'.
>> >> > DBCC results for 'sysfulltextcatalogs'.
>> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>> >> > DBCC results for 'sysfulltextnotify'.
>> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>> >> > DBCC results for 'sysdatabases'.
>> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
>> >> > DBCC results for 'sysxlogins'.
>> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
>> >> > DBCC results for 'sysdevices'.
>> >> > There are 8 rows in 1 pages for object 'sysdevices'.
>> >> > DBCC results for 'sysmessages'.
>> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
>> >> > DBCC results for 'sysconfigures'.
>> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
>> >> > DBCC results for 'sysservers'.
>> >> > There are 7 rows in 1 pages for object 'sysservers'.
>> >> > DBCC results for 'syslanguages'.
>> >> > There are 33 rows in 3 pages for object 'syslanguages'.
>> >> > DBCC results for 'syscharsets'.
>> >> > There are 114 rows in 33 pages for object 'syscharsets'.
>> >> > DBCC results for 'sysaltfiles'.
>> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
>> >> > DBCC results for 'sysfilegroups'.
>> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
>> >> > DBCC results for 'spt_monitor'.
>> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
>> >> > DBCC results for 'spt_values'.
>> >> > There are 730 rows in 7 pages for object 'spt_values'.
>> >> > DBCC results for 'spt_fallback_db'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
>> >> > DBCC results for 'spt_fallback_dev'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>> >> > DBCC results for 'spt_fallback_usg'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>> >> > DBCC results for 'spt_provider_types'.
>> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
>> >> > DBCC results for 'spt_datatype_info_ext'.
>> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>> >> > DBCC results for 'MSreplication_options'.
>> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
>> >> > DBCC results for 'spt_datatype_info'.
>> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
>> >> > DBCC results for 'spt_server_info'.
>> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
>> >> > CHECKDB found 0 allocation errors and 2 consistency errors in
>> >> > database
>> >> > 'master'.
>> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> > found
>> >> > by DBCC CHECKDB (master ).
>> >> > DBCC execution completed. If DBCC printed error messages, contact
>> >> > your
>> >> > system administrator.
>> >> >
>> >
>|||PSS asked me to rebuild it. We have our application back up and
running now.
Thanks for all the help guys! I have a much better understanding of
database corruption, backup, and restore now.
I will make sure we get on a sensible backup program.
Paul S Randal [MS] wrote:
> I suggest you take a backup of master and try running repair, and also the
> other stuff I suggest below.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150905664.179677.169920@.c74g2000cwc.googlegroups.com...
> > Paul
> >
> > Yes I can select * from sysdepends
> >
> > Are you saying that I can repair the master database or I cannot?
> >
> >
> > It looks like our backups are corrupt too. I am not sure when the
> > original errors were introduced. It looks like I might have to use a
> > procedure similar to what Sassan described above.
> >
> >
> >
> >
> >
> >
> > Paul S Randal [MS] wrote:
> >> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes.
> >> I
> >> documented every error message that CHECKDB can return (SQL Server 2005
> >> ones
> >> are done but haven't made it out to the wild yet)
> >>
> >> Can you select * from sysdepends in master? You may be lucky and there's
> >> nothing there, in which case (once you've made sure you know exactly why
> >> the
> >> problem occured and have taken steps to make sure it doesn't happen
> >> again),
> >> you should be able to get away with running repair rather than going back
> >> to
> >> your old backups. And, of course, get a much better backup strategy.
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> >> > Thanks Paul!!!!!
> >> >
> >> > I am trying to understand how to interpret the results of CHECKDB. I
> >> > can't seem to find enough detail to understand what is going on in BOL.
> >> >
> >> > I think we are going to open a ticket with Microsoft support to assist
> >> > us.
> >> >
> >> > Our latest backup overlaps the disk failure. Based off time, it looks
> >> > like the backup was 80-90% complete before the disk went bad.
> >> >
> >> > The next backup we have is over a week old. It looks like our backup
> >> > to tape got bumped by other jobs which ran over :(
> >> >
> >> >
> >> > Paul S Randal [MS] wrote:
> >> >> If the corruption is limited to non-clustered indexes (with index ID >
> >> >> 1),
> >> >> then you won't lose data. In your case, the corrupt page is in the
> >> >> sysdepends heap so you will lose data from that table. This system
> >> >> table
> >> >> stores info about the dependencies between views, triggers, sprocs and
> >> >> the
> >> >> objects they reference. I'm not sure how the system will behave if you
> >> >> run
> >> >> repair - which will delete the page (because the internal pointer in
> >> >> the
> >> >> record that points to the variable-length column offset table is
> >> >> pointing
> >> >> off the end of the record - and so the page could be grossly corrupt).
> >> >>
> >> >> A far better course of action is to run restore in this case. Do you
> >> >> have
> >> >> a
> >> >> valid backup?
> >> >>
> >> >> See my blog post at
> >> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> >> >> for more details.
> >> >>
> >> >> Thanks
> >> >>
> >> >> --
> >> >> Paul Randal
> >> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> >> >> > Can I get data loss from running CHECKDB on master (or user
> >> >> > databases)
> >> >> > if the consistency errors are in indexes?
> >> >> >
> >> >> >
> >> >> > Here are the results of CHECKDB on master:
> >> >> >
> >> >> > Server: Msg 8928, Level 16, State 1, Line 2
> >> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> >> >> > other errors for details.
> >> >> > Server: Msg 8944, Level 16, State 1, Line 2
> >> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> >> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> >> >> > DBCC results for 'master'.
> >> >> > DBCC results for 'sysobjects'.
> >> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
> >> >> > DBCC results for 'sysindexes'.
> >> >> > There are 104 rows in 4 pages for object 'sysindexes'.
> >> >> > DBCC results for 'syscolumns'.
> >> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
> >> >> > DBCC results for 'systypes'.
> >> >> > There are 26 rows in 1 pages for object 'systypes'.
> >> >> > DBCC results for 'syscomments'.
> >> >> > There are 2024 rows in 952 pages for object 'syscomments'.
> >> >> > DBCC results for 'sysfiles1'.
> >> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
> >> >> > DBCC results for 'syspermissions'.
> >> >> > There are 788 rows in 4 pages for object 'syspermissions'.
> >> >> > DBCC results for 'sysusers'.
> >> >> > There are 17 rows in 1 pages for object 'sysusers'.
> >> >> > DBCC results for 'sysproperties'.
> >> >> > There are 0 rows in 0 pages for object 'sysproperties'.
> >> >> > DBCC results for 'sysdepends'.
> >> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
> >> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> >> >> > 'sysdepends' (object ID 12).
> >> >> > DBCC results for 'sysreferences'.
> >> >> > There are 0 rows in 0 pages for object 'sysreferences'.
> >> >> > DBCC results for 'sysfulltextcatalogs'.
> >> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> >> >> > DBCC results for 'sysfulltextnotify'.
> >> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> >> >> > DBCC results for 'sysdatabases'.
> >> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
> >> >> > DBCC results for 'sysxlogins'.
> >> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
> >> >> > DBCC results for 'sysdevices'.
> >> >> > There are 8 rows in 1 pages for object 'sysdevices'.
> >> >> > DBCC results for 'sysmessages'.
> >> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
> >> >> > DBCC results for 'sysconfigures'.
> >> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
> >> >> > DBCC results for 'sysservers'.
> >> >> > There are 7 rows in 1 pages for object 'sysservers'.
> >> >> > DBCC results for 'syslanguages'.
> >> >> > There are 33 rows in 3 pages for object 'syslanguages'.
> >> >> > DBCC results for 'syscharsets'.
> >> >> > There are 114 rows in 33 pages for object 'syscharsets'.
> >> >> > DBCC results for 'sysaltfiles'.
> >> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> >> >> > DBCC results for 'sysfilegroups'.
> >> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> >> >> > DBCC results for 'spt_monitor'.
> >> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
> >> >> > DBCC results for 'spt_values'.
> >> >> > There are 730 rows in 7 pages for object 'spt_values'.
> >> >> > DBCC results for 'spt_fallback_db'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> >> >> > DBCC results for 'spt_fallback_dev'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> >> >> > DBCC results for 'spt_fallback_usg'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> >> >> > DBCC results for 'spt_provider_types'.
> >> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> >> >> > DBCC results for 'spt_datatype_info_ext'.
> >> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> >> >> > DBCC results for 'MSreplication_options'.
> >> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> >> >> > DBCC results for 'spt_datatype_info'.
> >> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> >> >> > DBCC results for 'spt_server_info'.
> >> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
> >> >> > CHECKDB found 0 allocation errors and 2 consistency errors in
> >> >> > database
> >> >> > 'master'.
> >> >> > repair_allow_data_loss is the minimum repair level for the errors
> >> >> > found
> >> >> > by DBCC CHECKDB (master ).
> >> >> > DBCC execution completed. If DBCC printed error messages, contact
> >> >> > your
> >> >> > system administrator.
> >> >> >
> >> >
> >
Subscribe to:
Posts (Atom)