Help Backup: dbcc

Showing posts with label dbcc. Show all posts

Friday, March 9, 2012

Help-Corrupt table

Yes. DBCC CheckTable is basically the same thing except
it only does one table. DBCC CheckDB generated the same
error.
[vbcol=seagreen]
>--Original Message--
>Have you tried a DBCC CHECKDB ?
>Peter
>"The length of this document defends it well against the
>risk of its being read."
>Winston Churchill
>
CHECK[vbcol=seagreen]
messages,[vbcol=seagreen]
REPAIR_ALLOW_DATA_LOSS
>select
>it
>.
>
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"VanColeJones" <anonymous@.discussions.microsoft.com> wrote in message
news:064801c518fd$15f1d550$a601280a@.phx.gbl...[vbcol=seagreen]
> Yes. DBCC CheckTable is basically the same thing except
> it only does one table. DBCC CheckDB generated the same
> error.
> CHECK
> messages,
> REPAIR_ALLOW_DATA_LOSS

Help-Corrupt table

This is just a followup if anybody's curious. This hotfix=20
didn't totally fix the error. DBCC CheckTable and CheckDB=20
were still giving errors. Exception_Access_Violations=20
were still occuring and the SQL server was actually=20
stopping itself. The hotfix did at least make it so that=20
I could at least select from the table and recreate it=20
without having to go to backup. So I just recreated the=20
table and dropped the old one. All is good now...
[vbcol=seagreen]
>--Original Message--
>Thanks, that was it. Had to get the hotfix and test it=20
>and all, but that was the problem.
>Thanks,
>Van
>return=20
>clustered=20
>when=20
>this=20
you=20[vbcol=seagreen]
>might=20
>BOL=20
and=20[vbcol=seagreen]
>in=20
mind,=20
>except=20
>same=20
>somehow. =20
>.
>
Thanks everyone for all your postigs. This is not the first time I meet
hypotetical clustered index, and they always screw up the tables they
are on. Most of the time they just hung select statement. Now it was
DBCC CHECKDB which was screwed. I was lucky enough that my hypotetical
friend index had typeable name and I managed to drop it. As soon as I
dropped it the message had gone.
Thanks!
Eugene
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Help-Corrupt table

Have you tried a DBCC CHECKDB ?
Peter
"The length of this document defends it well against the
risk of its being read."
Winston Churchill

>--Original Message--
>I have a table that got corrupted last night somehow.
>dbcc checktable on the table returns:
>Server: Msg 7995, Level 16, State 3, Line 1
>Database 'MPF' consistency errors in sysobjects,
>sysindexes, syscolumns, or systypes prevent further CHECK
>processing.
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>I've tried all 3 options including REPAIR_ALLOW_DATA_LOSS
>and they all get the same results (above). I can't
select
>from the table either. I backed up the db and restored
it
>to another server...same thing. What all are my options
>at this point? Is there anyway to fix the table without
>going to backup?
>Thanks,
>Van
>.
>http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"VanColeJones" <anonymous@.discussions.microsoft.com> wrote in message
news:064801c518fd$15f1d550$a601280a@.phx.gbl...[vbcol=seagreen]
> Yes. DBCC CheckTable is basically the same thing except
> it only does one table. DBCC CheckDB generated the same
> error.
>
> CHECK
> messages,
> REPAIR_ALLOW_DATA_LOSS

Help-Corrupt table

This is just a followup if anybody's curious. This hotfix=20
didn't totally fix the error. DBCC CheckTable and CheckDB=20
were still giving errors. Exception_Access_Violations=20
were still occuring and the SQL server was actually=20
stopping itself. The hotfix did at least make it so that=20
I could at least select from the table and recreate it=20
without having to go to backup. So I just recreated the=20
table and dropped the old one. All is good now...

>--Original Message--
>Thanks, that was it. Had to get the hotfix and test it=20
>and all, but that was the problem.
>Thanks,
>Van
>
>return=20
>clustered=20
>when=20
>this=20
you=20[vbcol=seagreen]
>might=20
>BOL=20
and=20[vbcol=seagreen]
>in=20
mind,=20[vbcol=seagreen]
>except=20
>same=20
>somehow. =20
>.
>Thanks everyone for all your postigs. This is not the first time I meet
hypotetical clustered index, and they always screw up the tables they
are on. Most of the time they just hung select statement. Now it was
DBCC CHECKDB which was screwed. I was lucky enough that my hypotetical
friend index had typeable name and I managed to drop it. As soon as I
dropped it the message had gone.
Thanks!
Eugene
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Help-Corrupt table

I have a table that got corrupted last night somehow.
dbcc checktable on the table returns:
Server: Msg 7995, Level 16, State 3, Line 1
Database 'MPF' consistency errors in sysobjects,
sysindexes, syscolumns, or systypes prevent further CHECK
processing.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I've tried all 3 options including REPAIR_ALLOW_DATA_LOSS
and they all get the same results (above). I can't select
from the table either. I backed up the db and restored it
to another server...same thing. What all are my options
at this point? Is there anyway to fix the table without
going to backup?
Thanks,
VanHave you tried a DBCC CHECKDB ?
Peter
"The length of this document defends it well against the
risk of its being read."
Winston Churchill
>--Original Message--
>I have a table that got corrupted last night somehow.
>dbcc checktable on the table returns:
>Server: Msg 7995, Level 16, State 3, Line 1
>Database 'MPF' consistency errors in sysobjects,
>sysindexes, syscolumns, or systypes prevent further CHECK
>processing.
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>I've tried all 3 options including REPAIR_ALLOW_DATA_LOSS
>and they all get the same results (above). I can't
select
>from the table either. I backed up the db and restored
it
>to another server...same thing. What all are my options
>at this point? Is there anyway to fix the table without
>going to backup?
>Thanks,
>Van
>.
>|||Yes. DBCC CheckTable is basically the same thing except
it only does one table. DBCC CheckDB generated the same
error.
>--Original Message--
>Have you tried a DBCC CHECKDB ?
>Peter
>"The length of this document defends it well against the
>risk of its being read."
>Winston Churchill
>
>>--Original Message--
>>I have a table that got corrupted last night somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>I've tried all 3 options including
REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>select
>>from the table either. I backed up the db and restored
>it
>>to another server...same thing. What all are my options
>>at this point? Is there anyway to fix the table without
>>going to backup?
>>Thanks,
>>Van
>>.
>.
>|||In that case no - sorry, you will need to restore from
backup.
If you still have access to your Databases Data then you
could re-create it using the generate SQL (but that might
not work as it looks at the tables that caused the error),
DTS it, delete the old one, then rename the new one.
BTW CHECKTABLE and CHECKDB are different if you check BOL
under DBCC CHECKDB you will see 'DBCC CHECKDB performs the
same checking as if both a DBCC CHECKALLOC statement and a
DBCC CHECKTABLE statement were executed for each table in
the database.'
Anyway sorry I can't be of more help.
Peter
"Age is an issue of mind over matter. If you don't mind,
it doesn't matter."
Mark Twain
>--Original Message--
>Yes. DBCC CheckTable is basically the same thing except
>it only does one table. DBCC CheckDB generated the same
>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against the
>>risk of its being read."
>>Winston Churchill
>>
>>--Original Message--
>>I have a table that got corrupted last night somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
>CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
>messages,
>>contact your system administrator.
>>I've tried all 3 options including
>REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>>select
>>from the table either. I backed up the db and restored
>>it
>>to another server...same thing. What all are my
options
>>at this point? Is there anyway to fix the table
without
>>going to backup?
>>Thanks,
>>Van
>>.
>>.
>.
>|||http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"VanColeJones" <anonymous@.discussions.microsoft.com> wrote in message
news:064801c518fd$15f1d550$a601280a@.phx.gbl...
> Yes. DBCC CheckTable is basically the same thing except
> it only does one table. DBCC CheckDB generated the same
> error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against the
>>risk of its being read."
>>Winston Churchill
>>
>>--Original Message--
>>I have a table that got corrupted last night somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
> CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
> messages,
>>contact your system administrator.
>>I've tried all 3 options including
> REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>>select
>>from the table either. I backed up the db and restored
>>it
>>to another server...same thing. What all are my options
>>at this point? Is there anyway to fix the table without
>>going to backup?
>>Thanks,
>>Van
>>.
>>.|||I see a hypothetical index on the corrupt table named "ĸ8=04". It's not really a lower case "k". It's something else and the square is somekind a carrage return or something because there is a blank line between this index name and the next. This index is listed as a clustered, hypothetical index. I already have a clustered index on this table (different fields). Not sure if a clustered hypothetical index is valid. And the name? I tried to drop it. Said it didn't exist but left the square out of the name (like it didn't see the square when it tried to drop it. So I u used brackets to refference it, and it says it's still not there (with the square this time).
>--Original Message--
>In that case no - sorry, you will need to restore from >backup.
>If you still have access to your Databases Data then you >could re-create it using the generate SQL (but that might >not work as it looks at the tables that caused the error), >DTS it, delete the old one, then rename the new one.
>BTW CHECKTABLE and CHECKDB are different if you check BOL >under DBCC CHECKDB you will see 'DBCC CHECKDB performs the >same checking as if both a DBCC CHECKALLOC statement and a >DBCC CHECKTABLE statement were executed for each table in >the database.'
>Anyway sorry I can't be of more help.
>Peter
>"Age is an issue of mind over matter. If you don't mind, >it doesn't matter." >Mark Twain >
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing except >>it only does one table. DBCC CheckDB generated the same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and restored >>it >>to another server...same thing. What all are my >options >>at this point? Is there anyway to fix the table >without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>.
>|||Hmm Interesting, this maybe of help.
http://support.microsoft.com/default.aspx?scid=3Dkb;en-
us;818097
Peter
"All generalizations are false, including this one."
Mark Twain
>--Original Message--
>I see a hypothetical index on the corrupt table >named "ĸ8=04". It's not really a lower case "k". It's >something else and the square is somekind a carrage return >or something because there is a blank line between this >index name and the next. This index is listed as a >clustered, hypothetical index. I already have a clustered >index on this table (different fields). Not sure if a >clustered hypothetical index is valid. And the name? I >tried to drop it. Said it didn't exist but left the >square out of the name (like it didn't see the square when >it tried to drop it. So I u used brackets to refference >it, and it says it's still not there (with the square this >time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from >>backup.
>>If you still have access to your Databases Data then you >>could re-create it using the generate SQL (but that might >>not work as it looks at the tables that caused the >error), >>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check BOL >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs >the >>same checking as if both a DBCC CHECKALLOC statement and >a >>DBCC CHECKTABLE statement were executed for each table in >>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind, >>it doesn't matter." >>Mark Twain >>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing except >>it only does one table. DBCC CheckDB generated the same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against >the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and >restored >>it >>to another server...same thing. What all are my >>options >>at this point? Is there anyway to fix the table >>without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>.
>|||Thanks, that was it. Had to get the hotfix and test it and all, but that was the problem.
Thanks,
Van
>--Original Message--
>I see a hypothetical index on the corrupt table >named "ĸ8=04". It's not really a lower case "k". It's >something else and the square is somekind a carrage return >or something because there is a blank line between this >index name and the next. This index is listed as a >clustered, hypothetical index. I already have a clustered >index on this table (different fields). Not sure if a >clustered hypothetical index is valid. And the name? I >tried to drop it. Said it didn't exist but left the >square out of the name (like it didn't see the square when >it tried to drop it. So I u used brackets to refference >it, and it says it's still not there (with the square this >time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from >>backup.
>>If you still have access to your Databases Data then you >>could re-create it using the generate SQL (but that might >>not work as it looks at the tables that caused the >error), >>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check BOL >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs >the >>same checking as if both a DBCC CHECKALLOC statement and >a >>DBCC CHECKTABLE statement were executed for each table in >>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind, >>it doesn't matter." >>Mark Twain >>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing except >>it only does one table. DBCC CheckDB generated the same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against >the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and >restored >>it >>to another server...same thing. What all are my >>options >>at this point? Is there anyway to fix the table >>without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>.
>|||That KB has nothing whatsoever do to with the problem described. CHECKDB
won't do anything different from CHECKTABLE in this case. You've got system
table corruption that DBCC repairs will not fix so you must restore from
your last known good backup. Tibor's link is a good set of steps to follow.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:068c01c51903$51607780$a601280a@.phx.gbl...
Hmm Interesting, this maybe of help.
http://support.microsoft.com/default.aspx?scid=kb;en-
us;818097
Peter
"All generalizations are false, including this one."
Mark Twain
>--Original Message--
>I see a hypothetical index on the corrupt table
>named "ĸ8 ". It's not really a lower case "k". It's
>something else and the square is somekind a carrage
return
>or something because there is a blank line between this
>index name and the next. This index is listed as a
>clustered, hypothetical index. I already have a
clustered
>index on this table (different fields). Not sure if a
>clustered hypothetical index is valid. And the name? I
>tried to drop it. Said it didn't exist but left the
>square out of the name (like it didn't see the square
when
>it tried to drop it. So I u used brackets to refference
>it, and it says it's still not there (with the square
this
>time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from
>>backup.
>>If you still have access to your Databases Data then you
>>could re-create it using the generate SQL (but that
might
>>not work as it looks at the tables that caused the
>error),
>>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check
BOL
>>under DBCC CHECKDB you will see 'DBCC CHECKDB performs
>the
>>same checking as if both a DBCC CHECKALLOC statement and
>a
>>DBCC CHECKTABLE statement were executed for each table
in
>>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind,
>>it doesn't matter."
>>Mark Twain
>>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing
except
>>it only does one table. DBCC CheckDB generated the
same
>>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against
>the
>>risk of its being read."
>>Winston Churchill
>>
>>--Original Message--
>>I have a table that got corrupted last night
somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
>>CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
>>messages,
>>contact your system administrator.
>>I've tried all 3 options including
>>REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>>select
>>from the table either. I backed up the db and
>restored
>>it
>>to another server...same thing. What all are my
>>options
>>at this point? Is there anyway to fix the table
>>without
>>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>.
>|||Oops - mistyped the URL and looked at the wrong KB article. Well done Peter.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:e4R1dHRGFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That KB has nothing whatsoever do to with the problem described. CHECKDB
> won't do anything different from CHECKTABLE in this case. You've got
system
> table corruption that DBCC repairs will not fix so you must restore from
> your last known good backup. Tibor's link is a good set of steps to
follow.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
> news:068c01c51903$51607780$a601280a@.phx.gbl...
> Hmm Interesting, this maybe of help.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;818097
> Peter
> "All generalizations are false, including this one."
> Mark Twain
>
> >--Original Message--
> >I see a hypothetical index on the corrupt table
> >named "ĸ8 ". It's not really a lower case "k". It's
> >something else and the square is somekind a carrage
> return
> >or something because there is a blank line between this
> >index name and the next. This index is listed as a
> >clustered, hypothetical index. I already have a
> clustered
> >index on this table (different fields). Not sure if a
> >clustered hypothetical index is valid. And the name? I
> >tried to drop it. Said it didn't exist but left the
> >square out of the name (like it didn't see the square
> when
> >it tried to drop it. So I u used brackets to refference
> >it, and it says it's still not there (with the square
> this
> >time).
> >
> >>--Original Message--
> >>In that case no - sorry, you will need to restore from
> >>backup.
> >>
> >>If you still have access to your Databases Data then you
> >>could re-create it using the generate SQL (but that
> might
> >>not work as it looks at the tables that caused the
> >error),
> >>DTS it, delete the old one, then rename the new one.
> >>
> >>BTW CHECKTABLE and CHECKDB are different if you check
> BOL
> >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs
> >the
> >>same checking as if both a DBCC CHECKALLOC statement and
> >a
> >>DBCC CHECKTABLE statement were executed for each table
> in
> >>the database.'
> >>
> >>Anyway sorry I can't be of more help.
> >>Peter
> >>
> >>"Age is an issue of mind over matter. If you don't mind,
> >>it doesn't matter."
> >>Mark Twain
> >>
> >>
> >>--Original Message--
> >>Yes. DBCC CheckTable is basically the same thing
> except
> >>it only does one table. DBCC CheckDB generated the
> same
> >>error.
> >>
> >>--Original Message--
> >>Have you tried a DBCC CHECKDB ?
> >>
> >>Peter
> >>
> >>"The length of this document defends it well against
> >the
> >>risk of its being read."
> >>Winston Churchill
> >>
> >>
> >>--Original Message--
> >>I have a table that got corrupted last night
> somehow.
> >>dbcc checktable on the table returns:
> >>
> >>Server: Msg 7995, Level 16, State 3, Line 1
> >>Database 'MPF' consistency errors in sysobjects,
> >>sysindexes, syscolumns, or systypes prevent further
> >>CHECK
> >>processing.
> >>DBCC execution completed. If DBCC printed error
> >>messages,
> >>contact your system administrator.
> >>
> >>I've tried all 3 options including
> >>REPAIR_ALLOW_DATA_LOSS
> >>and they all get the same results (above). I can't
> >>select
> >>from the table either. I backed up the db and
> >restored
> >>it
> >>to another server...same thing. What all are my
> >>options
> >>at this point? Is there anyway to fix the table
> >>without
> >>going to backup?
> >>
> >>Thanks,
> >>
> >>Van
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >.
> >
>|||This is just a followup if anybody's curious. This hotfix didn't totally fix the error. DBCC CheckTable and CheckDB were still giving errors. Exception_Access_Violations were still occuring and the SQL server was actually stopping itself. The hotfix did at least make it so that I could at least select from the table and recreate it without having to go to backup. So I just recreated the table and dropped the old one. All is good now...
>--Original Message--
>Thanks, that was it. Had to get the hotfix and test it >and all, but that was the problem.
>Thanks,
>Van
>>--Original Message--
>>I see a hypothetical index on the corrupt table >>named "ĸ8=04". It's not really a lower case "k". It's >>something else and the square is somekind a carrage >return >>or something because there is a blank line between this >>index name and the next. This index is listed as a >>clustered, hypothetical index. I already have a >clustered >>index on this table (different fields). Not sure if a >>clustered hypothetical index is valid. And the name? I >>tried to drop it. Said it didn't exist but left the >>square out of the name (like it didn't see the square >when >>it tried to drop it. So I u used brackets to refference >>it, and it says it's still not there (with the square >this >>time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from >>backup.
>>If you still have access to your Databases Data then you >>could re-create it using the generate SQL (but that >might >>not work as it looks at the tables that caused the >>error), >>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check >BOL >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs >>the >>same checking as if both a DBCC CHECKALLOC statement and >>a >>DBCC CHECKTABLE statement were executed for each table >in >>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind, >>it doesn't matter." >>Mark Twain >>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing >except >>it only does one table. DBCC CheckDB generated the >same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against >>the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night >somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and >>restored >>it >>to another server...same thing. What all are my >>options >>at this point? Is there anyway to fix the table >>without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>>.
>.
>

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:
> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> > Hello:
> >
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
> > What is the EXACT syntax? You see, the name of the database is JC and the
> > log file is named GPSJClog.ldf. I have tried to place both names in the
> > syntax of the statement but with no success. I keep getting a message saying
> > that the file name is not in sysfiles.
> >
> > This is what I have tried:
> >
> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> > Please help!
> >
> > childofthe1980s
>|||Can you post the commands you tried to execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>> You specify the *logical name of the file*. See the sysfiles table. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
>> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> > Hello:
>> >
>> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> > analyzer in SQL 2000 against the master database.
>> >
>> > What is the EXACT syntax? You see, the name of the database is JC and the
>> > log file is named GPSJClog.ldf. I have tried to place both names in the
>> > syntax of the statement but with no success. I keep getting a message saying
>> > that the file name is not in sysfiles.
>> >
>> > This is what I have tried:
>> >
>> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> > Please help!
>> >
>> > childofthe1980s
>>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:
> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
> >I have done both of those things--no success...
> >
> > PLEASE HELP!!!
> >
> > childofthe1980s
> >
> > "Tibor Karaszi" wrote:
> >
> >> You specify the *logical name of the file*. See the sysfiles table. Also, see
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> >> > Hello:
> >> >
> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >> > analyzer in SQL 2000 against the master database.
> >> >
> >> > What is the EXACT syntax? You see, the name of the database is JC and the
> >> > log file is named GPSJClog.ldf. I have tried to place both names in the
> >> > syntax of the statement but with no success. I keep getting a message saying
> >> > that the file name is not in sysfiles.
> >> >
> >> > This is what I have tried:
> >> >
> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> > Please help!
> >> >
> >> > childofthe1980s
> >>
> >>
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:
>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:
> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
> >Hello:
> >
> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >analyzer in SQL 2000 against the master database.
> >
> >What is the EXACT syntax? You see, the name of the database is JC and the
> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >syntax of the statement but with no success. I keep getting a message saying
> >that the file name is not in sysfiles.
> >
> >This is what I have tried:
> >
> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> >Please help!
> >
> >childofthe1980s
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>> Can you post the commands you tried to execute?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message
>> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>> >I have done both of those things--no success...
>> >
>> > PLEASE HELP!!!
>> >
>> > childofthe1980s
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> You specify the *logical name of the file*. See the sysfiles table.
>> >> Also, see
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> >> > Hello:
>> >> >
>> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE
>> >> > in query
>> >> > analyzer in SQL 2000 against the master database.
>> >> >
>> >> > What is the EXACT syntax? You see, the name of the database is JC
>> >> > and the
>> >> > log file is named GPSJClog.ldf. I have tried to place both names in
>> >> > the
>> >> > syntax of the statement but with no success. I keep getting a
>> >> > message saying
>> >> > that the file name is not in sysfiles.
>> >> >
>> >> > This is what I have tried:
>> >> >
>> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> > Please help!
>> >> >
>> >> > childofthe1980s
>> >>
>> >>
>>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>> Where you IN the database in question? I believe that is required.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >Hello:
>> >
>> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >query
>> >analyzer in SQL 2000 against the master database.
>> >
>> >What is the EXACT syntax? You see, the name of the database is JC and
>> >the
>> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >syntax of the statement but with no success. I keep getting a message
>> >saying
>> >that the file name is not in sysfiles.
>> >
>> >This is what I have tried:
>> >
>> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> >Please help!
>> >
>> >childofthe1980s|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:
> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> > Nevermind. I figured it out.
> >
> > I had to run the following scripts against each of the databases:
> >
> > BACKUP LOG JC with TRUNCATE_ONLY
> > DBCC SHRINKFILE (2, 20)
> >
> > You see, the support documentation on the web did not say that you have to
> > take the fileID of the database--2 in the syntax above-- in order to make
> > it
> > work! You get the number 2 from running the following query against the
> > database:
> >
> > select * from sysfiles
> >
> > It would have been nice if this had been in the documentation!
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
> > "Roy Harvey" wrote:
> >
> >> Where you IN the database in question? I believe that is required.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> >> <childofthe1980s@.discussions.microsoft.com> wrote:
> >>
> >> >Hello:
> >> >
> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
> >> >query
> >> >analyzer in SQL 2000 against the master database.
> >> >
> >> >What is the EXACT syntax? You see, the name of the database is JC and
> >> >the
> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >> >syntax of the statement but with no success. I keep getting a message
> >> >saying
> >> >that the file name is not in sysfiles.
> >> >
> >> >This is what I have tried:
> >> >
> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> >Please help!
> >> >
> >> >childofthe1980s
> >>
>
>|||> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:04A6393F-154E-4A1A-946B-0768F7B47F85@.microsoft.com...
> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
> "Andrew J. Kelly" wrote:
>> Not true. You can use either the ID or the LOGICAL name not the physical
>> name. And hopefully the db is not in FULL recovery mode or you just lost the
>> log chain with that truncate.
>> --
>> Andrew J. Kelly SQL MVP
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>> > Nevermind. I figured it out.
>> >
>> > I had to run the following scripts against each of the databases:
>> >
>> > BACKUP LOG JC with TRUNCATE_ONLY
>> > DBCC SHRINKFILE (2, 20)
>> >
>> > You see, the support documentation on the web did not say that you have to
>> > take the fileID of the database--2 in the syntax above-- in order to make
>> > it
>> > work! You get the number 2 from running the following query against the
>> > database:
>> >
>> > select * from sysfiles
>> >
>> > It would have been nice if this had been in the documentation!
>> >
>> > Thanks!
>> >
>> > childofthe1980s
>> >
>> >
>> >
>> >
>> >
>> > "Roy Harvey" wrote:
>> >
>> >> Where you IN the database in question? I believe that is required.
>> >>
>> >> Roy Harvey
>> >> Beacon Falls, CT
>> >>
>> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> >> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello:
>> >> >
>> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >> >query
>> >> >analyzer in SQL 2000 against the master database.
>> >> >
>> >> >What is the EXACT syntax? You see, the name of the database is JC and
>> >> >the
>> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >> >syntax of the statement but with no success. I keep getting a message
>> >> >saying
>> >> >that the file name is not in sysfiles.
>> >> >
>> >> >This is what I have tried:
>> >> >
>> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> >Please help!
>> >> >
>> >> >childofthe1980s
>> >>
>>

Friday, March 9, 2012

Help-Corrupt table

Help-Corrupt table

Help-Corrupt table

Help-Corrupt table

Help-Corrupt table

HELP--cannot shrink the transaction log

HELP--cannot shrink the transaction log

HELP--cannot shrink the transaction log

Help Backup

Blog Archive

About Me