Checkpoints problem in parallel tasks

Hi,

I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.

The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.

If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.

Please let me know if there is any setting that only the failed task executes on restart.

Thanks in advance

Essentially, you want to track the outcome of parallel execute package tasks, and only re-execute those which have failed. The problem with using checkpoint files to accomplish this, is that checkpoint files don't track the status of parallel containers after the first task failure associated with FailPackageOnFailure happens.

What that means, if you have 10 parallel EPTs (execute package tasks), and any one of them has a task failure, none of the subsequently completed EPT tasks, whether they succeed or fail, have their outcomes written to the checkpoint file. So, on package restart, "post last checkpoint file write" tasks will run again.

An easier approach might be to put a for loop around each EPT, and loop until successful, using a variable scoped at the For Loop as a"Go/No Go" decision maker, and max retry count.

However, if you want to do it in SSIS using a restart mechanism, you could roll your own checkpointing mechanism.

Such a mechanism would mean creating an OnTaskFailed event handler which would track the failed EPT SourceID/SourceName's (read TaskID/TaskName).

Then add an OnPostExecute event handler to determine those EPTs which succeeded by inference (they didn't fail). Add in a final "On Completetion" script task to append the successful TaskIDs to a configuration file which would then be read in automatically on subsequent execution.

Lastly, you'd set the the Disable property on each EPT to something like FINDSTRING(@.SuccessfulTaskIDs,@.System::TaskID,1) > 0. You can do it that way, but its not point and click by any stretch.

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?
Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checking if anything changed

I have a series of records that I periodically sum up, and then place those
sums in a master record for easy reporting. Every time the sum changes, I
note down that a change was made by updating a datetime in the master.
The trick is knowing if anything changed or not. I do this...
DECLARE @.didChange AS boolean
SET @.didChange = FALSE
IF ((@.newFilled IS null AND @.oldFilled IS NOT null) OR (@.newFilled IS NOT
null AND @.oldFilled IS null) OR (ROUND(@.newFilled, 2) <> ROUND(@.oldFilled,
2))) @.didChange = TRUE
there's a couple more IFs following. BTW, there's a syntax error in there
somewhere, I'm still trying to find it.
My question is whether or not I can simplify the if down to something
smaller. As you can see, it tests three cases...
1) the value was changed TO null from anything
2) the value was changed FROM null to anything
3) the value simply changed
In the past I tried a greatly simplified solution...
IF ISNULL(@.newFilled, 0) <> ISNULL(@.oldFilled, 0) THEN @.didChange = TRUE
The problem with this approach is that it "misses" changes from null to zero
or back. It just didn't work right.
So can I do this...
IF ISNULL(@.newFilled, -1) <> ISNULL(@.oldFilled, -1) THEN...
The values are always positive, so they can never be -1. Do you think this
is a good approach, or is there some edge case I'm forgetting about?
MauryAhh, the syntax error...
obviously that should be a tinyint (or bit I guess) and there needs to be a
SET
those are fixed.

checking data and index linkages on msdb and master

Hi, I am running a maintenance plan daily, and under default configurations,
it's failing to Check data and index linkages on the "master" and "msdb"
database because it's not in single user mode. Am I not able to do this?
If so, do I need to change it to single user? Will that harm anything else?
_____
DC GIt's failing because you've set the job up to automatically repair minor
errors. You should not do this - always investigate integrity problems
before taking any corrective action (i.e. restoring from your backups)
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"DC Gringo" <glevine@.visiontechnology.net> wrote in message
news:ueA0bX0lDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Hi, I am running a maintenance plan daily, and under default
configurations,
> it's failing to Check data and index linkages on the "master" and "msdb"
> database because it's not in single user mode. Am I not able to do this?
> If so, do I need to change it to single user? Will that harm anything
else?
> _____
> DC G
>

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.
> >> >> >
> >> >
> >

CHECKDB on master ?

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/sqlserversto.../07/620319.aspx
for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/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.
>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/sqlserversto.../07/620319.aspx
for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/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:[vbcol=seagreen]
> 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/sqlserversto...ne/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...|||Before you undertake the effort of restoring your currently damaged master d
b
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/sqlserverstor...ne/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:
>|||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:[vbcol=seagreen]
> 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/sqlserversto...ne/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...|||Before you undertake the effort of restoring your currently damaged master d
b
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/sqlserverstor...ne/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:
>|||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:[vbcol=seagreen]
> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
> documented every error message that CHECKDB can return (SQL Server 2005 on
es
> 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 t
he
> 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/sqlserverstor...ne/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...