DBfile usage: error

Showing posts with label error. Show all posts

Thursday, March 29, 2012

DBCC SHRINKFILE

When I attempt to launch DBCC SHRINKFILE for any filename that contains a
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the error
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.

Tuesday, March 27, 2012

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

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

DBCC Shrinkdatabase errors

When DBCC ShrinkDatabase ('db-name') is run; the following error is returned.

File ID 1 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

File ID 3 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

Cannot shrink log file 2 (logmentusnet) because all logical log files are in use.

The files 1-3 are not empty so I'm assuming that another shrink is already running.

Questions:

1. If I restart the database will the errent database shrink stop?

2. How can I find and stop the errent database shrink process?

3. What's going on, and how can I recover the empty space in the database?

I am also facing the same issue.

Any workaround for this.

Regards

kokila

|||

Hy,

I am with the same problem… somebody can help?

File ID 9 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.

dbcc shrinkdatabase error

Hi ,
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgsHi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
> > Hi,
> >
> > Post the exact error.
> >
> > I feel that Probably there will be some active transactions. Execute DBCC
> > OPENTRAN('DBNAME') to identify if there is any open active trans.
> > If yes, wait that to complete. After that you could execute a backup LOG to
> > clear the transaction log and then execute DBCC SHRINKFILE to
> > shrink MDF and LDF seperately.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > > Hi ,
> > >
> > > i was trying to shrink a database but i got the following error :
> > > it's something like " logical file is in use" despite me logging in as a
> > > single user
> > >
> > > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> > >
> > > i have also used the EM's shrink database , though it says it completed
> > > successfully but it did not shrink the database
> > >
> > > kindly advise
> > > tks & rdgs
> >
> >
> >|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Are you trying to shrink the db file or the log file? Sounds like the
>> log
>> file. You should use DBCC SHRINKFILE not database as it allows you to
>> choose the specific file you want to shrink and how much.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> > Hi ,
>> >
>> > i was trying to shrink a database but i got the following error :
>> > it's something like " logical file is in use" despite me logging in as
>> > a
>> > single user
>> >
>> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> > truncate_only)
>> >
>> > i have also used the EM's shrink database , though it says it completed
>> > successfully but it did not shrink the database
>> >
>> > kindly advise
>> > tks & rdgs
>>|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> > Hi Andrew ,
> >
> > i was trying to shrink the log file but it couldn't as per the error
> > below
> > and i have also tried to shrink the database as well , hoping it'll shrink
> > the log file
> >
> > tks & rdgs
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you trying to shrink the db file or the log file? Sounds like the
> >> log
> >> file. You should use DBCC SHRINKFILE not database as it allows you to
> >> choose the specific file you want to shrink and how much.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> >> > Hi ,
> >> >
> >> > i was trying to shrink a database but i got the following error :
> >> > it's something like " logical file is in use" despite me logging in as
> >> > a
> >> > single user
> >> >
> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
> >> > truncate_only)
> >> >
> >> > i have also used the EM's shrink database , though it says it completed
> >> > successfully but it did not shrink the database
> >> >
> >> > kindly advise
> >> > tks & rdgs
> >>
> >>
> >>
>
>|||This is because the recovery mode you were in was preventing the log being
truncated until you'd backed it up. Switching to simple mode removes that
restriction. See BOL for more info ('Shrinking the Transaction Log' is a
good place to start)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:65ADA556-3489-4620-844E-CC0DCB0BDF3A@.microsoft.com...
> Hi Andrew ,
> i did tried with the DBCC shrink file statement but got the "logical log
> in
> use" error as per the DBCC Shrinkdatabase. however, when i changed the
> mode
> to "Simple" recovery mode , i could somehow shrink the db
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Did you actually try DBCC SHRINKFILE? If not you should. You don't need
>> to
>> be in single user mode to run this.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > i was trying to shrink the log file but it couldn't as per the error
>> > below
>> > and i have also tried to shrink the database as well , hoping it'll
>> > shrink
>> > the log file
>> >
>> > tks & rdgs
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Are you trying to shrink the db file or the log file? Sounds like the
>> >> log
>> >> file. You should use DBCC SHRINKFILE not database as it allows you to
>> >> choose the specific file you want to shrink and how much.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> >> > Hi ,
>> >> >
>> >> > i was trying to shrink a database but i got the following error :
>> >> > it's something like " logical file is in use" despite me logging in
>> >> > as
>> >> > a
>> >> > single user
>> >> >
>> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> >> > truncate_only)
>> >> >
>> >> > i have also used the EM's shrink database , though it says it
>> >> > completed
>> >> > successfully but it did not shrink the database
>> >> >
>> >> > kindly advise
>> >> > tks & rdgs
>> >>
>> >>
>> >>
>>

dbcc shrinkdatabase error

Sunday, March 25, 2012

DBCC SHRINK DATABASE TIMED OUT

Hi all,

mydbcc schrink database command failed to shrink
a datbase of 461 gb on sql server 2005 CTP
here's the error. pls help
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

thanks

joey

have you seen ken's blog http://blogs.msdn.com/khen1234/archive/2005/10/20/483015.aspx

and tibor's article http://www.karaszi.com/sqlserver/info_dont_shrink.asp

DBCC SHOWFILESTATS

Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?
What does the documentation for the backup vendor say? Does it say that the login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>
|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make calls
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> What does the documentation for the backup vendor say? Does it say that the login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>
|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]

DBCC SHOWFILESTATS

Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?What does the documentation for the backup vendor say? Does it say that the
login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command,
so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server
i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make call
s
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> What does the documentation for the backup vendor say? Does it say that th
e login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented comman
d, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]

Thursday, March 22, 2012

DBCC Showcontig

Does anyone know why i get this answer:
DBCC execution completed. If DBCC printed error messages,
contact your system administrator. when i do a dbcc
showcontig ofa table? BUt another table gives me results..
Any explanation?
ThanxsPerhaps that table doesn't have any indexes? Use sp_helpindex to check which indexes each table has.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"smiley" <anonymous@.discussions.microsoft.com> wrote in message
news:059601c3adcc$d57d2cf0$a401280a@.phx.gbl...
> Does anyone know why i get this answer:
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator. when i do a dbcc
> showcontig ofa table? BUt another table gives me results..
> Any explanation?
> Thanxssql

DBCC Results into a Table Brings Errors!

If you run the script below, it will cause an error. However, if you
run the select portion after the error, it will have accomplished the
desired result. Unfortunately, the table has to be manually dropped
after the proc is run. Any ideas why'? USing the GO keyword is not
an option as it will blow the rest of the script.
CREATE TABLE #db_file_information(
fileid integer,
theFileGroup integer,
Total_Extents integer,
Used_Extents integer,
db varchar(30),
file_Path_name varchar(300)--,
-- File_Free_space decimal(15,2),
-- Percent_Free decimal(15,2)
)
-- Get the size of the datafiles
insert into #db_file_information exec('DBCC showfilestats')
-- add two columns to the temp table
alter table #db_file_information add dude as
((Total_Extents-Used_Extents)/(Total_extents*1.0))
select * from #db_file_information
drop table #db_file_informationTry,
use northwind
go
CREATE TABLE #db_file_information(
Fileid bigint,
[FileGroup] bigint,
TotalExtents bigint,
UsedExtents bigint,
[Name] sysname,
[FileName] varchar(260)
)
go
-- Get the size of the datafiles
insert into #db_file_information
exec('DBCC showfilestats with NO_INFOMSGS')
-- add two columns to the temp table
alter table #db_file_information
add dude as ((TotalExtents - UsedExtents) / nullif((TotalExtents * 1.0), 0))
go
select * from #db_file_information
go
drop table #db_file_information
go
AMB
"dpaskiet@.comcast.net" wrote:

> If you run the script below, it will cause an error. However, if you
> run the select portion after the error, it will have accomplished the
> desired result. Unfortunately, the table has to be manually dropped
> after the proc is run. Any ideas why'? USing the GO keyword is not
> an option as it will blow the rest of the script.
>
> CREATE TABLE #db_file_information(
> fileid integer,
> theFileGroup integer,
> Total_Extents integer,
> Used_Extents integer,
> db varchar(30),
> file_Path_name varchar(300)--,
> -- File_Free_space decimal(15,2),
> -- Percent_Free decimal(15,2)
> )
> -- Get the size of the datafiles
> insert into #db_file_information exec('DBCC showfilestats')
> -- add two columns to the temp table
> alter table #db_file_information add dude as
> ((Total_Extents-Used_Extents)/(Total_extents*1.0))
>
> select * from #db_file_information
>
> drop table #db_file_information
>

Wednesday, March 21, 2012

DBCC Problems with syscolumns

Hi, I have run a DBCC CHECKDB and it found 2 Errors in syscolumns. I
have run a CHECKTABLE and found the following Error:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 3, index ID 2: Page (1:266) could not be processed. See other
errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 3, index ID 2, page (1:266), row 12. Test
(VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 178 and 72.
DBCC results for 'syscolumns'.
There are 6963 rows in 144 pages for object 'syscolumns'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'syscolumns' (object ID 3).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (EMIS.dbo.syscolumns ).
Can anyone suggest the best way to proceed with this problem. I am a
little dubious about running REPAIR_ALLOW_DATALOSS on syscolumns, I am
unsure of what will happen if sections of syscolumns go missing. I have
run DBCC PAGE ('EMIS',1,266,1) but this has given me nothing to work
with.
Thanks in Advance,
Dave Shaw.Normally i would suggest DBREINDEX but as this is a system table you're not
allowed.
Give SP_FixIndex a try. You will need the DB in single user mode 1st.
"Dave Shaw" <Dave.Shaw1584@.NTLWorld.com> wrote in message
news:1134990883.398840.145620@.z14g2000cwz.googlegroups.com...
> Hi, I have run a DBCC CHECKDB and it found 2 Errors in syscolumns. I
> have run a CHECKTABLE and found the following Error:
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 3, index ID 2: Page (1:266) could not be processed. See other
> errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 3, index ID 2, page (1:266), row 12. Test
> (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <=> (nextRec - pRec)) failed. Values are 178 and 72.
> DBCC results for 'syscolumns'.
> There are 6963 rows in 144 pages for object 'syscolumns'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'syscolumns' (object ID 3).
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKTABLE (EMIS.dbo.syscolumns ).
> Can anyone suggest the best way to proceed with this problem. I am a
> little dubious about running REPAIR_ALLOW_DATALOSS on syscolumns, I am
> unsure of what will happen if sections of syscolumns go missing. I have
> run DBCC PAGE ('EMIS',1,266,1) but this has given me nothing to work
> with.
> Thanks in Advance,
> Dave Shaw.
>|||This article may help ...
http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14051
"John Smith" <fsfsdf@.microsoft.com> wrote in message
news:enUNTKJBGHA.3872@.TK2MSFTNGP12.phx.gbl...
> Normally i would suggest DBREINDEX but as this is a system table you're
not
> allowed.
> Give SP_FixIndex a try. You will need the DB in single user mode 1st.
>
> "Dave Shaw" <Dave.Shaw1584@.NTLWorld.com> wrote in message
> news:1134990883.398840.145620@.z14g2000cwz.googlegroups.com...
> > Hi, I have run a DBCC CHECKDB and it found 2 Errors in syscolumns. I
> > have run a CHECKTABLE and found the following Error:
> >
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 3, index ID 2: Page (1:266) could not be processed. See other
> > errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 1
> > Table Corrupt: Object ID 3, index ID 2, page (1:266), row 12. Test
> > (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <=> > (nextRec - pRec)) failed. Values are 178 and 72.
> > DBCC results for 'syscolumns'.
> > There are 6963 rows in 144 pages for object 'syscolumns'.
> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> > 'syscolumns' (object ID 3).
> > repair_allow_data_loss is the minimum repair level for the errors found
> > by DBCC CHECKTABLE (EMIS.dbo.syscolumns ).
> >
> > Can anyone suggest the best way to proceed with this problem. I am a
> > little dubious about running REPAIR_ALLOW_DATALOSS on syscolumns, I am
> > unsure of what will happen if sections of syscolumns go missing. I have
> > run DBCC PAGE ('EMIS',1,266,1) but this has given me nothing to work
> > with.
> >
> > Thanks in Advance,
> >
> > Dave Shaw.
> >
>|||Thanks John Smith.
I shall give this a try.
Dave.

Monday, March 19, 2012

DBCC memusage problem

When I use ShrinkDB or reindex and some other DB performance utilities, then when I use DBCC memusage it throws some error however it get corrected when I restart SQL Server
Is there any simplest way to correct this problem without restarting the SQL server
Regards
SunilCan you share the error? We might be able to debug this more easily.
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming
--
Server: Msg 8966, Level 16, State 4, Line
Could not read and latch page (5:562) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1621) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1622) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1623) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1809) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1810) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1811) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1812) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1813) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1814) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1815) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1816) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1840) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1841) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1842) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1843) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1844) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1845) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1846) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1847) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1848) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1863) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1864) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1865) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1866) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1867) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1868) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1869) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1870) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1871) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1872) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1873) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1874) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId failed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC memusage problem

When I use ShrinkDB or reindex and some other DB performance utilities, then
when I use DBCC memusage it throws some error however it get corrected when
I restart SQL Server.
Is there any simplest way to correct this problem without restarting the SQL
server.
Regards,
SunilCan you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming :
--
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId fail
ed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system
administrator.

DBCC memusage problem

When I use ShrinkDB or reindex and some other DB performance utilities, then when I use DBCC memusage it throws some error however it get corrected when I restart SQL Server.
Is there any simplest way to correct this problem without restarting the SQL server.
Regards,
Sunil
Can you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil
|||This error is coming :
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId failed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system administrator.

Thursday, March 8, 2012

dbcc error on sysobjects

Hi,
I am getting error 8970 when I run a dbcc on my production database. Please
help.
Error Details:
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
was created NOT NULL, but is NULL in the row.
DBCC results for 'sysobjects'.
There are 590 rows in 11 pages for object 'sysobjects'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
tia
Take a backup of the database. Restore on another server. Run dbcc checkdb
against the restored database. If it still errors out, run the
repair_rebuild option
and if it still does not work, you may have to run the
REPAIR_ALLOW_DATA_LOSS
See what happens. If everything looks good on this restored database on the
other server after performing the fix, then run it on production. if things
go south, atleast you have a backup of the database restored someplace else.
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database.
> Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column
> 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>
|||Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the needed repair level to
repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrupt data will be removed.
In this case, it might mean that the offending rows from sysobjects has to be removed, hence, the
tables that they "point to" also will be removed. Perhaps someone from MS will jump in and comment
on that. Consider opening a case with MS if reverting to your latest clean backup isn't an option.
Also, see http://www.karaszi.com/SQLServer/inf...suspect_db.asp
And, this is what Books Online has to say about that error:
Error 8970
Severity Level 16
Message Text
Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT
NULL, but is NULL in the row.
Explanation
A column was created as not NULL, but is NULL in the row.
Per-state information:
State 1: The column is a regular data column.
State 2: The column is a SqlVariant and the data contained within it is NULL.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft? Windows NT? system
and application logs and the SQL Server? error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the record is from a non-clustered index, performing a repair will rebuild the index. Otherwise,
the record is deleted and all indexes are rebuilt.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database. Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>
|||Thanks all for your suggestions.
I had tried the REPAIR_ALLOW_DATA_LOSS but since it's sysobjects that's
corrupt, it does not proceed with the dbcc.
Since it's a production database and I cannot go back to an older backup, I
was forced to update the system table.
Updated the column in sysobjects, ran dbcc again. Gave the same error on
another column in the same row, repeated the steps a couple of times.
The table is clean now and dbcc checkdb/checkcatalog too do not give any
errors. Also tried accessing the object and it works fine.
I know the MS guys are going to scream, but this was my last resort!
thanks.
"Tibor Karaszi" wrote:

> Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the needed repair level to
> repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrupt data will be removed.
> In this case, it might mean that the offending rows from sysobjects has to be removed, hence, the
> tables that they "point to" also will be removed. Perhaps someone from MS will jump in and comment
> on that. Consider opening a case with MS if reverting to your latest clean backup isn't an option.
> Also, see http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> And, this is what Books Online has to say about that error:
> Error 8970
> Severity Level 16
> Message Text
> Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT
> NULL, but is NULL in the row.
> Explanation
> A column was created as not NULL, but is NULL in the row.
> Per-state information:
> State 1: The column is a regular data column.
> State 2: The column is a SqlVariant and the data contained within it is NULL.
> Action
> HARDWARE FAILURE
> Run hardware diagnostics and correct any problems. Also examine the Microsoft? Windows NT? system
> and application logs and the SQL Server? error log to see if the error occurred as the result of
> hardware failure. Fix any hardware related problems.
> If you have persistent data corruption problems, try to swap out different hardware components to
> isolate the problem. Check to ensure that your system does not have write caching enabled on the
> disk controller. If you suspect this to be the problem, contact your hardware vendor.
> Finally, you might find it beneficial to switch to a completely new hardware system, including
> reformatting the disk drives and reinstalling the operating system.
> RESTORE FROM BACKUP
> If the problem is not hardware related and a known clean backup is available, restore the database
> from the backup.
> DBCC CHECKDB
> If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
> extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
> CHECKDB with the appropriate repair clause to repair the corruption.
>
> Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
> your primary support provider before executing this statement.
>
> If the record is from a non-clustered index, performing a repair will rebuild the index. Otherwise,
> the record is deleted and all indexes are rebuilt.
>
> Caution This repair may cause data loss.
>
> If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
> primary support provider.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sc_simsl" <sc_simsl@.hotmail.com> wrote in message
> news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
>

dbcc error on sysobjects

Hi,
I am getting error 8970 when I run a dbcc on my production database. Please
help.
Error Details:
--
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
was created NOT NULL, but is NULL in the row.
DBCC results for 'sysobjects'.
There are 590 rows in 11 pages for object 'sysobjects'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
tiaTake a backup of the database. Restore on another server. Run dbcc checkdb
against the restored database. If it still errors out, run the
repair_rebuild option
and if it still does not work, you may have to run the
REPAIR_ALLOW_DATA_LOSS
See what happens. If everything looks good on this restored database on the
other server after performing the fix, then run it on production. if things
go south, atleast you have a backup of the database restored someplace else.
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database.
> Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column
> 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>|||Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the
needed repair level to
repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrup
t data will be removed.
In this case, it might mean that the offending rows from sysobjects has to b
e removed, hence, the
tables that they "point to" also will be removed. Perhaps someone from MS wi
ll jump in and comment
on that. Consider opening a case with MS if reverting to your latest clean b
ackup isn't an option.
Also, see http://www.karaszi.com/SQLServer/in..._suspect_db.asp
And, this is what Books Online has to say about that error:
Error 8970
Severity Level 16
Message Text
Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column
'COLUMN' was created NOT
NULL, but is NULL in the row.
Explanation
A column was created as not NULL, but is NULL in the row.
Per-state information:
State 1: The column is a regular data column.
State 2: The column is a SqlVariant and the data contained within it is NULL
.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsof
t? Windows NT? system
and application logs and the SQL Server? error log to see if the error occ
urred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different h
ardware components to
isolate the problem. Check to ensure that your system does not have write ca
ching enabled on the
disk controller. If you suspect this to be the problem, contact your hardwar
e vendor.
Finally, you might find it beneficial to switch to a completely new hardware
system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available
, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair claus
e to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use
. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has
on your data, contact
your primary support provider before executing this statement.
If the record is from a non-clustered index, performing a repair will rebuil
d the index. Otherwise,
the record is deleted and all indexes are rebuilt.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the
problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database. Pleas
e
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid
'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>|||Thanks all for your suggestions.
I had tried the REPAIR_ALLOW_DATA_LOSS but since it's sysobjects that's
corrupt, it does not proceed with the dbcc.
Since it's a production database and I cannot go back to an older backup, I
was forced to update the system table.
Updated the column in sysobjects, ran dbcc again. Gave the same error on
another column in the same row, repeated the steps a couple of times.
The table is clean now and dbcc checkdb/checkcatalog too do not give any
errors. Also tried accessing the object and it works fine.
I know the MS guys are going to scream, but this was my last resort!
thanks.
"Tibor Karaszi" wrote:

> Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you t
he needed repair level to
> repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corr
upt data will be removed.
> In this case, it might mean that the offending rows from sysobjects has to
be removed, hence, the
> tables that they "point to" also will be removed. Perhaps someone from MS
will jump in and comment
> on that. Consider opening a case with MS if reverting to your latest clean
backup isn't an option.
> Also, see http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> And, this is what Books Online has to say about that error:
> Error 8970
> Severity Level 16
> Message Text
> Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Colum
n 'COLUMN' was created NOT
> NULL, but is NULL in the row.
> Explanation
> A column was created as not NULL, but is NULL in the row.
> Per-state information:
> State 1: The column is a regular data column.
> State 2: The column is a SqlVariant and the data contained within it is NU
LL.
> Action
> hardware FAILURE
> Run hardware diagnostics and correct any problems. Also examine the Micros
oft? Windows NT? system
> and application logs and the SQL Server? error log to see if the error o
ccurred as the result of
> hardware failure. Fix any hardware related problems.
> If you have persistent data corruption problems, try to swap out different
hardware components to
> isolate the problem. Check to ensure that your system does not have write
caching enabled on the
> disk controller. If you suspect this to be the problem, contact your hardw
are vendor.
> Finally, you might find it beneficial to switch to a completely new hardwa
re system, including
> reformatting the disk drives and reinstalling the operating system.
> RESTORE FROM BACKUP
> If the problem is not hardware related and a known clean backup is availab
le, restore the database
> from the backup.
> DBCC CHECKDB
> If no clean backup is available, execute DBCC CHECKDB without a repair cla
use to determine the
> extent of the corruption. DBCC CHECKDB will recommend a repair clause to u
se. Then, execute DBCC
> CHECKDB with the appropriate repair clause to repair the corruption.
>
> Caution If you are unsure what effect DBCC CHECKDB with a repair clause h
as on your data, contact
> your primary support provider before executing this statement.
>
> If the record is from a non-clustered index, performing a repair will rebu
ild the index. Otherwise,
> the record is deleted and all indexes are rebuilt.
>
> Caution This repair may cause data loss.
>
> If running DBCC CHECKDB with one of the repair clauses does not correct th
e problem, contact your
> primary support provider.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sc_simsl" <sc_simsl@.hotmail.com> wrote in message
> news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
>

dbcc error on sysobjects

Hi,
I am getting error 8970 when I run a dbcc on my production database. Please
help.
Error Details:
--
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
was created NOT NULL, but is NULL in the row.
DBCC results for 'sysobjects'.
There are 590 rows in 11 pages for object 'sysobjects'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
tiaTake a backup of the database. Restore on another server. Run dbcc checkdb
against the restored database. If it still errors out, run the
repair_rebuild option
and if it still does not work, you may have to run the
REPAIR_ALLOW_DATA_LOSS
See what happens. If everything looks good on this restored database on the
other server after performing the fix, then run it on production. if things
go south, atleast you have a backup of the database restored someplace else.
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database.
> Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column
> 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>|||This is a multi-part message in MIME format.
--=_NextPart_000_008A_01C60D27.01D7E6D0
Content-Type: text/plain;
format=flowed;
charset="Utf-8";
reply-type=original
Content-Transfer-Encoding: 8bit
Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the needed repair level to
repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrupt data will be removed.
In this case, it might mean that the offending rows from sysobjects has to be removed, hence, the
tables that they "point to" also will be removed. Perhaps someone from MS will jump in and comment
on that. Consider opening a case with MS if reverting to your latest clean backup isn't an option.
Also, see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
And, this is what Books Online has to say about that error:
Error 8970
Severity Level 16
Message Text
Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT
NULL, but is NULL in the row.
Explanation
A column was created as not NULL, but is NULL in the row.
Per-state information:
State 1: The column is a regular data column.
State 2: The column is a SqlVariant and the data contained within it is NULL.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the MicrosoftÂ® Windows NTÂ® system
and application logs and the SQL Serverâ?¢ error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the record is from a non-clustered index, performing a repair will rebuild the index. Otherwise,
the record is deleted and all indexes are rebuilt.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database. Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>
--=_NextPart_000_008A_01C60D27.01D7E6D0
Content-Type: image/gif;
name="caution.gif"
Content-Transfer-Encoding: base64
Content-ID: <008601c60d1e$a0107190$0300a8c0@.tibwork>
R0lGODlhDAALAPcAAAAAADMAAGYAAJkAAMwAAP8AAAAzADMzAGYzAJkzAMwzAP8zAABmADNmAGZm
AJlmAMxmAP9mAACZADOZAGaZAJmZAMyZAP+ZAADMADPMAGbMAJnMAMzMAP/MAAD/ADP/AGb/AJn/
AMz/AP//AAAAMzMAM2YAM5kAM8wAM/8AMwAzMzMzM2YzM5kzM8wzM/8zMwBmMzNmM2ZmM5lmM8xm
M/9mMwCZMzOZM2aZM5mZM8yZM/+ZMwDMMzPMM2bMM5nMM8zMM//MMwD/MzP/M2b/M5n/M8z/M///
MwAAZjMAZmYAZpkAZswAZv8AZgAzZjMzZmYzZpkzZswzZv8zZgBmZjNmZmZmZplmZsxmZv9mZgCZ
ZjOZZmaZZpmZZsyZZv+ZZgDMZjPMZmbMZpnMZszMZv/MZgD/ZjP/Zmb/Zpn/Zsz/Zv//ZgAAmTMA
mWYAmZkAmcwAmf8AmQAzmTMzmWYzmZkzmcwzmf8zmQBmmTNmmWZmmZlmmcxmmf9mmQCZmTOZmWaZ
mZmZmcyZmf+ZmQDMmTPMmWbMmZnMmczMmf/MmQD/mTP/mWb/mZn/mcz/mf//mQAAzDMAzGYAzJkA
zMwAzP8AzAAzzDMzzGYzzJkzzMwzzP8zzABmzDNmzGZmzJlmzMxmzP9mzACZzDOZzGaZzJmZzMyZ
zP+ZzADMzDPMzGbMzJnMzMzMzP/MzAD/zDP/zGb/zJn/zMz/zP//zAAA/zMA/2YA/5kA/8wA//8A
/wAz/zMz/2Yz/5kz/8wz//8z/wBm/zNm/2Zm/5lm/8xm//9m/wCZ/zOZ/2aZ/5mZ/8yZ//+Z/wDM
/zPM/2bM/5nM/8zM///M/wD//zP//2b//5n//8z//////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAKwALAAAAAAMAAsA
QAgiAFkJHEiwoMGDrAooRJhwIUKFBRhCZEhR4MSHDitijDgwIAA7
--=_NextPart_000_008A_01C60D27.01D7E6D0--|||Thanks all for your suggestions.
I had tried the REPAIR_ALLOW_DATA_LOSS but since it's sysobjects that's
corrupt, it does not proceed with the dbcc.
Since it's a production database and I cannot go back to an older backup, I
was forced to update the system table.
Updated the column in sysobjects, ran dbcc again. Gave the same error on
another column in the same row, repeated the steps a couple of times.
The table is clean now and dbcc checkdb/checkcatalog too do not give any
errors. Also tried accessing the object and it works fine.
I know the MS guys are going to scream, but this was my last resort!
thanks.
"Tibor Karaszi" wrote:
> Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the needed repair level to
> repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrupt data will be removed.
> In this case, it might mean that the offending rows from sysobjects has to be removed, hence, the
> tables that they "point to" also will be removed. Perhaps someone from MS will jump in and comment
> on that. Consider opening a case with MS if reverting to your latest clean backup isn't an option.
> Also, see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> And, this is what Books Online has to say about that error:
> Error 8970
> Severity Level 16
> Message Text
> Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT
> NULL, but is NULL in the row.
> Explanation
> A column was created as not NULL, but is NULL in the row.
> Per-state information:
> State 1: The column is a regular data column.
> State 2: The column is a SqlVariant and the data contained within it is NULL.
> Action
> HARDWARE FAILURE
> Run hardware diagnostics and correct any problems. Also examine the MicrosoftÂ® Windows NTÂ® system
> and application logs and the SQL Serverâ?¢ error log to see if the error occurred as the result of
> hardware failure. Fix any hardware related problems.
> If you have persistent data corruption problems, try to swap out different hardware components to
> isolate the problem. Check to ensure that your system does not have write caching enabled on the
> disk controller. If you suspect this to be the problem, contact your hardware vendor.
> Finally, you might find it beneficial to switch to a completely new hardware system, including
> reformatting the disk drives and reinstalling the operating system.
> RESTORE FROM BACKUP
> If the problem is not hardware related and a known clean backup is available, restore the database
> from the backup.
> DBCC CHECKDB
> If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
> extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
> CHECKDB with the appropriate repair clause to repair the corruption.
>
> Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
> your primary support provider before executing this statement.
>
> If the record is from a non-clustered index, performing a repair will rebuild the index. Otherwise,
> the record is deleted and all indexes are rebuilt.
>
> Caution This repair may cause data loss.
>
> If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
> primary support provider.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sc_simsl" <sc_simsl@.hotmail.com> wrote in message
> news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> > Hi,
> > I am getting error 8970 when I run a dbcc on my production database. Please
> > help.
> >
> > Error Details:
> > --
> > Server: Msg 8970, Level 16, State 1, Line 1
> > Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
> > was created NOT NULL, but is NULL in the row.
> > DBCC results for 'sysobjects'.
> > There are 590 rows in 11 pages for object 'sysobjects'.
> > CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> > 'sysobjects' (object ID 1).
> >
> > tia
> >
>

Thursday, March 29, 2012

DBCC SHRINKFILE

Tuesday, March 27, 2012

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

DBCC Shrinkdatabase errors

dbcc shrinkdatabase error

dbcc shrinkdatabase error

dbcc shrinkdatabase error

Sunday, March 25, 2012

DBCC SHRINK DATABASE TIMED OUT

DBCC SHOWFILESTATS

DBCC SHOWFILESTATS

Thursday, March 22, 2012

DBCC Showcontig

DBCC Results into a Table Brings Errors!

Wednesday, March 21, 2012

DBCC Problems with syscolumns

DBCC Problems with syscolumns

Monday, March 19, 2012

DBCC memusage problem

DBCC memusage problem

DBCC memusage problem

Thursday, March 8, 2012

dbcc error on sysobjects

dbcc error on sysobjects

dbcc error on sysobjects

DBfile usage

Blog Archive

About Me