DBCC Shrinkdatabase

Hello,
A question related to shrinking a database. Find below details of a
database I use, extracted from sp_helpdb abd sp_spaceused commands
Database
Name : ABC
Size : 56999.44 MB
Status : select into/bulkcopy, trunc. log on chkpt
NameSizeMax SizeGrowth Usage
Abc_Data53734848 KBUnlimited10%data only
Abc_Log 4632576 KBUnlimited10%log only
database_name database_size unallocated space
-----
Abc 56999.44 MB 23566.41 MB
reserved data index_size unused
-- -- --
34235424 KB 30824144 KB 3404648 KB 6632 KB
I am looking to free up the unallocated space of 23 GB. How do I go
about doing the same ?
DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
space.
Should I try "DBCC Shrinkdatabase" ? If so,
a) Is the amount of time taken for the process related to the size of
the database ?
b) If the answer to a) is in the affirmative, how long do you think
shrinking a 56 GB database would take ?
c) Should I stop all activity on the database before doing this ?
d) Will the process lock the database in entirety for the duration of
the execution of the command ?
Thank you,
Narayanan B
> a) Is the amount of time taken for the process related to the size of
> the database ?
Depends more on the level of fragmentation. Obviously, you have parts of
objects at the end of the data file, otherwise the TRUNCATEONLY option would
have been enough.

> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
Again, depends on the level of fragmentation.

> c) Should I stop all activity on the database before doing this ?
Would help, but not necessary.

> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
No, not the entire database.
It may sometimes be a good idea to leave the database size as is, if you
think it would grow to that size some day. This is because if the database
needs to grow dynamically, it comes with a small impact on performance. If
performance is currently an issue, you could try defragmenting individual
tables / indexes.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"narayanan" <narayanan@.mytiger.com> wrote in message
news:13949517.0406030114.52d7432d@.posting.google.c om...
> Hello,
> A question related to shrinking a database. Find below details of a
> database I use, extracted from sp_helpdb abd sp_spaceused commands
> Database
> Name : ABC
> Size : 56999.44 MB
> Status : select into/bulkcopy, trunc. log on chkpt
>
> Name Size Max Size Growth Usage
> ----
> Abc_Data 53734848 KB Unlimited 10% data only
> Abc_Log 4632576 KB Unlimited 10% log only
>
> database_name database_size unallocated space
> ----
--
> Abc 56999.44 MB 23566.41 MB
>
> reserved data index_size unused
> -- -- --
> --
> 34235424 KB 30824144 KB 3404648 KB 6632 KB
>
> I am looking to free up the unallocated space of 23 GB. How do I go
> about doing the same ?
> DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
> space.
> Should I try "DBCC Shrinkdatabase" ? If so,
> a) Is the amount of time taken for the process related to the size of
> the database ?
> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
> c) Should I stop all activity on the database before doing this ?
> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
> Thank you,
> Narayanan B
|||Also, a bit more info is found at:
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/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:eieOE4USEHA.2480@.TK2MSFTNGP10.phx.gbl...
> Depends more on the level of fragmentation. Obviously, you have parts of
> objects at the end of the data file, otherwise the TRUNCATEONLY option would
> have been enough.
> Again, depends on the level of fragmentation.
> Would help, but not necessary.
> No, not the entire database.
> It may sometimes be a good idea to leave the database size as is, if you
> think it would grow to that size some day. This is because if the database
> needs to grow dynamically, it comes with a small impact on performance. If
> performance is currently an issue, you could try defragmenting individual
> tables / indexes.
>
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "narayanan" <narayanan@.mytiger.com> wrote in message
> news:13949517.0406030114.52d7432d@.posting.google.c om...
> --
>

DBCC Shrinkdatabase

Hello,
A question related to shrinking a database. Find below details of a
database I use, extracted from sp_helpdb abd sp_spaceused commands
Database
Name : ABC
Size : 56999.44 MB
Status : select into/bulkcopy, trunc. log on chkpt
Name Size Max Size Growth Usage
----
Abc_Data 53734848 KB Unlimited 10% data only
Abc_Log 4632576 KB Unlimited 10% log only
database_name database_size unallocated space
-----
Abc 56999.44 MB 23566.41 MB
reserved data index_size unused
-- -- --
--
34235424 KB 30824144 KB 3404648 KB 6632 KB
I am looking to free up the unallocated space of 23 GB. How do I go
about doing the same ?
DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
space.
Should I try "DBCC Shrinkdatabase" ? If so,
a) Is the amount of time taken for the process related to the size of
the database ?
b) If the answer to a) is in the affirmative, how long do you think
shrinking a 56 GB database would take ?
c) Should I stop all activity on the database before doing this ?
d) Will the process lock the database in entirety for the duration of
the execution of the command ?
Thank you,
Narayanan B> a) Is the amount of time taken for the process related to the size of
> the database ?
Depends more on the level of fragmentation. Obviously, you have parts of
objects at the end of the data file, otherwise the TRUNCATEONLY option would
have been enough.
> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
Again, depends on the level of fragmentation.
> c) Should I stop all activity on the database before doing this ?
Would help, but not necessary.
> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
No, not the entire database.
It may sometimes be a good idea to leave the database size as is, if you
think it would grow to that size some day. This is because if the database
needs to grow dynamically, it comes with a small impact on performance. If
performance is currently an issue, you could try defragmenting individual
tables / indexes.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"narayanan" <narayanan@.mytiger.com> wrote in message
news:13949517.0406030114.52d7432d@.posting.google.com...
> Hello,
> A question related to shrinking a database. Find below details of a
> database I use, extracted from sp_helpdb abd sp_spaceused commands
> Database
> Name : ABC
> Size : 56999.44 MB
> Status : select into/bulkcopy, trunc. log on chkpt
>
> Name Size Max Size Growth Usage
> ----
> Abc_Data 53734848 KB Unlimited 10% data only
> Abc_Log 4632576 KB Unlimited 10% log only
>
> database_name database_size unallocated space
> ----
--
> Abc 56999.44 MB 23566.41 MB
>
> reserved data index_size unused
> -- -- --
> --
> 34235424 KB 30824144 KB 3404648 KB 6632 KB
>
> I am looking to free up the unallocated space of 23 GB. How do I go
> about doing the same ?
> DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
> space.
> Should I try "DBCC Shrinkdatabase" ? If so,
> a) Is the amount of time taken for the process related to the size of
> the database ?
> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
> c) Should I stop all activity on the database before doing this ?
> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
> Thank you,
> Narayanan B|||Also, a bit more info is found at:
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/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:eieOE4USEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > a) Is the amount of time taken for the process related to the size of
> > the database ?
> Depends more on the level of fragmentation. Obviously, you have parts of
> objects at the end of the data file, otherwise the TRUNCATEONLY option would
> have been enough.
> > b) If the answer to a) is in the affirmative, how long do you think
> > shrinking a 56 GB database would take ?
> Again, depends on the level of fragmentation.
> > c) Should I stop all activity on the database before doing this ?
> Would help, but not necessary.
> > d) Will the process lock the database in entirety for the duration of
> > the execution of the command ?
> No, not the entire database.
> It may sometimes be a good idea to leave the database size as is, if you
> think it would grow to that size some day. This is because if the database
> needs to grow dynamically, it comes with a small impact on performance. If
> performance is currently an issue, you could try defragmenting individual
> tables / indexes.
>
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "narayanan" <narayanan@.mytiger.com> wrote in message
> news:13949517.0406030114.52d7432d@.posting.google.com...
> > Hello,
> >
> > A question related to shrinking a database. Find below details of a
> > database I use, extracted from sp_helpdb abd sp_spaceused commands
> >
> > Database
> > Name : ABC
> > Size : 56999.44 MB
> > Status : select into/bulkcopy, trunc. log on chkpt
> >
> >
> > Name Size Max Size Growth Usage
> > ----
> > Abc_Data 53734848 KB Unlimited 10% data only
> > Abc_Log 4632576 KB Unlimited 10% log only
> >
> >
> > database_name database_size unallocated space
> > ----
> --
> > Abc 56999.44 MB 23566.41 MB
> >
> >
> > reserved data index_size unused
> > -- -- --
> > --
> > 34235424 KB 30824144 KB 3404648 KB 6632 KB
> >
> >
> > I am looking to free up the unallocated space of 23 GB. How do I go
> > about doing the same ?
> > DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
> > space.
> >
> > Should I try "DBCC Shrinkdatabase" ? If so,
> > a) Is the amount of time taken for the process related to the size of
> > the database ?
> > b) If the answer to a) is in the affirmative, how long do you think
> > shrinking a 56 GB database would take ?
> > c) Should I stop all activity on the database before doing this ?
> > d) Will the process lock the database in entirety for the duration of
> > the execution of the command ?
> >
> > Thank you,
> > Narayanan B
>

DBCC Shrinkdatabase

Hello,
A question related to shrinking a database. Find below details of a
database I use, extracted from sp_helpdb abd sp_spaceused commands
Database
Name : ABC
Size : 56999.44 MB
Status : select into/bulkcopy, trunc. log on chkpt
Name Size Max Size Growth Usage
----
Abc_Data 53734848 KB Unlimited 10% data only
Abc_Log 4632576 KB Unlimited 10% log only
database_name database_size unallocated space
----
--
Abc 56999.44 MB 23566.41 MB
reserved data index_size unused
-- -- --
--
34235424 KB 30824144 KB 3404648 KB 6632 KB
I am looking to free up the unallocated space of 23 GB. How do I go
about doing the same ?
DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
space.
Should I try "DBCC Shrinkdatabase" ? If so,
a) Is the amount of time taken for the process related to the size of
the database ?
b) If the answer to a) is in the affirmative, how long do you think
shrinking a 56 GB database would take ?
c) Should I stop all activity on the database before doing this ?
d) Will the process lock the database in entirety for the duration of
the execution of the command ?
Thank you,
Narayanan B> a) Is the amount of time taken for the process related to the size of
> the database ?
Depends more on the level of fragmentation. Obviously, you have parts of
objects at the end of the data file, otherwise the TRUNCATEONLY option would
have been enough.

> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
Again, depends on the level of fragmentation.

> c) Should I stop all activity on the database before doing this ?
Would help, but not necessary.

> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
No, not the entire database.
It may sometimes be a good idea to leave the database size as is, if you
think it would grow to that size some day. This is because if the database
needs to grow dynamically, it comes with a small impact on performance. If
performance is currently an issue, you could try defragmenting individual
tables / indexes.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"narayanan" <narayanan@.mytiger.com> wrote in message
news:13949517.0406030114.52d7432d@.posting.google.com...
> Hello,
> A question related to shrinking a database. Find below details of a
> database I use, extracted from sp_helpdb abd sp_spaceused commands
> Database
> Name : ABC
> Size : 56999.44 MB
> Status : select into/bulkcopy, trunc. log on chkpt
>
> Name Size Max Size Growth Usage
> ----
> Abc_Data 53734848 KB Unlimited 10% data only
> Abc_Log 4632576 KB Unlimited 10% log only
>
> database_name database_size unallocated space
> ----
--
> Abc 56999.44 MB 23566.41 MB
>
> reserved data index_size unused
> -- -- --
> --
> 34235424 KB 30824144 KB 3404648 KB 6632 KB
>
> I am looking to free up the unallocated space of 23 GB. How do I go
> about doing the same ?
> DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
> space.
> Should I try "DBCC Shrinkdatabase" ? If so,
> a) Is the amount of time taken for the process related to the size of
> the database ?
> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
> c) Should I stop all activity on the database before doing this ?
> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
> Thank you,
> Narayanan B|||Also, a bit more info is found at:
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/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:eieOE4USEHA.2480@.TK2MSFTNGP10.phx.gbl
..
> Depends more on the level of fragmentation. Obviously, you have parts of
> objects at the end of the data file, otherwise the TRUNCATEONLY option wou
ld
> have been enough.
>
> Again, depends on the level of fragmentation.
>
> Would help, but not necessary.
>
> No, not the entire database.
> It may sometimes be a good idea to leave the database size as is, if you
> think it would grow to that size some day. This is because if the databas
e
> needs to grow dynamically, it comes with a small impact on performance. I
f
> performance is currently an issue, you could try defragmenting individual
> tables / indexes.
>
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "narayanan" <narayanan@.mytiger.com> wrote in message
> news:13949517.0406030114.52d7432d@.posting.google.com...
> --
>

dbcc memorystatus update for 2005

Is there any update for the details in the output for DBCC memorystatus for
2005 ?
Do you mean like this:
http://support.microsoft.com/kb/907877/en-us
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Is there any update for the details in the output for DBCC memorystatus
> for 2005 ?
>
|||Yes.. thanks.. I had reference to this old one :
http://support.microsoft.com/kb/271624
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u$6x9pXLHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Do you mean like this:
> http://support.microsoft.com/kb/907877/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
>

dbcc memorystatus update for 2005

Is there any update for the details in the output for DBCC memorystatus for
2005 ?Do you mean like this:
http://support.microsoft.com/kb/907877/en-us
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Is there any update for the details in the output for DBCC memorystatus
> for 2005 ?
>|||Yes.. thanks.. I had reference to this old one :
http://support.microsoft.com/kb/271624
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u$6x9pXLHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Do you mean like this:
> http://support.microsoft.com/kb/907877/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
>

dbcc memorystatus update for 2005

Is there any update for the details in the output for DBCC memorystatus for
2005 ?Do you mean like this:
http://support.microsoft.com/kb/907877/en-us
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Is there any update for the details in the output for DBCC memorystatus
> for 2005 ?
>|||Yes.. thanks.. I had reference to this old one :
http://support.microsoft.com/kb/271624
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u$6x9pXLHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Do you mean like this:
> http://support.microsoft.com/kb/907877/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> Is there any update for the details in the output for DBCC memorystatus
>> for 2005 ?
>

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
> >
>