error: Attempt to fetch logical page

Hi,
During an upgrade from sql server 6.5 to sql server 2000 I receive the
follwoing error and the upgrade process remains stuck until terminated
manually:
ODBC error 605 (HY000) Attempt to fetch logical page 1430280 in database
'nice_cls' belongs to object 'NICE_CLS_CALLS_0008', not to object
'NICE_CLS_CALLS_0007'.
I tried using dbcc checkdb and received the same error.
Is there any idea how to address this situation or how to delete the
specific page?
Thanks,
YanivHi Yaniv,
This error occurs when Microsoft SQL Server detects database corruption.
The second object specified in the text not to object '%.*ls' is probably
corrupt. Because this error can mask the existence of other errors, execute
DBCC CHECKDB to determine the extent of the damage.
I am assuming that the corruption is happening on your SQL Server 6.5
server. The best way to recover from corruption is to restore from your
good backup.
I hope this helps !!!
Thanks,
Fargham
Microsoft - SQL Server

error: Attempt to fetch logical page

Hi,
During an upgrade from sql server 6.5 to sql server 2000 I receive the
follwoing error and the upgrade process remains stuck until terminated
manually:
ODBC error 605 (HY000) Attempt to fetch logical page 1430280 in database
'nice_cls' belongs to object 'NICE_CLS_CALLS_0008', not to object
'NICE_CLS_CALLS_0007'.
I tried using dbcc checkdb and received the same error.
Is there any idea how to address this situation or how to delete the
specific page?
Thanks,
YanivHi Yaniv,
This error occurs when Microsoft® SQL Server detects database corruption.
The second object specified in the text not to object '%.*ls' is probably
corrupt. Because this error can mask the existence of other errors, execute
DBCC CHECKDB to determine the extent of the damage.
I am assuming that the corruption is happening on your SQL Server 6.5
server. The best way to recover from corruption is to restore from your
good backup.
I hope this helps !!!
Thanks,
Fargham
Microsoft - SQL Server

error: Attempt to fetch logical page

Hi,
During an upgrade from sql server 6.5 to sql server 2000 I receive the
follwoing error and the upgrade process remains stuck until terminated
manually:
ODBC error 605 (HY000) Attempt to fetch logical page 1430280 in database
'nice_cls' belongs to object 'NICE_CLS_CALLS_0008', not to object
'NICE_CLS_CALLS_0007'.
I tried using dbcc checkdb and received the same error.
Is there any idea how to address this situation or how to delete the
specific page?
Thanks,
Yaniv
Hi Yaniv,
This error occurs when Microsoft SQL Server detects database corruption.
The second object specified in the text not to object '%.*ls' is probably
corrupt. Because this error can mask the existence of other errors, execute
DBCC CHECKDB to determine the extent of the damage.
I am assuming that the corruption is happening on your SQL Server 6.5
server. The best way to recover from corruption is to restore from your
good backup.
I hope this helps !!!
Thanks,
Fargham
Microsoft - SQL Server

Error: 823 - Torn page - Reposting for Attention

Hi,
I am reposting my problem freshly , previously i posted it
as a part of existing thread. Sorry for Inconvience.
I had a server break down with two harddisks down on RAID
5. Was able to reocover from that situation. When I was
going through the event viewer I found the below error (
the time stamp is after two hours of my server
recovery,when a Maintanence Plan starts). I am not finding
msdb marked suspect and everything seems working normally.
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset
0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\msdbdata.mdf'.
following the above i found a warning msg
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance
Plan 'DB Maintenance Plan1''
(0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
Invoked on: 2003-08-25 18:00:00 - Message: The job
failed. The Job was invoked by Schedule 5 (Schedule 1).
The last step to run was step 1 (Step 1).
on Executing DDBC CHECKDB
partial log
--
There are 0 rows in 0 pages for
object 'RTblEnumerationValueDef'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2069582411, index ID 2: Page (1:1569) could not
be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 2, page
(1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Last Lines
--
CHECKDB found 0 allocation errors and 2 consistency errors
in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the
errors found by DBCC CHECKDB (msdb ).
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I dont have the backup of msdb and when i try to backup
the existing one it gives torn page error.
Is this happend because of Database Maintanence plan which
was scheduled exactly few minutes before at the time of
error msg. I am just using backup option of user databases
and nothing else in that plan . (oR) some data missing coz
of system breakdown
Any help of this will be greatly appreciated.
Best Regards
ThirumalThirumal
Well,I think you have onto Maintenance Plan check box 'Attempt to repaire
any minor problems' turned on ,so you need turn off the checkbox.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
> Hi,
> I am reposting my problem freshly , previously i posted it
> as a part of existing thread. Sorry for Inconvience.
>
> I had a server break down with two harddisks down on RAID
> 5. Was able to reocover from that situation. When I was
> going through the event viewer I found the below error (
> the time stamp is after two hours of my server
> recovery,when a Maintanence Plan starts). I am not finding
> msdb marked suspect and everything seems working normally.
> Error: 823, Severity: 24, State: 2
> I/O error (torn page) detected during read at offset
> 0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
> Server\MSSQL\data\msdbdata.mdf'.
> following the above i found a warning msg
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan1''
> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
> Invoked on: 2003-08-25 18:00:00 - Message: The job
> failed. The Job was invoked by Schedule 5 (Schedule 1).
> The last step to run was step 1 (Step 1).
> on Executing DDBC CHECKDB
> partial log
> --
> There are 0 rows in 0 pages for
> object 'RTblEnumerationValueDef'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2069582411, index ID 2: Page (1:1569) could not
> be processed. See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 2069582411, index ID 2, page
> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >berrcode) failed. Values are 2057 and -1.
> Last Lines
> --
> CHECKDB found 0 allocation errors and 2 consistency errors
> in database 'msdb'.
> repair_allow_data_loss is the minimum repair level for the
> errors found by DBCC CHECKDB (msdb ).
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I dont have the backup of msdb and when i try to backup
> the existing one it gives torn page error.
> Is this happend because of Database Maintanence plan which
> was scheduled exactly few minutes before at the time of
> error msg. I am just using backup option of user databases
> and nothing else in that plan . (oR) some data missing coz
> of system breakdown
> Any help of this will be greatly appreciated.
> Best Regards
> Thirumal
>|||Hi Dimant,
Thanks for the reply. I dont have any system databases in
my maintenance plan and in fact i dint enabled integrity
check for my user databses .
Regards
Thirumal
>--Original Message--
>Thirumal
>Well,I think you have onto Maintenance Plan check
box 'Attempt to repaire
>any minor problems' turned on ,so you need turn off the
checkbox.
>
>
>"Thirumal" <treddym@.hotmail.com> wrote in message
>news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
>> Hi,
>> I am reposting my problem freshly , previously i posted
it
>> as a part of existing thread. Sorry for Inconvience.
>>
>> I had a server break down with two harddisks down on
RAID
>> 5. Was able to reocover from that situation. When I was
>> going through the event viewer I found the below error (
>> the time stamp is after two hours of my server
>> recovery,when a Maintanence Plan starts). I am not
finding
>> msdb marked suspect and everything seems working
normally.
>> Error: 823, Severity: 24, State: 2
>> I/O error (torn page) detected during read at offset
>> 0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\data\msdbdata.mdf'.
>> following the above i found a warning msg
>> SQL Server Scheduled Job 'DB Backup Job for DB
Maintenance
>> Plan 'DB Maintenance Plan1''
>> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
>> Invoked on: 2003-08-25 18:00:00 - Message: The job
>> failed. The Job was invoked by Schedule 5 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> on Executing DDBC CHECKDB
>> partial log
>> --
>> There are 0 rows in 0 pages for
>> object 'RTblEnumerationValueDef'.
>> Server: Msg 8928, Level 16, State 1, Line 1
>> Object ID 2069582411, index ID 2: Page (1:1569) could
not
>> be processed. See other errors for details.
>> Server: Msg 8939, Level 16, State 1, Line 1
>> Table error: Object ID 2069582411, index ID 2, page
>> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>> >berrcode) failed. Values are 2057 and -1.
>> Last Lines
>> --
>> CHECKDB found 0 allocation errors and 2 consistency
errors
>> in database 'msdb'.
>> repair_allow_data_loss is the minimum repair level for
the
>> errors found by DBCC CHECKDB (msdb ).
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> I dont have the backup of msdb and when i try to backup
>> the existing one it gives torn page error.
>> Is this happend because of Database Maintanence plan
which
>> was scheduled exactly few minutes before at the time of
>> error msg. I am just using backup option of user
databases
>> and nothing else in that plan . (oR) some data missing
coz
>> of system breakdown
>> Any help of this will be greatly appreciated.
>> Best Regards
>> Thirumal
>
>.
>|||Ok
Do you have a last backup of MSDB database? If you do try to restore it ,
else you need to reser the suspect status.
UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = @.dbname
You must reboot SQL Server prior to accessing this database.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:024501c36ba5$eea87860$a001280a@.phx.gbl...
> Hi Dimant,
> Thanks for the reply. I dont have any system databases in
> my maintenance plan and in fact i dint enabled integrity
> check for my user databses .
> Regards
> Thirumal
> >--Original Message--
> >Thirumal
> >Well,I think you have onto Maintenance Plan check
> box 'Attempt to repaire
> >any minor problems' turned on ,so you need turn off the
> checkbox.
> >
> >
> >
> >
> >
> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> I am reposting my problem freshly , previously i posted
> it
> >> as a part of existing thread. Sorry for Inconvience.
> >>
> >>
> >> I had a server break down with two harddisks down on
> RAID
> >> 5. Was able to reocover from that situation. When I was
> >> going through the event viewer I found the below error (
> >> the time stamp is after two hours of my server
> >> recovery,when a Maintanence Plan starts). I am not
> finding
> >> msdb marked suspect and everything seems working
> normally.
> >>
> >> Error: 823, Severity: 24, State: 2
> >> I/O error (torn page) detected during read at offset
> >> 0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
> >> Server\MSSQL\data\msdbdata.mdf'.
> >>
> >> following the above i found a warning msg
> >>
> >> SQL Server Scheduled Job 'DB Backup Job for DB
> Maintenance
> >> Plan 'DB Maintenance Plan1''
> >> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
> >> Invoked on: 2003-08-25 18:00:00 - Message: The job
> >> failed. The Job was invoked by Schedule 5 (Schedule 1).
> >> The last step to run was step 1 (Step 1).
> >>
> >> on Executing DDBC CHECKDB
> >>
> >> partial log
> >> --
> >> There are 0 rows in 0 pages for
> >> object 'RTblEnumerationValueDef'.
> >> Server: Msg 8928, Level 16, State 1, Line 1
> >> Object ID 2069582411, index ID 2: Page (1:1569) could
> not
> >> be processed. See other errors for details.
> >> Server: Msg 8939, Level 16, State 1, Line 1
> >> Table error: Object ID 2069582411, index ID 2, page
> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >> >berrcode) failed. Values are 2057 and -1.
> >>
> >> Last Lines
> >> --
> >>
> >> CHECKDB found 0 allocation errors and 2 consistency
> errors
> >> in database 'msdb'.
> >> repair_allow_data_loss is the minimum repair level for
> the
> >> errors found by DBCC CHECKDB (msdb ).
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> I dont have the backup of msdb and when i try to backup
> >> the existing one it gives torn page error.
> >>
> >> Is this happend because of Database Maintanence plan
> which
> >> was scheduled exactly few minutes before at the time of
> >> error msg. I am just using backup option of user
> databases
> >> and nothing else in that plan . (oR) some data missing
> coz
> >> of system breakdown
> >>
> >> Any help of this will be greatly appreciated.
> >>
> >> Best Regards
> >>
> >> Thirumal
> >>
> >
> >
> >.
> >|||Hi Dimant,
I dont have the backup of msdb. BTW as i mentioned in my
previous posts msdb is not marked suspect. Do i still need
to use reset queries?
Thanks
Thirumal
>--Original Message--
>Ok
>Do you have a last backup of MSDB database? If you do try
to restore it ,
>else you need to reser the suspect status.
>UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = @.dbname
>You must reboot SQL Server prior to accessing this
database.
>
>
>"Thirumal" <treddym@.hotmail.com> wrote in message
>news:024501c36ba5$eea87860$a001280a@.phx.gbl...
>> Hi Dimant,
>> Thanks for the reply. I dont have any system databases
in
>> my maintenance plan and in fact i dint enabled integrity
>> check for my user databses .
>> Regards
>> Thirumal
>> >--Original Message--
>> >Thirumal
>> >Well,I think you have onto Maintenance Plan check
>> box 'Attempt to repaire
>> >any minor problems' turned on ,so you need turn off the
>> checkbox.
>> >
>> >
>> >
>> >
>> >
>> >"Thirumal" <treddym@.hotmail.com> wrote in message
>> >news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I am reposting my problem freshly , previously i
posted
>> it
>> >> as a part of existing thread. Sorry for Inconvience.
>> >>
>> >>
>> >> I had a server break down with two harddisks down on
>> RAID
>> >> 5. Was able to reocover from that situation. When I
was
>> >> going through the event viewer I found the below
error (
>> >> the time stamp is after two hours of my server
>> >> recovery,when a Maintanence Plan starts). I am not
>> finding
>> >> msdb marked suspect and everything seems working
>> normally.
>> >>
>> >> Error: 823, Severity: 24, State: 2
>> >> I/O error (torn page) detected during read at offset
>> >> 0x00000000c42000 in file 'd:\Program Files\Microsoft
SQL
>> >> Server\MSSQL\data\msdbdata.mdf'.
>> >>
>> >> following the above i found a warning msg
>> >>
>> >> SQL Server Scheduled Job 'DB Backup Job for DB
>> Maintenance
>> >> Plan 'DB Maintenance Plan1''
>> >> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status:
Failed -
>> >> Invoked on: 2003-08-25 18:00:00 - Message: The job
>> >> failed. The Job was invoked by Schedule 5 (Schedule
1).
>> >> The last step to run was step 1 (Step 1).
>> >>
>> >> on Executing DDBC CHECKDB
>> >>
>> >> partial log
>> >> --
>> >> There are 0 rows in 0 pages for
>> >> object 'RTblEnumerationValueDef'.
>> >> Server: Msg 8928, Level 16, State 1, Line 1
>> >> Object ID 2069582411, index ID 2: Page (1:1569) could
>> not
>> >> be processed. See other errors for details.
>> >> Server: Msg 8939, Level 16, State 1, Line 1
>> >> Table error: Object ID 2069582411, index ID 2, page
>> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>> >> >berrcode) failed. Values are 2057 and -1.
>> >>
>> >> Last Lines
>> >> --
>> >>
>> >> CHECKDB found 0 allocation errors and 2 consistency
>> errors
>> >> in database 'msdb'.
>> >> repair_allow_data_loss is the minimum repair level
for
>> the
>> >> errors found by DBCC CHECKDB (msdb ).
>> >> DBCC execution completed. If DBCC printed error
>> messages,
>> >> contact your system administrator.
>> >>
>> >> I dont have the backup of msdb and when i try to
backup
>> >> the existing one it gives torn page error.
>> >>
>> >> Is this happend because of Database Maintanence plan
>> which
>> >> was scheduled exactly few minutes before at the time
of
>> >> error msg. I am just using backup option of user
>> databases
>> >> and nothing else in that plan . (oR) some data
missing
>> coz
>> >> of system breakdown
>> >>
>> >> Any help of this will be greatly appreciated.
>> >>
>> >> Best Regards
>> >>
>> >> Thirumal
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||You should open a case with Product support to resolve this in the best
way. From the error it looks as though there was a write error on the disk
prior to the last shutdown of the database.
Here is how books online describes torn pages:
This recovery option allows SQL Server to detect incomplete I/O operations
caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte
sector in an 8-kilobyte (KB) database page when the page is written to
disk. If a bit is in the wrong state when the page is later read by SQL
Server, the page was written incorrectly; a torn page is detected. Torn
pages are usually detected during recovery because any page that was
written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations
using a 512-byte sector. Therefore, 16 sectors are written per database
page. A torn page can occur if the system fails (for example, due to power
failure) between the time the operating system writes the first 512-byte
sector to disk and the completion of the 8-KB I/O operation. If the first
sector of a database page is successfully written before the failure, the
database page on disk will appear as updated, although it may not have
succeeded.
Note Using battery-backed disk caches can ensure that data is successfully
written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is
killed. If the torn page is detected during recovery, the database is also
marked suspect. The database backup should be restored, and any transaction
log backups applied, because it is physically inconsistent.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:0b0401c36bb2$d1845fd0$a301280a@.phx.gbl...
> Hi Dimant,
> I dont have the backup of msdb. BTW as i mentioned in my
> previous posts msdb is not marked suspect. Do i still need
> to use reset queries?
> Thanks
> Thirumal
> >--Original Message--
> >Ok
> >Do you have a last backup of MSDB database? If you do try
> to restore it ,
> >else you need to reser the suspect status.
> >UPDATE master..sysdatabases SET status = status ^ 256
> WHERE name = @.dbname
> >You must reboot SQL Server prior to accessing this
> database.
> >
> >
> >
> >
> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >news:024501c36ba5$eea87860$a001280a@.phx.gbl...
> >> Hi Dimant,
> >>
> >> Thanks for the reply. I dont have any system databases
> in
> >> my maintenance plan and in fact i dint enabled integrity
> >> check for my user databses .
> >>
> >> Regards
> >>
> >> Thirumal
> >> >--Original Message--
> >> >Thirumal
> >> >Well,I think you have onto Maintenance Plan check
> >> box 'Attempt to repaire
> >> >any minor problems' turned on ,so you need turn off the
> >> checkbox.
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >> >news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> I am reposting my problem freshly , previously i
> posted
> >> it
> >> >> as a part of existing thread. Sorry for Inconvience.
> >> >>
> >> >>
> >> >> I had a server break down with two harddisks down on
> >> RAID
> >> >> 5. Was able to reocover from that situation. When I
> was
> >> >> going through the event viewer I found the below
> error (
> >> >> the time stamp is after two hours of my server
> >> >> recovery,when a Maintanence Plan starts). I am not
> >> finding
> >> >> msdb marked suspect and everything seems working
> >> normally.
> >> >>
> >> >> Error: 823, Severity: 24, State: 2
> >> >> I/O error (torn page) detected during read at offset
> >> >> 0x00000000c42000 in file 'd:\Program Files\Microsoft
> SQL
> >> >> Server\MSSQL\data\msdbdata.mdf'.
> >> >>
> >> >> following the above i found a warning msg
> >> >>
> >> >> SQL Server Scheduled Job 'DB Backup Job for DB
> >> Maintenance
> >> >> Plan 'DB Maintenance Plan1''
> >> >> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status:
> Failed -
> >> >> Invoked on: 2003-08-25 18:00:00 - Message: The job
> >> >> failed. The Job was invoked by Schedule 5 (Schedule
> 1).
> >> >> The last step to run was step 1 (Step 1).
> >> >>
> >> >> on Executing DDBC CHECKDB
> >> >>
> >> >> partial log
> >> >> --
> >> >> There are 0 rows in 0 pages for
> >> >> object 'RTblEnumerationValueDef'.
> >> >> Server: Msg 8928, Level 16, State 1, Line 1
> >> >> Object ID 2069582411, index ID 2: Page (1:1569) could
> >> not
> >> >> be processed. See other errors for details.
> >> >> Server: Msg 8939, Level 16, State 1, Line 1
> >> >> Table error: Object ID 2069582411, index ID 2, page
> >> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >> >> >berrcode) failed. Values are 2057 and -1.
> >> >>
> >> >> Last Lines
> >> >> --
> >> >>
> >> >> CHECKDB found 0 allocation errors and 2 consistency
> >> errors
> >> >> in database 'msdb'.
> >> >> repair_allow_data_loss is the minimum repair level
> for
> >> the
> >> >> errors found by DBCC CHECKDB (msdb ).
> >> >> DBCC execution completed. If DBCC printed error
> >> messages,
> >> >> contact your system administrator.
> >> >>
> >> >> I dont have the backup of msdb and when i try to
> backup
> >> >> the existing one it gives torn page error.
> >> >>
> >> >> Is this happend because of Database Maintanence plan
> >> which
> >> >> was scheduled exactly few minutes before at the time
> of
> >> >> error msg. I am just using backup option of user
> >> databases
> >> >> and nothing else in that plan . (oR) some data
> missing
> >> coz
> >> >> of system breakdown
> >> >>
> >> >> Any help of this will be greatly appreciated.
> >> >>
> >> >> Best Regards
> >> >>
> >> >> Thirumal
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hi Jacco,
Thanks for the help. I have executed DBCC CHECKDB('msdb',
REPAIR_REBUILD)in single user mode and finding errors with
two tables at SQL output.
DBCC results for 'RTblVersions'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2069582411, index ID 2: Page (1:1569) could not
be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 2, page
(1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 2. Page
(1:1569) was not seen in the scan although its parent
(1:1370) and previous (1:1487) refer to it. Check any
previous errors.
There are 2333 rows in 27 pages for object 'RTblVersions'.
--
DBCC results for 'backupmediafamily'.
The repair level on the DBCC statement caused this
repair to be bypassed.
The repair level on the DBCC statement caused this
repair to be bypassed.
The repair level on the DBCC statement caused this
repair to be bypassed.
There are 580 rows in 11 pages for
object 'backupmediafamily'.
CHECKDB found 0 allocation errors and 3 consistency errors
in table 'backupmediafamily' (object ID 2069582411).
I am running only two user databases one is around 1GB and
the other in MB's . I am using Database maintainance
plans - Backup option. I am aware that Information needed
by the SQL Server Agent is stored in the msdb database,
for example, alerts and jobs and tear in msdb wont affect
user databases.
Any help to come out of this situation will be highly
regarded as i dont have msdb backups. Since I am new to
DBA works I would also request you to guide me for any
articles or ur suggestions on what all the databases needs
to be backup on regular basis.
Best Regards
Thirumal
>--Original Message--
>Hi,
>You can speed up the SET SINGLE_USER statement by adding
WITH ROLLBACK
>IMMEDIATE. This will roll back any open transaction in
the database and
>terminate all the connections.Without rollback immediate
SQL Server will
>just wait until all transactions have been committed or
rolled back.
>You can set the database back to normal with ALTER
DATABASE msdb SET
>MULTI_USER
>
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>|||Hi Thirumal,
The error that are reported for RTblVersions indicate that there is a
problem with a non-clustered index, which you can safely correct with DBBC
CHECKTABLE('RTblVersions' , REPAIR_ALLOW_DATA_LOSS).
I would also run DBBC CHECKTABLE('backupmediafamily',
REPAIR_ALLOW_DATA_LOSS).
You might possibly lose some data there, but the only information that is in
that table is where (tape or file locations) old backups have been backed up
to, something you can do without. It is only used to present you with a list
of backups to restore in the restore dialog in Enterprise Manager, but you
can still restore the backups if you can rememeber the filename.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:00cc01c36bea$bc194d80$a301280a@.phx.gbl...
> Hi Jacco,
> Thanks for the help. I have executed DBCC CHECKDB('msdb',
> REPAIR_REBUILD)in single user mode and finding errors with
> two tables at SQL output.
> DBCC results for 'RTblVersions'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2069582411, index ID 2: Page (1:1569) could not
> be processed. See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 2069582411, index ID 2, page
> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >berrcode) failed. Values are 2057 and -1.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 2069582411, index ID 2. Page
> (1:1569) was not seen in the scan although its parent
> (1:1370) and previous (1:1487) refer to it. Check any
> previous errors.
> There are 2333 rows in 27 pages for object 'RTblVersions'.
> --
> DBCC results for 'backupmediafamily'.
> The repair level on the DBCC statement caused this
> repair to be bypassed.
> The repair level on the DBCC statement caused this
> repair to be bypassed.
> The repair level on the DBCC statement caused this
> repair to be bypassed.
> There are 580 rows in 11 pages for
> object 'backupmediafamily'.
> CHECKDB found 0 allocation errors and 3 consistency errors
> in table 'backupmediafamily' (object ID 2069582411).
> I am running only two user databases one is around 1GB and
> the other in MB's . I am using Database maintainance
> plans - Backup option. I am aware that Information needed
> by the SQL Server Agent is stored in the msdb database,
> for example, alerts and jobs and tear in msdb wont affect
> user databases.
> Any help to come out of this situation will be highly
> regarded as i dont have msdb backups. Since I am new to
> DBA works I would also request you to guide me for any
> articles or ur suggestions on what all the databases needs
> to be backup on regular basis.
> Best Regards
> Thirumal
>
> >--Original Message--
> >Hi,
> >
> >You can speed up the SET SINGLE_USER statement by adding
> WITH ROLLBACK
> >IMMEDIATE. This will roll back any open transaction in
> the database and
> >terminate all the connections.Without rollback immediate
> SQL Server will
> >just wait until all transactions have been committed or
> rolled back.
> >
> >You can set the database back to normal with ALTER
> DATABASE msdb SET
> >MULTI_USER
> >
> >
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
>|||> I would like to hear from your experience on,
> what are the possible cases for these type of torn page
> erros on production boxes.
Power failure in the middle of writing a page. Hence the name "torn page". If you have HW write
caching *and proper battery backup* then the battery backup should make sure that the OS never get a
pertially completed I/O operation.
> Any suggestions & precautions to newbie's to excel as SQL
> DBA's from ur end would be very helpful.
Another option for you could have been to script out the jobs, alerts etc definitions and rebuild
the MSDB database (search KB for how to rebuild). And, of course, do regular backup of all databases
except tempdb, pubs and northwind from now on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Thirumal" <treddym@.hotmail.com> wrote in message news:004901c36bfc$86327450$a601280a@.phx.gbl...
> Hi Jacoo,
> Thanks a lot for helping this newbie. I have executed the
> commands on both the tables and to cross check i have run
> DBCC CHECKB and found found 0 allocation errors and 0
> consistency errors. ariseThe Maintenance Plan is working
> fine now. I would like to hear from your experience on,
> what are the possible cases for these type of torn page
> erros on production boxes.
> Any suggestions & precautions to newbie's to excel as SQL
> DBA's from ur end would be very helpful.
> Thanks & Best Regards
> Thirumal
> >--Original Message--
> >Hi Thirumal,
> >
> >The error that are reported for RTblVersions indicate
> that there is a
> >problem with a non-clustered index, which you can safely
> correct with DBBC
> >CHECKTABLE('RTblVersions' , REPAIR_ALLOW_DATA_LOSS).
> >
> >I would also run DBBC CHECKTABLE('backupmediafamily',
> >REPAIR_ALLOW_DATA_LOSS).
> >You might possibly lose some data there, but the only
> information that is in
> >that table is where (tape or file locations) old backups
> have been backed up
> >to, something you can do without. It is only used to
> present you with a list
> >of backups to restore in the restore dialog in Enterprise
> Manager, but you
> >can still restore the backups if you can rememeber the
> filename.
> >
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >news:00cc01c36bea$bc194d80$a301280a@.phx.gbl...
> >> Hi Jacco,
> >>
> >> Thanks for the help. I have executed DBCC CHECKDB
> ('msdb',
> >> REPAIR_REBUILD)in single user mode and finding errors
> with
> >> two tables at SQL output.
> >>
> >> DBCC results for 'RTblVersions'.
> >> Server: Msg 8928, Level 16, State 1, Line 1
> >> Object ID 2069582411, index ID 2: Page (1:1569) could
> not
> >> be processed. See other errors for details.
> >> Server: Msg 8939, Level 16, State 1, Line 1
> >> Table error: Object ID 2069582411, index ID 2, page
> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >> >berrcode) failed. Values are 2057 and -1.
> >> Server: Msg 8976, Level 16, State 1, Line 1
> >> Table error: Object ID 2069582411, index ID 2. Page
> >> (1:1569) was not seen in the scan although its parent
> >> (1:1370) and previous (1:1487) refer to it. Check any
> >> previous errors.
> >> There are 2333 rows in 27 pages for
> object 'RTblVersions'.
> >> --
> >> DBCC results for 'backupmediafamily'.
> >> The repair level on the DBCC statement caused
> this
> >> repair to be bypassed.
> >> The repair level on the DBCC statement caused
> this
> >> repair to be bypassed.
> >> The repair level on the DBCC statement caused
> this
> >> repair to be bypassed.
> >> There are 580 rows in 11 pages for
> >> object 'backupmediafamily'.
> >> CHECKDB found 0 allocation errors and 3 consistency
> errors
> >> in table 'backupmediafamily' (object ID 2069582411).
> >>
> >> I am running only two user databases one is around 1GB
> and
> >> the other in MB's . I am using Database maintainance
> >> plans - Backup option. I am aware that Information
> needed
> >> by the SQL Server Agent is stored in the msdb database,
> >> for example, alerts and jobs and tear in msdb wont
> affect
> >> user databases.
> >>
> >> Any help to come out of this situation will be highly
> >> regarded as i dont have msdb backups. Since I am new to
> >> DBA works I would also request you to guide me for any
> >> articles or ur suggestions on what all the databases
> needs
> >> to be backup on regular basis.
> >>
> >> Best Regards
> >>
> >> Thirumal
> >>
> >>
> >>
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >You can speed up the SET SINGLE_USER statement by
> adding
> >> WITH ROLLBACK
> >> >IMMEDIATE. This will roll back any open transaction in
> >> the database and
> >> >terminate all the connections.Without rollback
> immediate
> >> SQL Server will
> >> >just wait until all transactions have been committed or
> >> rolled back.
> >> >
> >> >You can set the database back to normal with ALTER
> >> DATABASE msdb SET
> >> >MULTI_USER
> >> >
> >> >
> >> >
> >> >--
> >> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >> >Database Administrator
> >> >Eurostop Ltd.
> >> >
> >> >
> >>
> >
> >
> >.
> >

Error: 823

Hi,

I am I notice this message on the event logs.

Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x000003182da000 in file 'e:\sqlData\FSDocsOnline.mdf'.

If I was to run:

DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]

Will it fix my problem or will I need to restore the database. Which have no backup.

LystraYou might be able to fix the database, but I'd be more comfortable with either restoring a backup, or better yet trying to copy the data out one table at a time (saving the backup for a last resort).

A lot depends on just what is broken and how it broke. I don't have a good way to guess what caused the problem, so I'd assume that it will be tough to fix.

-PatP|||Well there is no Backup for this database. The last time it was backup was in 10/03. And the files have since been deleted.

In order to copy the time out one at a time what would this accomplish. And to do this should I use the bCP out method?

Thanking you in advance.

Lystra|||Brett would say yes to the BCP part.|||The method you use for the copy isn't really significant. You can BCP to flat files, or you can just use an INSERT if both databases are on the same server, or DTS if they are on different servers.

The trick is to copy data until the copy fails (due to the problem with the disk), then find a way to resume the copy just past the problem. The easiest way to do this is using PK values to copy what you can, then move a few PK values higher and start again. If there was only one flaw, then you can copy from the low PK values up, then from the high PK values down and you will have all of the recoverable data. If you have more than one flaw, then you'll have to fuss with the copy a bit, trying to "jump" the flaws.

-PatPsql

Error: 7105, Severity: 22, State: 6

My SQL server logs this in Application log:
Error: 7105, Severity: 22, State: 6
Page (1:952446), slot 39 for text, ntext, or image node does not exist.
MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS for
a hot fix for this problem.
I believe this call is free because this is a bug. Can anyone confirm this?
Thanks.Oops.
Wrong group.
"ME" <ME@.mail.com> wrote in message
news:e7fZBsT$GHA.4800@.TK2MSFTNGP05.phx.gbl...
> My SQL server logs this in Application log:
> Error: 7105, Severity: 22, State: 6
> Page (1:952446), slot 39 for text, ntext, or image node does not exist.
> MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS
> for a hot fix for this problem.
> I believe this call is free because this is a bug. Can anyone confirm
> this? Thanks.
>
>sql

Error: 7105, Severity: 22, State: 6

My SQL server logs this in Application log:
Error: 7105, Severity: 22, State: 6
Page (1:952446), slot 39 for text, ntext, or image node does not exist.
MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS for
a hot fix for this problem.
I believe this call is free because this is a bug. Can anyone confirm this?
Thanks.Make the call, they will tell you up front if it will be a covered (free)
call.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"ME" <ME@.mail.com> wrote in message
news:%23OovmtT$GHA.3344@.TK2MSFTNGP03.phx.gbl...
> My SQL server logs this in Application log:
> Error: 7105, Severity: 22, State: 6
> Page (1:952446), slot 39 for text, ntext, or image node does not exist.
> MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS
> for
> a hot fix for this problem.
> I believe this call is free because this is a bug. Can anyone confirm
> this?
> Thanks.
>|||Hotfix calls are always free. Issues that are determined to be bugs are
free, even if no hotfix yet exists.
If you call the 800 number with the KB article # ready, tell them you are
looking for the hotfix, they will code it as "professional unpaid" and
transfer you to the support engineer.
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
http://kevin3nf.blogspot.com
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eepBp5T$GHA.1760@.TK2MSFTNGP02.phx.gbl...
> Make the call, they will tell you up front if it will be a covered (free)
> call.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "ME" <ME@.mail.com> wrote in message
> news:%23OovmtT$GHA.3344@.TK2MSFTNGP03.phx.gbl...
>> My SQL server logs this in Application log:
>> Error: 7105, Severity: 22, State: 6
>> Page (1:952446), slot 39 for text, ntext, or image node does not exist.
>> MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS
>> for
>> a hot fix for this problem.
>> I believe this call is free because this is a bug. Can anyone confirm
>> this?
>> Thanks.
>>
>

Error: 644, Severity: 21, State: 3

How do I actually identify the table to which the index that is having the p
roblem applies? The error message detail gives me an "index page (1:427282)
" and an "index ID 0", in addition to the "RID some long binary string". In
order to reindex using DBC
C DBREINDEX, I need to know the table.
Also, this is the second time in a couple of weeks we've had this problem.
We've used DBCC CHECKDB with the rebuild/repair option (which requires the d
atabase be in single-user mode), received an error of similar kind on tempdb
, then used DBCC DBREINDEX
on the table (which we knew by fortuitous circumstance), and that resolved i
t...for about a week-and-a-half. What should we look into as the potential
cause of this recurrence?
Thanks!Not sure your SQL build, but there was an article for updates under Read
Uncommitted isolation with sort and spool operations:
http://support.microsoft.com/defaul...kb;en-us;328736
Christopher Winn
Business Intelligence Engineer
Edugration Corp.
"JohnAtAMCO" <John.Nowak@.AMCO.Net> wrote in message
news:0F42A68E-9F4A-4BCC-BFDB-33FF49079BBD@.microsoft.com...
> How do I actually identify the table to which the index that is having the
problem applies? The error message detail gives me an "index page
(1:427282)" and an "index ID 0", in addition to the "RID some long binary
string". In order to reindex using DBCC DBREINDEX, I need to know the
table.
> Also, this is the second time in a couple of weeks we've had this problem.
We've used DBCC CHECKDB with the rebuild/repair option (which requires the
database be in single-user mode), received an error of similar kind on
tempdb, then used DBCC DBREINDEX on the table (which we knew by fortuitous
circumstance), and that resolved it...for about a week-and-a-half. What
should we look into as the potential cause of this recurrence?
> Thanks!|||Thanks, Chris. I read that article, as well as a couple of others on the 64
4 error. Unfortunately, we don't seem to be doing any of the things mention
ed (such as collating a unicode column with Latin1_General_BIN, or using upp
ercase preference sort orde
r on a column, etc.).
Also, none of them tell me how to translate the Index Page, Index ID, or RID
Binary String into the actual table to which the Index belongs. That's one
of the things I really need to know in order to further troubleshoot and re
solve this problem.
Any suggestions?
Thanks,
John
-- Winn, Chris wrote: --
Not sure your SQL build, but there was an article for updates under Read
Uncommitted isolation with sort and spool operations:
http://support.microsoft.com/defaul...kb;en-us;328736
Christopher Winn
Business Intelligence Engineer
Edugration Corp.
"JohnAtAMCO" <John.Nowak@.AMCO.Net> wrote in message
news:0F42A68E-9F4A-4BCC-BFDB-33FF49079BBD@.microsoft.com...
> How do I actually identify the table to which the index that is having the
problem applies? The error message detail gives me an "index page
(1:427282)" and an "index ID 0", in addition to the "RID some long binary
string". In order to reindex using DBCC DBREINDEX, I need to know the
table.
We've used DBCC CHECKDB with the rebuild/repair option (which requires the
database be in single-user mode), received an error of similar kind on
tempdb, then used DBCC DBREINDEX on the table (which we knew by fortuitous
circumstance), and that resolved it...for about a week-and-a-half. What
should we look into as the potential cause of this recurrence?|||I'd be willing to bet, since you mentioned a similar error against the
tempdb, your server is experiecing hardware faults and you should be
prepared for failure. There is no such Index ID 0 anywhere in MSSQL. That
error shows up in only a few "bug fixes" as mentioned previously.
Chris
"JohnAtAMCO" <anonymous@.discussions.microsoft.com> wrote in message
news:3B57F016-6EA4-4561-B343-BC57DFEF9025@.microsoft.com...
> Thanks, Chris. I read that article, as well as a couple of others on the
644 error. Unfortunately, we don't seem to be doing any of the things
mentioned (such as collating a unicode column with Latin1_General_BIN, or
using uppercase preference sort order on a column, etc.).
> Also, none of them tell me how to translate the Index Page, Index ID, or
RID Binary String into the actual table to which the Index belongs. That's
one of the things I really need to know in order to further troubleshoot and
resolve this problem.
> Any suggestions?
> Thanks,
> John
> -- Winn, Chris wrote: --
> Not sure your SQL build, but there was an article for updates under
Read
> Uncommitted isolation with sort and spool operations:
> http://support.microsoft.com/defaul...kb;en-us;328736
> Christopher Winn
> Business Intelligence Engineer
> Edugration Corp.
> "JohnAtAMCO" <John.Nowak@.AMCO.Net> wrote in message
> news:0F42A68E-9F4A-4BCC-BFDB-33FF49079BBD@.microsoft.com...
having the
> problem applies? The error message detail gives me an "index page
> (1:427282)" and an "index ID 0", in addition to the "RID some long
binary
> string". In order to reindex using DBCC DBREINDEX, I need to know
the
> table.
problem.
> We've used DBCC CHECKDB with the rebuild/repair option (which
requires the
> database be in single-user mode), received an error of similar kind
on
> tempdb, then used DBCC DBREINDEX on the table (which we knew by
fortuitous
> circumstance), and that resolved it...for about a week-and-a-half.
What
> should we look into as the potential cause of this recurrence?
>
>|||Chris,
Too bad you're not the guy responsible for our server hardware He insist
s it can't be hardware, it's all redundantly fault-tolerant, so my earlier s
uggestions to look for a disk failure have not been heard...
Meanwhile, I'm still hoping someone can tell me how to locate the table, giv
en the info in the error message. The MSDN SQL Server Troubleshooting page
that corresponds to this error says:
1. Record the value of index page and index ID...
2. Identify which table and index correspond to the index page number.
...
But nowhere does it tell me how to get from step 1 to step 2! How do I iden
tify the table and index from the index page and index ID?
Thanks again,
John|||AN indid = 0 is a heap table ( no clustered index)..
The DBCC PAGE command will give you the object ID that the page belongs to,
then (in the appropriate db)
select object_name(id) will give you the table name
DBCC page details can be found at
http://support.microsoft.com/defaul...=kb;en-us;83065
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"JohnAtAMCO" <john.nowak@.amco.net> wrote in message
news:BD6079EB-4995-4FE7-90A1-E786D929BEC9@.microsoft.com...
> Chris,
> Too bad you're not the guy responsible for our server hardware He
insists it can't be hardware, it's all redundantly fault-tolerant, so my
earlier suggestions to look for a disk failure have not been heard...
> Meanwhile, I'm still hoping someone can tell me how to locate the table,
given the info in the error message. The MSDN SQL Server Troubleshooting
page that corresponds to this error says:
> 1. Record the value of index page and index ID...
> 2. Identify which table and index correspond to the index page number.
> ...
> But nowhere does it tell me how to get from step 1 to step 2! How do I
identify the table and index from the index page and index ID?
> Thanks again,
> John|||Hi John,
What kind of hardware is this? HP/Compaq? If so, I suggest upgrading
firmware of the controller. It may not be hard disk problem but
controller.
Yih-Yoon Lee
On Tue, 23 Mar 2004 09:36:06 -0800, JohnAtAMCO wrote:

> Chris,
> Too bad you're not the guy responsible for our server hardware He insi
sts it can't be hardware, it's all redundantly fault-tolerant, so my earlier
suggestions to look for a disk failure have not been heard...
> Meanwhile, I'm still hoping someone can tell me how to locate the table, g
iven the info in the error message. The MSDN SQL Server Troubleshooting pag
e that corresponds to this error says:
> 1. Record the value of index page and index ID...
> 2. Identify which table and index correspond to the index page number.
> ...
> But nowhere does it tell me how to get from step 1 to step 2! How do I id
entify the table and index from the index page and index ID?
> Thanks again,
> Johnsql

Error: 644

I get this warning when I do a dbcc checkdb:

Server: Msg 2750, Level 16, State 4, Line 2
Warning: Page (1:300465), Slot 9 in object 2009058193 Index 0 Column Val value -1.#QNAN is out of range for data type "real". Update column to a legal value.

A service using a stored procedure result in the following errir in the event log:

Error 644, Severity: 21, State 5
Could not find the index entry for RID '16ca8880200' in index page (1:31928), index ID 7, database 'BPS'.

I have tried 'dbcc checkdb' and 'dbcc checktable' both with option REPAIR_REBUILD and I have tried 'dbcc reindex' but none of them helped.

Are there other options or can I find the column that shall be updated to a legal value?Did you get any error messages when your ran the DBCC's?

Sounds like a corruption problem to me...

Can you export the data? You may need to do it in ranges...you'll find there will be probably 1 corrupt row...

SELECT * FROM myTable WHERE identity < 1000
and so on...do a binary search..start at the middle your data...

In either Case, you'll either need to do a restore (which hopefully isn't corrupt), or export the data and build a new db...don't know what else could be hosed..

But that's only my own opinion (MOO)|||Have you tried to drop the index and recreate it ?
Have you used the REPAIR_ALLOW_DATA_LOSS option ?

Error: 1101, Severity: 17, State: 10

Error: 1101, Severity: 17, State: 10
Could not allocate new page for database 'TEMPDB'. There
are no more pages available in filegroup DEFAULT. Space
can be created by dropping objects, adding additional
files, or allowing file growth..
Does anybody know the quick fix to this? Thx.Rose,
Tempdb has filled up its space or has filled the disk
where it was created.
This leads to an MSDN article on expanding databases with
some specific comments on tempdb.
http://tinyurl.com/hoh4
Since tempdb is recreated each time you start the server
a restart will clear the current problem. However, you
should try to find out what was happening that filled
tempdb and try to prevent it from happening again.
Russell Fields
>--Original Message--
>Error: 1101, Severity: 17, State: 10
>Could not allocate new page for database 'TEMPDB'. There
>are no more pages available in filegroup DEFAULT. Space
>can be created by dropping objects, adding additional
>files, or allowing file growth..
>Does anybody know the quick fix to this? Thx.
>.
>|||Rose,
>> Does anybody know the quick fix to this?
How about the one mentioned in the error message itself? Did you check if
there is enough space in the filegroup?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rose Mbatuchic" <simacy6@.yahoo.com> wrote in message
news:0bd401c35059$82aec8b0$a401280a@.phx.gbl...
> Error: 1101, Severity: 17, State: 10
> Could not allocate new page for database 'TEMPDB'. There
> are no more pages available in filegroup DEFAULT. Space
> can be created by dropping objects, adding additional
> files, or allowing file growth..
> Does anybody know the quick fix to this? Thx.

Error with stored procedure

I have the following code in my code behind page:

 Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
 Dim CM As New SqlCommand("spCCF_CrossTab", CN)
 CM.CommandType = CommandType.StoredProcedure
 CM.Parameters.Add(New SqlParameter("@.LocationID", "CCFIF"))
 CM.Parameters.Add(New SqlParameter("@.BeginDate", dtbStart.Text))
 CM.Parameters.Add(New SqlParameter("@.EndDate", dtbEnd.Text))
 CN.Open()
 DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
 dgReport.DataSource = DR
 dgReport.DataBind()

A SQL exception is thrown: Incorrect syntax near the keyword 'END'

But I turned on tracing in Enterprise Manager, the following request is sent to SQL:

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

In query analyzer the above line executes without error and returns the expected information.

My stored procedure is:

CREATE PROCEDURE spCCF_CrossTab
@.LocationID varchar(10),
@.BeginDate varchar(10),
@.EndDate varchar(10)
AS
declare @.select varchar(8000), @.sumfunc varchar(100), @.pivot varchar(100), @.table varchar(100), @.where varchar(1000)
select @.select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @.BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @.EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action'
select @.sumfunc= 'COUNT(ActionID)'
select @.pivot='UserName'
select @.table= 'UserInfo'
select @.where='(dbo.UserInfo.LocationID = ''' + @.LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '
+ @.pivot + ' Is Not Null and ' + @.where)
SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )
SELECT @.delim=(CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END)
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')
EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I've been banging my head on this for quite some time now, any insight someone might have as to the problem would be greatly appreciated! Thanks!I don't see the ELSE and END part of the CASE statement in the code below.

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', 'should be something more here? FROM ##pivot

But that doesn't explain why it works in Query Analyzer but not through code.|||I'm using the code from this article: http://www.sqlteam.com/item.asp?ItemID=2955
which I have seen recommended before on the forums so I'm assuming someone has gotten this to work.

I've added a few things but have left everything after the statement below unchanged.
<code>
SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, 'END)')
<code
Again, it does work successfully in query analyzer and SQL trace does not appear to be generating any errors.|||The code in the article runs fine for me against the Pub database. Yes, many people have gotten it to work.

But you are not running the exact code from the article, so that's a moot point.

My suggestion would be to specify the datatype of your parameters explicitly in your ASP.NET code. It looks like ASP.NET is assuming nVarchar. I don't know if that makes a difference, but I would try that. Additionally, I would add an Output parameter to the stored procedure and pass back the @.select variable to your ASP.NET page and have your ASP.NET page display it. This will show you exactly what the stored procedure is trying to execute and might help your debugging effort.

Terri|||The stored procedure runs fine for me when in Query Analzyer. The problems is when I'm trying to call it from the ASP page.

I removed my modifications from the SP and am still running into the same exact error on the aspx page. I am already explicity setting the datatype for the parameters. Setting an output parameter in the SP is doing nothing in my debug efforts since it never gets to that point in the code and is erroring prior.

The error my aspx page is throwing a SQL exception: Incorrect syntax near the keyword 'END'.

I'm still trudging away at this and am not getting any closer to solving the problem. Any other insight on this problem?
Thanks!|||We need to see what @.select holds when your stored procedure goes to EXEC it.

There are a few ways to do this. Can you add an OUTPUT parameter to your stored procedure to hold @.select, and then output the value of this parameter on your page and then let us know what it is?

Terri|||Can you tell me how to get the output parameter to return to the aspx page as I'm getting an error when trying to do so? i have the following:

...
arParms(3) = New SqlParameter("select", SqlDbType.NVarChar, 8000)
arParms(3).Direction = ParameterDirection.ReturnValue
...
Dim strReturn As String = arParms(3).ToString ' also tried response.write (arParms(3).Value)
Response.Write(strReturn)
SP: DECLARE @.sql varchar(8000)
and instead of exec @.select I have return @.select

Error I get: Syntax error converting the varchar value 'SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID), 'ashelts' = COUNT ... (and then too long of a select statement to write out the entire message).

In debugging I did notice that this line of code in the sp:

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, 'END)' )

was causing part of the problem because the select statement looked like:

SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID END)

So I removed the END, now I'm getting a new error but I can't get to completely write on the screen I get as SQL exception: The name 'SELECT dbo.ActionCodes... (part of the select statement). I've tried simplifying my query and it still is too long to display the entire error.

Thanks

On a frustrating side; What I still can't understand it WHY does the stored procedure work (with the parameters I have from the aspx page) in Query Analyzer?|||Ok - actually got the entire sql error finally:

The name 'SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID) FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name' is not a valid identifier.|||To get the value of @.select back to your ASP.NET page, I think you should have:

arParms(3) = New SqlParameter("@.select", SqlDbType.NVarChar, 8000)
arParms(3).Direction = ParameterDirection.Output

You shouldn't need to RETURN @.select in the stored procedure. Just comment out the EXEC @.select line so no errors are generated.

But I am afraid I am leading you astray. If your stored procedure was running correctly from query analyzer with the desired parameters, then your stored procedure should not need any revisions.

Is this still returning the correct results from Query Analyzer?

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

If so, can you temporarily forego the Command parameters and just put that exec statement into your ASP.NET page? (untested)

Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim CM As New SqlCommand("exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'", CN)
CM.CommandType = CommandType.Text
CN.Open()
DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
dgReport.DataSource = DR
dgReport.DataBind()

Does this return the expected result?

Terri|||No. Query analyzer doesn't like this either (error 203 invalid identifier). HOWEVER, when I put this line back in the SP with the word 'END'

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' End )' )

Query analyzer works just fine with:

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

What the aspx page does:
without the word End I get error 203 invalid identifier.
With End I get the syntax error again "Incorrect syntax near the keyword 'End'."

The problem is I have to have about 8 different aspx pages that are going to need similar cross tab type reports - and I want to make the pages and code so I don't have to modify the aspx pages everytime there is a staff change. I can handle simple stored procedures but I really don't understand what is going on in this one enough to trouble shoot very well.

I appreciate your help in trying to get this worked out.|||For grins and giggles, what happens when you present the dates in ISO format?

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'20031103', @.EndDate = N'20031104'

Terri|||Tried that - still an error.
I decided to start from scratch - took just the code for the stored procedure and added a simple select statement.

Still the same darn error with the END. Removing it - I get a cross tab table - but the values are a sum of all the values for that row - I get a table that each row has the same value in each column. I see the importance of the END, have been reading up on the stuff function, but still can't get this to work.

Since I don't have a DBA accessible, I may have to (ugh!) go to a case statement and figure out how I will manage changing staff.|||If you are still having trouble, script out all of your tables, and provide some data via a series of INSERT statements. This one is really tough to help with without having the real "stuff" to mess with.

Terri|||I was able to make the scripts for the tables, but is there a tool for doing the inserts?
I found some different code for doing the cross tabs. Does the same thing: works fine from QA, but get a SQL syntax error "Incorrect syntax at keyword 'END'" from the aspx page.

I tried this on a sample database with sample code provided (copied the code, didn't even retype) - same exact situation: works in query analyzer, syntax error with the aspx page.

I'm really doubting the problem is in the code since I have had a number of circumstances where this works in QA. Could the problem be in the version of the framework or .NET? I'm using Visual Studio 2002, 1.0 of the framework.

CREATE PROCEDURE crosstabextended
@.select_stmt varchar(8000),
@.groupfn varchar(100),
@.pivot_column varchar(100),
@.output_table varchar(100),
@.select_table varchar(100)
AS
DECLARE @.sql varchar(8000)
DECLARE @.delimiter varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DROP TABLE ##pivot
EXEC ('SELECT ' + @.pivot_column + ' AS pivot INTO ##pivot FROM ' + @.select_table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot_column + ' FROM ' + @.select_table + ' WHERE '
+ @.pivot_column + ' Is Not Null' )
-- Add the string END to the group function (@.groupfn) variable
SELECT @.sql='', @.groupfn=stuff(@.groupfn, len(@.groupfn), 1, ' END)' )
--Check if pivot column starts with char or date
SELECT @.delimiter=CASE SIGN( CHARINDEX('char', data_type)+CHARINDEX('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @.sql=@.sql + '''' + CONVERT(varchar(100), pivot) + ''' = ' +
STUFF(@.groupfn,CHARINDEX( '(', @.groupfn )+1, 0, ' CASE ' + @.pivot_column + ' WHEN '
+ @.delimiter + CONVERT(varchar(100), pivot) + @.delimiter + ' THEN ' ) + ', ' FROM ##pivot
SELECT @.sql=LEFT(@.sql, LEN(@.sql)-1)
SELECT @.select_stmt=STUFF(@.select_stmt, CHARINDEX(' FROM ', @.select_stmt)+1, 0, ', ' + @.sql + ' ')
SELECT @.select_stmt=REPLACE(@.select_stmt, ' FROM', 'INTO '+ @.output_table + ' FROM')
IF EXISTS(SELECT table_name FROM tempdb.information_schema.tables WHERE table_name = @.output_table)
BEGIN
EXECUTE('DROP TABLE ' + @.output_table)
END
EXEC (@.select_stmt)
EXECUTE('SELECT * FROM ' + @.output_table)
SET ANSI_WARNINGS ON
GO

Code used in aspx page:

 Dim CN1 = New SqlConnection(ConfigurationSettings.AppSettings("TestString"))
 Dim CM As New SqlCommand("execute crosstabextended 'select Store_name as StoreName from storemaster inner join sales on (sales.store_id=storemaster.store_id) group by store_name', 'sum(qty)', 'toy_id', '##mytemp', 'toymaster'", CN1)
 CM.CommandType = CommandType.Text
CN1.Open()
 Dim da As SqlDataAdapter
 da = New SqlDataAdapter(CM)
da.Fill(dsData)

Thanks!|||Did you ever find a solution to this problem?

Error with SimplePageHeaders deviceinfo setting

I am using RS2000 SP2 and trying to implement SimplePageHeaders
deviceinfo to put the report header into the page header in Excel, but
cannot get this to work. I'm adding &rc:SimplePageHeaders=TRUE to my
URL but the Excel file does not appear to be generated.
If I add rc:RemoveSpace=0.5in, or rc:OmitFormulas=True, these work OK.
If I use SimplePageHeaders, the dialog to download the xls file
displays no File Name or File Type and if I select Open, I get an
Internet Explorer error "Internet Explorer cannot download from
<servername>. Internet Explorer was not able to open this Internet
site. The requested site is either unavailable or cannot be found.
Please try again later".
I have tried running tcptrace on it - this shows a rendering error:
"Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown. (rrRenderingError) ...... Object reference not set to an
instance of an object."
I've also tried adding the device info to the config file as
illustrated in MSDN online docs, but this appears to be completely
ignored.
Any assistance appreciated.I've had no response so far with the query below. Can anyone confirm
if using this deviceinfo setting should be feasible in RS2000 SP2 and
if there are any known issues with it?
AMasson wrote:
> I am using RS2000 SP2 and trying to implement SimplePageHeaders
> deviceinfo to put the report header into the page header in Excel, but
> cannot get this to work. I'm adding &rc:SimplePageHeaders=TRUE to my
> URL but the Excel file does not appear to be generated.
> If I add rc:RemoveSpace=0.5in, or rc:OmitFormulas=True, these work OK.
> If I use SimplePageHeaders, the dialog to download the xls file
> displays no File Name or File Type and if I select Open, I get an
> Internet Explorer error "Internet Explorer cannot download from
> <servername>. Internet Explorer was not able to open this Internet
> site. The requested site is either unavailable or cannot be found.
> Please try again later".
> I have tried running tcptrace on it - this shows a rendering error:
> "Microsoft.ReportingServices.ReportRendering.ReportRenderingException
> was thrown. (rrRenderingError) ...... Object reference not set to an
> instance of an object."
> I've also tried adding the device info to the config file as
> illustrated in MSDN online docs, but this appears to be completely
> ignored.
> Any assistance appreciated.

error with c# select statement

hi i have copied this from my other page where it works fine and i cant understand what is going wrong! maybe one of your guys can point out what i cant see! herei s my code

string strOrderID = Request.QueryString["orderID"].ToString();

int intOrderID =Convert.ToInt32(strOrderID);

int intCustID =Convert.ToInt32(Request.QueryString["qsnOrderCustID"].ToString());

lblCustomerID.Text = Request.QueryString["qsnOrderCustID"].ToString();lblOrderID.Text = Request.QueryString["orderID"].ToString();

SqlConnection myConn =newSqlConnection("Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx");

//This is the sql statement.

string sql ="SELECT [del_address], [del_post_code], [del_time] From tbl_del WHERE order_ID = " + intOrderID;

//This creates a sql command which executes the sql statement.

SqlCommand sqlCmd =newSqlCommand(sql, myConn);

myConn.Open();

SqlDataReader dr = sqlCmd.ExecuteReader();

//This reads the first result from the sqlReader

dr.Read();

try

{

lblDelTime.Text =Convert.ToString(dr["del_time"].ToString);

lblDelAddy.Text = dr["del_address"].ToString();

lblDelPCode.Text = dr["del_post_code"].ToString();if (lblDelAddy.Text !="")

{

lblDelDate.Visible =true;

lblDelTime.Visible =true;

Label1.Visible =true;

Label2.Visible =true;

}

}

catch (Exception except)

{

lblerror.Text =Convert.ToString(except);

}

Regards

Jez

What error are you getting?

|||

try

{

lblDelTime.Text =Convert.ToString(dr["del_time"].ToString);

lblDelAddy.Text = dr["del_address"].ToString();

lblDelPCode.Text = dr["del_post_code"].ToString();if (lblDelAddy.Text !="")

{

lblDelDate.Visible =true;

lblDelTime.Visible =true;

Label1.Visible =true;Label2.Visible =true;

}

}

catch (Exception except)

{

lblerror.Text =Convert.ToString(except);

}

I can only find the above problem in the code...if you can name the error it will be more helpful for us.

|||

i realised that i was being a complete idiot (thats what being up for to long does! makes you miss the obvious!) i took a look at the bigger picture, it said that the textbox wasnt being filled, so maybe a problem with the data reader, no, but because i have 2 data readers on one page and i just copied and pasted the code, i forgot to put dr2[" instead of dr["

Error while connect to SQL Server using ADO control

The following error happens in my ASP.NET page from time to time. It will disappear after I restart the SQL Server. I guess I messed up in the garbage collection, but I really don't know what to do except using "conn=nothing" statement, can anyone help me with this?

Thanks!

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Runtime.InteropServices.COMException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Source Error:

Line 39: Public Sub New()Line 40: conn = New ADODB.ConnectionLine 41: conn.Open("Provider=SQLOLEDB.1;Persist Security Info=xxxxx;User ID=xxxxx;Initial Catalog=xxxx;Data Source=xxx")Line 42: rs = New ADODB.RecordsetLine 43: my_UserID = 0

try encasing your db interaction in a using{} block - that way it will make sure that the dispose() method has been called and the objects marked for garbage collection.|||

DWesthead:

try encasing your db interaction in a using{} block - that way it will make sure that the dispose() method has been called and the objects marked for garbage collection.

I am not using C#, I am using VB, do you know the garbage collection using VB?

Thanks!

|||

In VB you can use something like this:

Using connectionAsNew SqlConnection()

connection.ConnectionString = connectionString

connection.Open()

Console.WriteLine("State: {0}", connection.State)
Console.WriteLine("ConnectionString: {0}", _
connection.ConnectionString)
End Using

|||

I tried to do that, but the "Using" tag is not recognized, maybe there is no such a thing as "USING" in VB?

|||

Using works for me:

Using connAsNew SqlConnectionEndUsing

Using turns blue, and when I finish the first line, it inserts the End using statement for me.

Curious, why are you using adodb instead of sqlconnect/sqlcommand?

|||

I am using adodb because I am used to it. I programed several ASP program using adodb.

I think I might not have to most recent asp.net sdk install then, I will try to install it now.

Could you tell me any different from sqlconnection and the adodb connection? how to use sqlconnection?

Thanks!

Alex

|||

They are very similiar. The easiest way to make a correct sqlconnection string is to drop a sqldatasource control on your page, then use the wizard

I often do that even for connection strings that I use programmatically, I just tell the wizard to put it in web.config, and after the wizard is done, I'll delete the sqldatasource from the page, and access the connectionstring programmatically.