DBfile usage: level

Showing posts with label level. Show all posts

Sunday, March 25, 2012

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?
Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?
|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent[vbcol=seagreen]
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I
>

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I[vbcol=seagreen]
>

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
> >I have a large table that I cannot seem to bring the extent fragmentation
> > down to an aceptable level. Fist the logical scan density and logical
> > fragmentation were extreamly high so I rebuit the index. This helped
both
> > the logical scan density and logical fragmentation but the extent
> > fragmentation stayed at 65%. I then did an index defrag and the extent
> > fragmentation stayed at 65%. I then Reindexed again and the extent
> > fragmentation dropped to 33%. I defraged and reindexed once more and it
> > droped to 21%. I could not get this number to drop any more from 21% so
I
> > droped the index and recreated it; - and it came back with an extent
> > fragmentation of 34%. Is there a way, short of moving to another file
> > group
> > to get my extent fragmentation down without jumping through all these
> > hoops?
>

Thursday, March 22, 2012

Dbcc Showcontig

DBCC SHOWCONTIG scanning 'MYTAB' table...
Table: 'MYTAB'(50099219); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 150
- Extents Scanned.......................: 25
- Extent Switches.......................: 25
- Avg. Pages per Extent..................: 6.0
- Scan Density [Best Count:Actual Count]......: 73.08% [19:26]
- Logical Scan Fragmentation ..............: 1.33%
- Extent Scan Fragmentation ...............: 96.00%
- Avg. Bytes Free per Page................: 878.5
- Avg. Page Density (full)................: 89.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since Extents Scanned and Extent Switches r equal, why the density is slow, what about pages per Extent, can it be manaully increased?

HowdyI think the reason lies in the fact that you have 2 pages per extent occupied by different objects. If you had 8 pages per extent, the table would take up approx. 19 extents (19*8-Best count). Look up DBCC DBREINDEX and DBCC INDEXDEFRAG in BOL. mojza

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/Artic...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...
>|||Thanks John Smith.
I shall give this a try.
Dave.

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.googlegr oups.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/Articl...rticleID=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.googlegr oups.com...
>
|||Thanks John Smith.
I shall give this a try.
Dave.

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.

DBCC Permissions

Hi,

I want to be able to run the following statement:

DBCC log (tbl_Name)

But I get this error:

Server: Msg 2571, Level 14, State 1, Line 1
User 'HQ\exd188' does not have permission to run DBCC log.

What server permissions would I need to run this statement.

TIA,
EricHere's what I found:

<QUOTE>
From: David
Date: 08-08-03 01:15
Subject: Re: How can I read the content of transaction log?

------------------------
Hi Wallace,

Well, Peter is right, it is very difficult to get some piece of information
out from the log.
It depends on what you are looking for. Have a look at dbcc log command:

dbcc log (dbid, object_id, pagenum, rownum, nrecords, type, printopt)

- dbid: the id of your database in sysdatabases (or you can get it with
db_id("my_db"))
- object_id: if you know especially on which object the transactions you're
looking for are related.
- pagenum / rownum: (optional) you can add the page number (if you don't
have rownum) for this object
or the transaction ID (if you can supply a rownum)
- nrecords: number of records to examine. Put a negative value to get the
last n records.
- type: the type of transaction you can find. It goes from -1 (all records)
to 36. Each number from 1 to 36
represents a xact. For example: 4 for XREC_INSERT, 5 for XREC_DELETE, 9
for XREC_MODIFY, etc...
You can find the list at http://www.kaleidatech.com/dbcc2.htm
- printopt: the print option: 0 if you want only headers, 1 if you want
header + data.

For example, if I want the header of the 20 last records of every xact on my
object_id 263671987 in my database 4:
1>dbcc traceon(3604)
2>go
1>dbcc log(4,263671987,0,0,-20,-1,0);
2>go

If the log is empty, it will say:

LOG SCAN DEFINITION:
Database id : 4
Backward scan: starting at end of log
log records for object id 263671987
maximum of 20 log records.

LOG RECORDS:

Total number of log records 0
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.

If it is not, you will find hexa information. Have the closest look at the
timestamps. They will tell you when
the transaction occured.

You can have a look also at Thierry Antinolfi's DBA Devil website. He
provides a C++ built-in LogExplorer.
http://perso.wanadoo.fr/dbadevil/en/logexpl.html

Chrz, -]

David.

<Wallace> a crit dans le message de news:
3f31cd08.51ec.846930886@.sybase.com
[/QUOTE]

DBCC Page does not appear to functon?

I have a corrupt sql server 2000 database table, dbcc checktable yields:

Server: Msg 8929, Level 16, State 1, Line 1

Object ID 738101670: Errors found in text ID 1922475229184 owned by data record identified by RID = (1:5663655:28) PageId = 53918671.

Server: Msg 8965, Level 16, State 1, Line 1

Table error: Object ID 738101670. The text, ntext, or image node at page (1:5663737), slot 0, text ID 1922475229184 is referenced by page (1:4527446), slot 9, but was not seen in the scan.

When I try to use dbcc page( testdb, page#,1 ) where I plug in any of the reported page#'s above I always get the following:

"Server: Msg 8968, Level 16, State 1, Line 2
Table error: DBCC PAGE page (41728:1) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator."

The only page value which does not yield this error is page 1, which is the only pageno I found on any examples on the web. I guess this is because this is the only page it works on?

Can you post the exact command your using?

Also, can you tell me the result of 'SELECT DB_ID()'?

Thanks,|||Looks like you've got your parameters swapped. It should be dbcc page(testdb, 1, page#). The second parameter is the file number (which will always be 1 if your database has no secondary data files).
|||

You are correct! The format I originally found on the internet was either for a prior version or something else unexplained. I found the correct format in one of Ken Henderson's books.

Thanks!

DBCC Page does not appear to function?

I have a corrupt sql server 2000 database table, dbcc checktable yields:

Server: Msg 8929, Level 16, State 1, Line 1

Object ID 738101670: Errors found in text ID 1922475229184 owned by data record identified by RID = (1:5663655:28) PageId = 53918671.

Server: Msg 8965, Level 16, State 1, Line 1

Table error: Object ID 738101670. The text, ntext, or image node at page (1:5663737), slot 0, text ID 1922475229184 is referenced by page (1:4527446), slot 9, but was not seen in the scan.

When I try to use dbcc page( testdb, page#,1 ) where I plug in any of the reported page#'s above I always get the following:

The only page value which does not yield this error is page 1, which is the only pageno I found on any examples on the web. I guess this is because this is the only page it works on?

Can you post the exact command your using?

Also, can you tell me the result of 'SELECT DB_ID()'?

You are correct! The format I originally found on the internet was either for a prior version or something else unexplained. I found the correct format in one of Ken Henderson's books.

Thanks!

sql

Monday, March 19, 2012

dbcc memusage

when I issued "dbcc memusage",
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId failed.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
--
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQL
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> > when I issued "dbcc memusage",
> > I got the errors:
> > Server: Msg 8966, Level 16, State 4, Line 1
> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
> > failed.
> >
> > but when I issued: "dbcc checkdb", it is working ok...
> >
> > I have searched on the net, couldn't find any solution for it..
> >
> > Would you please advise?
>
>|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> > when I issued "dbcc memusage",
> > I got the errors:
> > Server: Msg 8966, Level 16, State 4, Line 1
> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
> > failed.
> >
> > but when I issued: "dbcc checkdb", it is working ok...
> >
> > I have searched on the net, couldn't find any solution for it..
> >
> > Would you please advise?
>
>|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
>> Well if you look in BooksOnLine it will tell you that it is no longer
>> supported and I believe there were even reports of it being dangerous to
>> run. What is it you are trying to look for?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "renhai" <renhai@.discussions.microsoft.com> wrote in message
>> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>> > when I issued "dbcc memusage",
>> > I got the errors:
>> > Server: Msg 8966, Level 16, State 4, Line 1
>> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
>> > failed.
>> >
>> > but when I issued: "dbcc checkdb", it is working ok...
>> >
>> > I have searched on the net, couldn't find any solution for it..
>> >
>> > Would you please advise?
>>

dbcc memusage

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

DBCC error 913

Hi
I am receiving following error while running DBCC through scripts on my
database.
Server: Msg 913, Level 16, State 10, Line 1
Could not find database ID 1128543719. Database may not be activated yet or
may be in transition.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Microsoft KB says that it will be rectified by the installing SQL SP3, but
my SQL server is already having SP3 loaded.
Previously only one database was showing this message, now another databases
is starting showing the same.
When I run DBCC manully i am not receiving any such message.
Nedd help.
Does the procedure runs whenever SQL Server starts? if so than it's possible
that the database is in recovery state.
If you are still having problems than please post the following information:
1) Script
2) Where/when are you executing this script
3) What is the database doing during that time.
4) Post SP_Who2 snapshot as well.
Also, I would suggest run SQL Profiler and see what's going on.
HTH
Saleem@.sqlnt.com
"Satyaki" wrote:

> Hi
> I am receiving following error while running DBCC through scripts on my
> database.
> Server: Msg 913, Level 16, State 10, Line 1
> Could not find database ID 1128543719. Database may not be activated yet or
> may be in transition.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Microsoft KB says that it will be rectified by the installing SQL SP3, but
> my SQL server is already having SP3 loaded.
> Previously only one database was showing this message, now another databases
> is starting showing the same.
> When I run DBCC manully i am not receiving any such message.
> Nedd help.
|||Hi
Thanks for the response.
The script run on schedule.
There is absolutely no problem with the scripts.
This is simple DBCC script I am running on the server every sunday. The
database is online and in live. I can't provide the scripts, but the script
uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog commands on
each database.
As DBCC runs on single user mode, at that time the database is only used by
the DBCC script.
I can't provide sp_who2 as it run late in night.
I think this much information will help you.
"Saleem Hakani" wrote:
[vbcol=seagreen]
> Does the procedure runs whenever SQL Server starts? if so than it's possible
> that the database is in recovery state.
> If you are still having problems than please post the following information:
> 1) Script
> 2) Where/when are you executing this script
> 3) What is the database doing during that time.
> 4) Post SP_Who2 snapshot as well.
> Also, I would suggest run SQL Profiler and see what's going on.
> HTH
> Saleem@.sqlnt.com
> "Satyaki" wrote:
|||I assume that you use ALTER DATABASE to set in single user mode... Can you post that command? What
rollback options are you using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...[vbcol=seagreen]
> Hi
> Thanks for the response.
> The script run on schedule.
> There is absolutely no problem with the scripts.
> This is simple DBCC script I am running on the server every sunday. The
> database is online and in live. I can't provide the scripts, but the script
> uses regular DBCC checkdb, DBCC checkalloc and DBCC checkcatalog commands on
> each database.
> As DBCC runs on single user mode, at that time the database is only used by
> the DBCC script.
> I can't provide sp_who2 as it run late in night.
> I think this much information will help you.
> "Saleem Hakani" wrote:
|||Hi
I had written a script which take one database at a time and alter it to
single user mode and perform DBCC checking.
I am using sp_dboption dbname,'single user',true command.
There is no explicit rollback option set for performing DBCC.
"Tibor Karaszi" wrote:

> I assume that you use ALTER DATABASE to set in single user mode... Can you post that command? What
> rollback options are you using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
>
>
|||Hi
Sorry, i had given only half of the details you asked for, yes I am using
alter database command
that is
alter database ' + convert(varchar,@.dbname ) + ' set single_user with
ROLLBACK IMMEDIATE
Then I run the command sp_dboption dbname,'Single user',true also.
this command runs inside a cursor and DBCC checkdb commands starts after that.
"Tibor Karaszi" wrote:

> I assume that you use ALTER DATABASE to set in single user mode... Can you post that command? What
> rollback options are you using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:7362432E-A3EA-440E-B0E7-4E6AC29B60C8@.microsoft.com...
>
>
|||Several questions:
1) why are you setting the db to single-user mode at all? CHECKDB runs
online. I can only assume you're running repair without looking at any
problems that may be found - this is a very bad idea.
2) You don't need to run DBCC CHECKALLOC as well as DBCC CHECKDB - CHECKDB
incorporates all the functionality of CHECKALLOC
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:0B799A97-4BAE-450B-82C8-6A5BD4B1D247@.microsoft.com...
> Hi
> Sorry, i had given only half of the details you asked for, yes I am using
> alter database command
> that is
> alter database ' + convert(varchar,@.dbname ) + ' set single_user with
> ROLLBACK IMMEDIATE
> Then I run the command sp_dboption dbname,'Single user',true also.
> this command runs inside a cursor and DBCC checkdb commands starts after
that.[vbcol=seagreen]
> "Tibor Karaszi" wrote:
you post that command? What[vbcol=seagreen]
The[vbcol=seagreen]
script[vbcol=seagreen]
commands on[vbcol=seagreen]
used by[vbcol=seagreen]
possible[vbcol=seagreen]
information:[vbcol=seagreen]
on my[vbcol=seagreen]
activated yet or[vbcol=seagreen]
SP3, but[vbcol=seagreen]
databases[vbcol=seagreen]
|||Thank you Paul
To answer your queries:
1. Yes, I am running DBCC Checkdb with repair_rebuild option. I will
definitly try to change the same.
2. I will check that.
But why this error is comming?, is it a bug, because when i run DBCC checkdb
on the database manually it is not giving any error. Can you help me?
Regards
"Paul S Randal [MS]" wrote:

> Several questions:
> 1) why are you setting the db to single-user mode at all? CHECKDB runs
> online. I can only assume you're running repair without looking at any
> problems that may be found - this is a very bad idea.
> 2) You don't need to run DBCC CHECKALLOC as well as DBCC CHECKDB - CHECKDB
> incorporates all the functionality of CHECKALLOC
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "satyaki" <satyaki@.discussions.microsoft.com> wrote in message
> news:0B799A97-4BAE-450B-82C8-6A5BD4B1D247@.microsoft.com...
> that.
> you post that command? What
> The
> script
> commands on
> used by
> possible
> information:
> on my
> activated yet or
> SP3, but
> databases
>
>

Sunday, March 25, 2012

DBCC Showcontig and extent fragmentation

DBCC Showcontig and extent fragmentation

DBCC Showcontig and extent fragmentation

Thursday, March 22, 2012

Dbcc Showcontig

Wednesday, March 21, 2012

DBCC Problems with syscolumns

DBCC Problems with syscolumns

DBCC Problems with syscolumns

DBCC Problems with syscolumns

DBCC Problems with syscolumns

DBCC Problems with syscolumns

DBCC Permissions

DBCC Page does not appear to functon?

DBCC Page does not appear to function?

Monday, March 19, 2012

dbcc memusage

dbcc memusage

dbcc memusage

Thursday, March 8, 2012

dbcc error on sysobjects

dbcc error on sysobjects

dbcc error on sysobjects

DBCC error 913

DBfile usage

Blog Archive

About Me