DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database Architect
This column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pro...ads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>
|||Thanks Gail.
MTM
|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect
|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pro...ads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>
|||I have a feeling that is it difficult to get the time of the grow operations, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we would need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably show us the timestamp
for the log record based on the LSN, but that requires that the log record exists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a server-side trace to
capture the autogrow operations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You might consider using
> Profiler to monitor for events that cause the log file to autogrow. See the topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as well.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pro...ads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database ArchitectThis column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Gail.
MTM|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>|||I have a feeling that is it difficult to get the time of the grow operations
, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we woul
d need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably
show us the timestamp
for the log record based on the LSN, but that requires that the log record e
xists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a ser
ver-side trace to
capture the autogrow operations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You
might consider using
> Profiler to monitor for events that cause the log file to autogrow. See t
he topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as we
ll.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database ArchitectThis column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Gail.
MTM|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>|||I have a feeling that is it difficult to get the time of the grow operations, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we would need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably show us the timestamp
for the log record based on the LSN, but that requires that the log record exists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a server-side trace to
capture the autogrow operations...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You might consider using
> Profiler to monitor for events that cause the log file to autogrow. See the topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as well.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>> Ok, read that but unfortunately it doesn't really help me much.
>> What I'm trying to find out is at what time, and by how much, the transaction log grows? I was
>> hoping that this CreateLSN might have helped, but I can't see how it can. Is there any other way
>> to find this out?
>> Michael MacGregor
>> Database Architect
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBAI think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBAI think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBA
I think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>

DBCC CHECKIDENT call never returns

I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
returning, even after a day. If I do the same call through Query Analyser it
takes about a minute to run.
Can anyone think of anything that might affect the use of DBCC CHECKIDENT. I
cant check for deadlocks, because the Current Activity tab in Enterprise
Manager is locked up, presumably because of a deadlock of some kind...
Thanks in advance,
Fred Forsyth.
Development Manager
The reason EM is "locked up" is certainly not due to a deadlock. SQL Server
automatically resolves deadlocks in a few seconds. It might be due to
blocking though. What permissions does the vb app have? To run DBCC
CHECKIDENT you must be sa or dbo.
Andrew J. Kelly SQL MVP
"Fred Forsyth" <fred.forsyth.nospam@.nospam.rcp.co.uk> wrote in message
news:%23nkjGb6BGHA.4076@.TK2MSFTNGP14.phx.gbl...
>I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
>returning, even after a day. If I do the same call through Query Analyser
>it takes about a minute to run.
> Can anyone think of anything that might affect the use of DBCC CHECKIDENT.
> I cant check for deadlocks, because the Current Activity tab in Enterprise
> Manager is locked up, presumably because of a deadlock of some kind...
> Thanks in advance,
> Fred Forsyth.
> Development Manager
>
|||Fred Forsyth (fred.forsyth.nospam@.nospam.rcp.co.uk) writes:
> I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
> returning, even after a day. If I do the same call through Query
> Analyser it takes about a minute to run.
> Can anyone think of anything that might affect the use of DBCC
> CHECKIDENT. I cant check for deadlocks, because the Current Activity tab
> in Enterprise Manager is locked up, presumably because of a deadlock of
> some kind...
Current Activity gets block if some process has create tables within a
transaction that still is open.
Use sp_who or sp_who2 to check for blocking. Check the Blk or BlkBy column.
If this column has a non-zero value, the spid on this row, is blocked by
spid in the Blk column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Some further investigation has shown that the VB process is blocked
waiting on a checkpoint to complete, and that there is a system process
that is attempting to checkpoint the database but is blocked by the VB
process. (I can this through select * from sysprocesses).
I suspect that the reason for the need to checkpoint is that the I
removed the log files from the databases, and then run the dbcc command
on them, and that this is what is causing the problems. I think my test
case is at fault, because in the real world the original log files
would be there (they are very big, hence the reason I removed them).
|||The log files are pretty important and it is never a good idea to remove
them.
Andrew J. Kelly SQL MVP
<fredforsyth@.gmail.com> wrote in message
news:1135870204.499678.310830@.f14g2000cwb.googlegr oups.com...
> Some further investigation has shown that the VB process is blocked
> waiting on a checkpoint to complete, and that there is a system process
> that is attempting to checkpoint the database but is blocked by the VB
> process. (I can this through select * from sysprocesses).
> I suspect that the reason for the need to checkpoint is that the I
> removed the log files from the databases, and then run the dbcc command
> on them, and that this is what is causing the problems. I think my test
> case is at fault, because in the real world the original log files
> would be there (they are very big, hence the reason I removed them).
>
|||(fredforsyth@.gmail.com) writes:
> Some further investigation has shown that the VB process is blocked
> waiting on a checkpoint to complete, and that there is a system process
> that is attempting to checkpoint the database but is blocked by the VB
> process. (I can this through select * from sysprocesses).
> I suspect that the reason for the need to checkpoint is that the I
> removed the log files from the databases,
You did what? Never, never do that again! If you remove the log file,
you are gambling with your database. There is a fair chance that you
will have to remove the data file as well, because it will not be
accessibe.

> I think my test case is at fault, because in the real world the original
> log files would be there (they are very big, hence the reason I removed
> them).
You can shrink a log file with DBCC SHRINKFILE, but don't do this with a
production database, unless the log file has grown because of some
one-off operation.
For a production database, you should also backup the transaction log
regulary. If you don't care about up-to-the-point recovery, you can set
the database in simple recovery mode.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

DBCC CHECKDB returns error, sys.sysobjvalues

Hello,
Just starting to work with sql 2005.
Run DBCC CHECKDB on user database and received this message:
Incorrect PFS free space information for page (1:224) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB
data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sys.sysobjvalues' (object ID 60).
The thing is I can't find sys.sysobjvalues table anywhere.
What is it?
Thanks.
Hi Jan
If you run sp_help, or select * from sys.objects, you should see object, and
see the fact that it is a system table.
By default, you cannot access the system tables in SQL Server 2005; metadata
is accessed through catalog views (such as sys.objects)
If you use the Dedicated Administrator Connection, you can select from
sys.sysobjvalues, but it wouldn't enlighten you much as it is very cryptic
information.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:79ED5E8D-0BAC-4E1B-8109-A8659F78513C@.microsoft.com...
> Hello,
> Just starting to work with sql 2005.
> Run DBCC CHECKDB on user database and received this message:
> Incorrect PFS free space information for page (1:224) in object ID 60,
> index
> ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
> LOB
> data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sys.sysobjvalues' (object ID 60).
> The thing is I can't find sys.sysobjvalues table anywhere.
> What is it?
> Thanks.

DBCC CHECKDB returns error, sys.sysobjvalues

Hello,
Just starting to work with sql 2005.
Run DBCC CHECKDB on user database and received this message:
Incorrect PFS free space information for page (1:224) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB
data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sys.sysobjvalues' (object ID 60).
The thing is I can't find sys.sysobjvalues table anywhere.
What is it?
Thanks.Hi Jan
If you run sp_help, or select * from sys.objects, you should see object, and
see the fact that it is a system table.
By default, you cannot access the system tables in SQL Server 2005; metadata
is accessed through catalog views (such as sys.objects)
If you use the Dedicated Administrator Connection, you can select from
sys.sysobjvalues, but it wouldn't enlighten you much as it is very cryptic
information.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:79ED5E8D-0BAC-4E1B-8109-A8659F78513C@.microsoft.com...
> Hello,
> Just starting to work with sql 2005.
> Run DBCC CHECKDB on user database and received this message:
> Incorrect PFS free space information for page (1:224) in object ID 60,
> index
> ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
> LOB
> data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sys.sysobjvalues' (object ID 60).
> The thing is I can't find sys.sysobjvalues table anywhere.
> What is it?
> Thanks.

DBCC CHECKDB returns error, sys.sysobjvalues

Hello,
Just starting to work with sql 2005.
Run DBCC CHECKDB on user database and received this message:
Incorrect PFS free space information for page (1:224) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LO
B
data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sys.sysobjvalues' (object ID 60).
The thing is I can't find sys.sysobjvalues table anywhere.
What is it?
Thanks.Hi Jan
If you run sp_help, or select * from sys.objects, you should see object, and
see the fact that it is a system table.
By default, you cannot access the system tables in SQL Server 2005; metadata
is accessed through catalog views (such as sys.objects)
If you use the Dedicated Administrator Connection, you can select from
sys.sysobjvalues, but it wouldn't enlighten you much as it is very cryptic
information.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:79ED5E8D-0BAC-4E1B-8109-A8659F78513C@.microsoft.com...
> Hello,
> Just starting to work with sql 2005.
> Run DBCC CHECKDB on user database and received this message:
> Incorrect PFS free space information for page (1:224) in object ID 60,
> index
> ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
> LOB
> data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sys.sysobjvalues' (object ID 60).
> The thing is I can't find sys.sysobjvalues table anywhere.
> What is it?
> Thanks.

DBCC CheckDB Question

Is there anyway I can programmatically determine if DBCC CheckDB has found any errors? When I run this command in Management Studio it simply returns a slew of messages. What I would like to do is to create a stored proc that executes this command, somehow determine if any errors were found, and then notify the appropriate person for follow-up. I am at a loss to know how to programmicatically determine if DBCC CheckDB encountered any errors during its run.

Thanks for your help.

A few different things - in the automated routine you are using, you can save the output to a file and search for the errors with a tool like findstr. Or import the file into a table and search for errors in the table. Or create a table that will use DBCC CHECKDB WITH TABLERESULTS, so an insert into that table, along the lines of:

Insert Into YourCheckDBTableResults
Exec ('dbcc checkdb with tableresults')
And then check the table you are storing the results in.

-Sue

DBCC CHECKDB Internal Error

After a hiccup on my server computer, Windows ran CHKDSK on the next
reboot.
Since then, DBCC CHECKDB now returns the following:
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing.
Contact Customer Support Services.
And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The semaphore timeout
period has expired.)
Based on what I could dig up, both of these messages may be caused by
hardware errors (even though a repeated CHKDSK /R finds no additional
problems).
I have tried copying the database file and running DBCC CHECKDB
against the copy (in case a flaky sector is causing the problem).
However, this returned the same error.
Given that a hardware error may be the original cause of this problem
and given that I would like to avoid restoring to backup, is there
anything I can do to resolve the problem (other than call MS PSS)?
For example, is there some way to force DBCC CHECKDB to continue past
the problem? Or, is there some way to backup all of the data that can
be accessed (through a single command or through programming)?
Any help is appreciated!
Gary Geniesse
NeuroDimension, Inc.
> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
CHKDSK can detect/correct only file system problems. It appears you have
corruption within database files (e.g. torn pages) due to the previous
hardware problem.

> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
Your best course of action is to restore from backup and apply transaction
log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
last resort to salvage data and with the understanding a significant about
of data might be lost. I suggest you make copies of the database files
before taking further corrective action.
It looks like the on-disk data corruption that is too severe for normal DBCC
processing. There is no technique can materialize data that no longer
exists but, depending on the nature of the corruption, you might be able to
salvage data by copying data to another database with the same schema. I
suggest you don't use this database for any purpose other than salvage going
forward.
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryGen" <gary@.garygen.com> wrote in message
news:1184966021.465560.312450@.w3g2000hsg.googlegro ups.com...
> After a hiccup on my server computer, Windows ran CHKDSK on the next
> reboot.
> Since then, DBCC CHECKDB now returns the following:
> --
> Msg 8967, Level 16, State 216, Line 1
> An internal error occurred in DBCC that prevented further processing.
> Contact Customer Support Services.
> --
> And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
> --
> Msg 121, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - The semaphore timeout
> period has expired.)
> --
> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
> I have tried copying the database file and running DBCC CHECKDB
> against the copy (in case a flaky sector is causing the problem).
> However, this returned the same error.
> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
> For example, is there some way to force DBCC CHECKDB to continue past
> the problem? Or, is there some way to backup all of the data that can
> be accessed (through a single command or through programming)?
> Any help is appreciated!
> Gary Geniesse
> NeuroDimension, Inc.
>
|||Thanks for the reply. I appreciate the help!

> Your best course of action is to restore from backup and apply transaction
> log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
> last resort to salvage data and with the understanding a significant about
> of data might be lost. I suggest you make copies of the database files
> before taking further corrective action.
Understood. Unfortunately, the first time you really need a backup is
when you discover your backup strategy is not adequate/not set up
properly. So yes, I was using REPAIR_ALLOW_DATA_LOSS with the
understanding that some data might be lost.
The odd thing is, I'm currently converting some software from ADODB to
ADO.NET. The ADO.NET routines keep exiting out due to "a logical
consistency-based I/O error: incorrect checksum", but the ADODB
routines seem to be running fine. Evidently ADODB or the old routines
are coded in such a way as to be not as sensitive to consistency
errors. Therefore, I'm not expecting excessive data loss, just a small
subset of relevant records to be hosed (if any).

> It looks like the on-disk data corruption that is too severe for normal DBCC
> processing. There is no technique can materialize data that no longer
> exists but, depending on the nature of the corruption, you might be able to
> salvage data by copying data to another database with the same schema. I
> suggest you don't use this database for any purpose other than salvage going
> forward.
Understood. If data is lost, it's lost. I'm not expecting DBCC to
recreate it. However, since a REPAIR_ALLOW_DATA_LOSS option was
available, it seemed reasonable that it could cleanse the database
enough to clear the error, even at the expense of mangling or deleting
the page with the bad checksum. I was definitely surprised that
CHECKDB returned messages like "internal error" and "transport error",
especially when the disk itself should no longer be returning errors.
It would seem like CHECKDB should be robust enough to handle this
case.
I'm currently trying Copy Database. If that fails, I'll try a more
granular approach. Thanks again for your help!
Gary Geniesse
NeuroDimension, Inc.

DBCC CHECKDB Internal Error

After a hiccup on my server computer, Windows ran CHKDSK on the next
reboot.
Since then, DBCC CHECKDB now returns the following:
--
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing.
Contact Customer Support Services.
--
And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
--
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The semaphore timeout
period has expired.)
--
Based on what I could dig up, both of these messages may be caused by
hardware errors (even though a repeated CHKDSK /R finds no additional
problems).
I have tried copying the database file and running DBCC CHECKDB
against the copy (in case a flaky sector is causing the problem).
However, this returned the same error.
Given that a hardware error may be the original cause of this problem
and given that I would like to avoid restoring to backup, is there
anything I can do to resolve the problem (other than call MS PSS)?
For example, is there some way to force DBCC CHECKDB to continue past
the problem? Or, is there some way to backup all of the data that can
be accessed (through a single command or through programming)?
Any help is appreciated!
Gary Geniesse
NeuroDimension, Inc.> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
CHKDSK can detect/correct only file system problems. It appears you have
corruption within database files (e.g. torn pages) due to the previous
hardware problem.
> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
Your best course of action is to restore from backup and apply transaction
log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
last resort to salvage data and with the understanding a significant about
of data might be lost. I suggest you make copies of the database files
before taking further corrective action.
It looks like the on-disk data corruption that is too severe for normal DBCC
processing. There is no technique can materialize data that no longer
exists but, depending on the nature of the corruption, you might be able to
salvage data by copying data to another database with the same schema. I
suggest you don't use this database for any purpose other than salvage going
forward.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryGen" <gary@.garygen.com> wrote in message
news:1184966021.465560.312450@.w3g2000hsg.googlegroups.com...
> After a hiccup on my server computer, Windows ran CHKDSK on the next
> reboot.
> Since then, DBCC CHECKDB now returns the following:
> --
> Msg 8967, Level 16, State 216, Line 1
> An internal error occurred in DBCC that prevented further processing.
> Contact Customer Support Services.
> --
> And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
> --
> Msg 121, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - The semaphore timeout
> period has expired.)
> --
> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
> I have tried copying the database file and running DBCC CHECKDB
> against the copy (in case a flaky sector is causing the problem).
> However, this returned the same error.
> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
> For example, is there some way to force DBCC CHECKDB to continue past
> the problem? Or, is there some way to backup all of the data that can
> be accessed (through a single command or through programming)?
> Any help is appreciated!
> Gary Geniesse
> NeuroDimension, Inc.
>|||Thanks for the reply. I appreciate the help!
> Your best course of action is to restore from backup and apply transaction
> log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
> last resort to salvage data and with the understanding a significant about
> of data might be lost. I suggest you make copies of the database files
> before taking further corrective action.
Understood. Unfortunately, the first time you really need a backup is
when you discover your backup strategy is not adequate/not set up
properly. So yes, I was using REPAIR_ALLOW_DATA_LOSS with the
understanding that some data might be lost.
The odd thing is, I'm currently converting some software from ADODB to
ADO.NET. The ADO.NET routines keep exiting out due to "a logical
consistency-based I/O error: incorrect checksum", but the ADODB
routines seem to be running fine. Evidently ADODB or the old routines
are coded in such a way as to be not as sensitive to consistency
errors. Therefore, I'm not expecting excessive data loss, just a small
subset of relevant records to be hosed (if any).
> It looks like the on-disk data corruption that is too severe for normal DBCC
> processing. There is no technique can materialize data that no longer
> exists but, depending on the nature of the corruption, you might be able to
> salvage data by copying data to another database with the same schema. I
> suggest you don't use this database for any purpose other than salvage going
> forward.
Understood. If data is lost, it's lost. I'm not expecting DBCC to
recreate it. However, since a REPAIR_ALLOW_DATA_LOSS option was
available, it seemed reasonable that it could cleanse the database
enough to clear the error, even at the expense of mangling or deleting
the page with the bad checksum. I was definitely surprised that
CHECKDB returned messages like "internal error" and "transport error",
especially when the disk itself should no longer be returning errors.
It would seem like CHECKDB should be robust enough to handle this
case.
I'm currently trying Copy Database. If that fails, I'll try a more
granular approach. Thanks again for your help!
Gary Geniesse
NeuroDimension, Inc.

DBCC CHECKDB Internal Error

After a hiccup on my server computer, Windows ran CHKDSK on the next
reboot.
Since then, DBCC CHECKDB now returns the following:
--
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing.
Contact Customer Support Services.
--
And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
--
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The semaphore timeout
period has expired.)
--
Based on what I could dig up, both of these messages may be caused by
hardware errors (even though a repeated CHKDSK /R finds no additional
problems).
I have tried copying the database file and running DBCC CHECKDB
against the copy (in case a flaky sector is causing the problem).
However, this returned the same error.
Given that a hardware error may be the original cause of this problem
and given that I would like to avoid restoring to backup, is there
anything I can do to resolve the problem (other than call MS PSS)?
For example, is there some way to force DBCC CHECKDB to continue past
the problem? Or, is there some way to backup all of the data that can
be accessed (through a single command or through programming)?
Any help is appreciated!
Gary Geniesse
NeuroDimension, Inc.> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
CHKDSK can detect/correct only file system problems. It appears you have
corruption within database files (e.g. torn pages) due to the previous
hardware problem.

> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
Your best course of action is to restore from backup and apply transaction
log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
last resort to salvage data and with the understanding a significant about
of data might be lost. I suggest you make copies of the database files
before taking further corrective action.
It looks like the on-disk data corruption that is too severe for normal DBCC
processing. There is no technique can materialize data that no longer
exists but, depending on the nature of the corruption, you might be able to
salvage data by copying data to another database with the same schema. I
suggest you don't use this database for any purpose other than salvage going
forward.
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryGen" <gary@.garygen.com> wrote in message
news:1184966021.465560.312450@.w3g2000hsg.googlegroups.com...
> After a hiccup on my server computer, Windows ran CHKDSK on the next
> reboot.
> Since then, DBCC CHECKDB now returns the following:
> --
> Msg 8967, Level 16, State 216, Line 1
> An internal error occurred in DBCC that prevented further processing.
> Contact Customer Support Services.
> --
> And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
> --
> Msg 121, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - The semaphore timeout
> period has expired.)
> --
> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
> I have tried copying the database file and running DBCC CHECKDB
> against the copy (in case a flaky sector is causing the problem).
> However, this returned the same error.
> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
> For example, is there some way to force DBCC CHECKDB to continue past
> the problem? Or, is there some way to backup all of the data that can
> be accessed (through a single command or through programming)?
> Any help is appreciated!
> Gary Geniesse
> NeuroDimension, Inc.
>|||Thanks for the reply. I appreciate the help!

> Your best course of action is to restore from backup and apply transaction
> log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
> last resort to salvage data and with the understanding a significant about
> of data might be lost. I suggest you make copies of the database files
> before taking further corrective action.
Understood. Unfortunately, the first time you really need a backup is
when you discover your backup strategy is not adequate/not set up
properly. So yes, I was using REPAIR_ALLOW_DATA_LOSS with the
understanding that some data might be lost.
The odd thing is, I'm currently converting some software from ADODB to
ADO.NET. The ADO.NET routines keep exiting out due to "a logical
consistency-based I/O error: incorrect checksum", but the ADODB
routines seem to be running fine. Evidently ADODB or the old routines
are coded in such a way as to be not as sensitive to consistency
errors. Therefore, I'm not expecting excessive data loss, just a small
subset of relevant records to be hosed (if any).

> It looks like the on-disk data corruption that is too severe for normal DB
CC
> processing. There is no technique can materialize data that no longer
> exists but, depending on the nature of the corruption, you might be able t
o
> salvage data by copying data to another database with the same schema. I
> suggest you don't use this database for any purpose other than salvage goi
ng
> forward.
Understood. If data is lost, it's lost. I'm not expecting DBCC to
recreate it. However, since a REPAIR_ALLOW_DATA_LOSS option was
available, it seemed reasonable that it could cleanse the database
enough to clear the error, even at the expense of mangling or deleting
the page with the bad checksum. I was definitely surprised that
CHECKDB returned messages like "internal error" and "transport error",
especially when the disk itself should no longer be returning errors.
It would seem like CHECKDB should be robust enough to handle this
case.
I'm currently trying Copy Database. If that fails, I'll try a more
granular approach. Thanks again for your help!
Gary Geniesse
NeuroDimension, Inc.