DBfile usage: windows

Showing posts with label windows. Show all posts

Tuesday, March 27, 2012

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

I am running SQL Server 2000 SP3a on Windows 2003
I get the following error when I run DBCC SHRINKDATABASE
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I shrink the database using Enterprise Manager, I can shrink all the
files, including the log, except the MDF (PRIMARY) file.
I get the following error when I try to shrink this file:
Error 0 : This server has been connected.You must reconnect to perform this
operation.
I have re-booted the server , but get the same error messages.
I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
Has anyone any ideas how to resolve this problem?
--
DuncanJTry setting single-user mode.
DuncanJ wrote:
> I am running SQL Server 2000 SP3a on Windows 2003
> I get the following error when I run DBCC SHRINKDATABASE
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I shrink the database using Enterprise Manager, I can shrink all the
> files, including the log, except the MDF (PRIMARY) file.
> I get the following error when I try to shrink this file:
> Error 0 : This server has been connected.You must reconnect to perform this
> operation.
> I have re-booted the server , but get the same error messages.
> I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
> Has anyone any ideas how to resolve this problem?
> --
> DuncanJ

DBCC SHRINKDATABASE

SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
Dan
Hi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>
|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>

DBCC SHRINKDATABASE

SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>> SQL Server 2000 on Windows 2003
>> I want to shrink the Transaction Log file .
>> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
>> log file 2 (IRIS_Log) because all logical log files are in use.
>> What should I do?
>> Thanks,
>> Dan
>>
>

DBCC SHRINKDATABASE

SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>

Thursday, March 22, 2012

dbcc reindex issue - - I dont understand!

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Did you not already post this message before, or am i experiencing deja
vu?

Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%

With this scan density, defragmentation may be no be very useful.

> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177

I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.

*** Sent via Developersdex http://www.developersdex.com ***

Friday, February 17, 2012

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.

dbcc checkdb gets hung

I have a server running Windows 2003 with sql server 2000 8.00.973 hot fix
level that is hanging when I run dbcc checkdb on one particular database. It
keeps getting io and cpu time -- but just runs and run and runs.
Once when I looked at it there were 81 threads running for the DBCC's. It
was completely using up resources on the server. This is an 8 way processor.
We've let it run up to 11 hours before the server eventually had to be
rebooted.
The database it is hanging on is 250 Gb -- but I think it should still
finish in less than 11 hours. Usually we kill the process after 9 hours so
we can run our backups and let users start running their jobs again. We do
run this at night during our lowest usage.
Any suggestions?first check space in your tempdb with
dbcc checkdb (databasename) WITH ESTIMATEONLY
run CHECKDB when the system usage is low
and READ "DBCC CHECKDB Recommendations" i BOL
"DML" wrote:
> I have a server running Windows 2003 with sql server 2000 8.00.973 hot fix
> level that is hanging when I run dbcc checkdb on one particular database. It
> keeps getting io and cpu time -- but just runs and run and runs.
> Once when I looked at it there were 81 threads running for the DBCC's. It
> was completely using up resources on the server. This is an 8 way processor.
> We've let it run up to 11 hours before the server eventually had to be
> rebooted.
> The database it is hanging on is 250 Gb -- but I think it should still
> finish in less than 11 hours. Usually we kill the process after 9 hours so
> we can run our backups and let users start running their jobs again. We do
> run this at night during our lowest usage.
> Any suggestions?
>|||We do run CHECKDB at night during low usage, and run it in the same job as
backups so that they do not conflict. Disk backups are done in the morning
when we're sure our db backups are finished. Tempdb is on SAN space -- and
has over 2 Gb allocated to it with 10% growth set and more space available.
We run it with NO_INFOMSGS also. We run the same steps on our other 100
servers.
It just hangs in this 1 particular database. It is not the largest db we
have. This is also a new server we finished setting up in the last few
months and is one side of an Active Active cluster.
I ran the with estimate only and it only said I'd need around 2 Gb of tempdb
space.
Any other suggestions would be most helpful.
"Aleksandar Grbic" wrote:
> first check space in your tempdb with
> dbcc checkdb (databasename) WITH ESTIMATEONLY
> run CHECKDB when the system usage is low
>
> and READ "DBCC CHECKDB Recommendations" i BOL
>
> "DML" wrote:
> > I have a server running Windows 2003 with sql server 2000 8.00.973 hot fix
> > level that is hanging when I run dbcc checkdb on one particular database. It
> > keeps getting io and cpu time -- but just runs and run and runs.
> >
> > Once when I looked at it there were 81 threads running for the DBCC's. It
> > was completely using up resources on the server. This is an 8 way processor.
> >
> > We've let it run up to 11 hours before the server eventually had to be
> > rebooted.
> >
> > The database it is hanging on is 250 Gb -- but I think it should still
> > finish in less than 11 hours. Usually we kill the process after 9 hours so
> > we can run our backups and let users start running their jobs again. We do
> > run this at night during our lowest usage.
> >
> > Any suggestions?
> >
> >|||Let it finish. Some things to consider:
1) what are the disk queue lengths on the drives holding the database? (i.e
is your IO subsystem the bottleneck)
2) does it complete a lot faster if you use the NOINDEX option? (See BOL).
If so, you've probably got a corruption somewhere in a non-clustered index
which is triggering a much more expensive set of checks to find the exact
row with the corruption in. In which case, remove the NOINDEX option and let
it complete so you know where the corruption is.
Number 2 is my bet.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"DML" <DML@.discussions.microsoft.com> wrote in message
news:81778FD5-BEC9-4D52-8A71-14396E6F187F@.microsoft.com...
> We do run CHECKDB at night during low usage, and run it in the same job as
> backups so that they do not conflict. Disk backups are done in the
morning
> when we're sure our db backups are finished. Tempdb is on SAN space --
and
> has over 2 Gb allocated to it with 10% growth set and more space
available.
> We run it with NO_INFOMSGS also. We run the same steps on our other 100
> servers.
> It just hangs in this 1 particular database. It is not the largest db we
> have. This is also a new server we finished setting up in the last few
> months and is one side of an Active Active cluster.
> I ran the with estimate only and it only said I'd need around 2 Gb of
tempdb
> space.
> Any other suggestions would be most helpful.
> "Aleksandar Grbic" wrote:
> > first check space in your tempdb with
> > dbcc checkdb (databasename) WITH ESTIMATEONLY
> >
> > run CHECKDB when the system usage is low
> >
> >
> > and READ "DBCC CHECKDB Recommendations" i BOL
> >
> >
> > "DML" wrote:
> >
> > > I have a server running Windows 2003 with sql server 2000 8.00.973 hot
fix
> > > level that is hanging when I run dbcc checkdb on one particular
database. It
> > > keeps getting io and cpu time -- but just runs and run and runs.
> > >
> > > Once when I looked at it there were 81 threads running for the DBCC's.
It
> > > was completely using up resources on the server. This is an 8 way
processor.
> > >
> > > We've let it run up to 11 hours before the server eventually had to be
> > > rebooted.
> > >
> > > The database it is hanging on is 250 Gb -- but I think it should still
> > > finish in less than 11 hours. Usually we kill the process after 9
hours so
> > > we can run our backups and let users start running their jobs again.
We do
> > > run this at night during our lowest usage.
> > >
> > > Any suggestions?
> > >
> > >|||We were able to track down error messages regarding 2 tables in the database.
We ran DBCC Checktable against both tables. One table came back and the
other hung. On the table that hung (357 million rows) we dropped/recreated
the indexes and this seemed to fix the problem.
Thanks for your help.
"Paul S Randal [MS]" wrote:
> Let it finish. Some things to consider:
> 1) what are the disk queue lengths on the drives holding the database? (i.e
> is your IO subsystem the bottleneck)
> 2) does it complete a lot faster if you use the NOINDEX option? (See BOL).
> If so, you've probably got a corruption somewhere in a non-clustered index
> which is triggering a much more expensive set of checks to find the exact
> row with the corruption in. In which case, remove the NOINDEX option and let
> it complete so you know where the corruption is.
> Number 2 is my bet.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "DML" <DML@.discussions.microsoft.com> wrote in message
> news:81778FD5-BEC9-4D52-8A71-14396E6F187F@.microsoft.com...
> > We do run CHECKDB at night during low usage, and run it in the same job as
> > backups so that they do not conflict. Disk backups are done in the
> morning
> > when we're sure our db backups are finished. Tempdb is on SAN space --
> and
> > has over 2 Gb allocated to it with 10% growth set and more space
> available.
> > We run it with NO_INFOMSGS also. We run the same steps on our other 100
> > servers.
> >
> > It just hangs in this 1 particular database. It is not the largest db we
> > have. This is also a new server we finished setting up in the last few
> > months and is one side of an Active Active cluster.
> >
> > I ran the with estimate only and it only said I'd need around 2 Gb of
> tempdb
> > space.
> >
> > Any other suggestions would be most helpful.
> >
> > "Aleksandar Grbic" wrote:
> >
> > > first check space in your tempdb with
> > > dbcc checkdb (databasename) WITH ESTIMATEONLY
> > >
> > > run CHECKDB when the system usage is low
> > >
> > >
> > > and READ "DBCC CHECKDB Recommendations" i BOL
> > >
> > >
> > > "DML" wrote:
> > >
> > > > I have a server running Windows 2003 with sql server 2000 8.00.973 hot
> fix
> > > > level that is hanging when I run dbcc checkdb on one particular
> database. It
> > > > keeps getting io and cpu time -- but just runs and run and runs.
> > > >
> > > > Once when I looked at it there were 81 threads running for the DBCC's.
> It
> > > > was completely using up resources on the server. This is an 8 way
> processor.
> > > >
> > > > We've let it run up to 11 hours before the server eventually had to be
> > > > rebooted.
> > > >
> > > > The database it is hanging on is 250 Gb -- but I think it should still
> > > > finish in less than 11 hours. Usually we kill the process after 9
> hours so
> > > > we can run our backups and let users start running their jobs again.
> We do
> > > > run this at night during our lowest usage.
> > > >
> > > > Any suggestions?
> > > >
> > > >
>
>

Tuesday, March 27, 2012

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

Thursday, March 22, 2012

dbcc reindex issue - - I dont understand!

Friday, February 17, 2012

DBCC CHECKDB Internal Error

DBCC CHECKDB Internal Error

DBCC CHECKDB Internal Error

dbcc checkdb gets hung

dbcc checkdb gets hung

dbcc checkdb gets hung

DBfile usage

Blog Archive

About Me