check sql licence: dbcc

Showing posts with label dbcc. Show all posts

Tuesday, March 27, 2012

cleanup the tempdb

Is there currently a way to cleanup the tempdb?
Tried dbcc shrinkfile , but no luck.
Can't restart the server ,because it's on production.
What makes tempDB big ?
and tempDB is in Simple mode , but not truncating it .
ThanksDid you post to enough groups<g>? Tempdb is used for all sorts <pun
intended> of things during the normal operation of the database. What makes
it so big (by the way how big is it?) depends on what your doing but it is
most likely the result of a large join or sort operation. You might want to
check to see if you have a long running open tran as well. If it got that
big once it is most likely going to get that big again (unless it was a
mistake) so you shouldn't bother to shrink it until you know for sure. Next
time you see a lot of activity in it you can investigate to see what is
happening at the time with profiler, sp_who etc.
--
Andrew J. Kelly
SQL Server MVP
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:eADnDxBQDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Is there currently a way to cleanup the tempdb?
> Tried dbcc shrinkfile , but no luck.
> Can't restart the server ,because it's on production.
> What makes tempDB big ?
> and tempDB is in Simple mode , but not truncating it .
> Thanks
>

Sunday, March 25, 2012

Clean Caches command vs recompile

Before I do performance check for stored procedure, I always clean the
caches using the following commands.
DBCC DROPCLEANBUFFERS
Go
DBCC FREEPROCCACHE
GO
But sometimes I think it's annoying as it basically purges all the caches
from the SQL server.
So if I just test one procedure's performance, I can just add 'WITH
recompile' keyword in the end, right?
is that the same thing since this way stored procedure will not use the
caches even caches exist.no, it's not the same thing. both 'with recompile' and 'dbcc freeprocbuffer'
will force recompile of the procedure, but 'dbcc dropcleanbuffers' will
purge the data cache (which is the reason it's not advisable to do it on a
production server).
dean
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23Ut4V3EFFHA.1084@.tk2msftngp13.phx.gbl...
>
> Before I do performance check for stored procedure, I always clean the
> caches using the following commands.
>
>
> DBCC DROPCLEANBUFFERS
> Go
> DBCC FREEPROCCACHE
> GO
>
> But sometimes I think it's annoying as it basically purges all the caches
> from the SQL server.
> So if I just test one procedure's performance, I can just add 'WITH
> recompile' keyword in the end, right?
> is that the same thing since this way stored procedure will not use the
> caches even caches exist.
>
>
>
>

Thursday, March 8, 2012

checktable with repair_rebuild

Hi! Could anyone tell me how much free space is needed to run DBCC
Checktable with repair_rebuild option. I have a table with more than 100
million record with size around 120 G and when I ran the command it failed
because of space issue. I only had around 50 G. of free space left on that
drive.
Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is there
any command that I can use to use Tempdb for the rebuild process instead of
using the database space?
I appreciate your answer.If your running 2000 then you can specify the ESTIMATE_ONLY option to see
how much space you need in tempdb. Check out BOL for more details.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> Hi! Could anyone tell me how much free space is needed to run DBCC
> Checktable with repair_rebuild option. I have a table with more than 100
> million record with size around 120 G and when I ran the command it failed
> because of space issue. I only had around 50 G. of free space left on that
> drive.
> Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
there
> any command that I can use to use Tempdb for the rebuild process instead
of
> using the database space?
> I appreciate your answer.
>
>|||However the ESTIMATEONLY option only works out how much space is required to
run the check - it does not know what repairs may be necessary and how much
space they will require. For an index rebuild, you will need the same amount
of free space as if you were running DBCC DBREINDEX on the same index.
BTW, if you have determined that there is an integrity problem, it is in
your best interestes to do root-cause analysis of the problem and see why it
happened (almost certainly hardware). Examine your event logs, the SQL
Server error logs and run any hardware diagnostics you can - the odds are it
will happen again.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> If your running 2000 then you can specify the ESTIMATE_ONLY option to see
> how much space you need in tempdb. Check out BOL for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > Hi! Could anyone tell me how much free space is needed to run DBCC
> > Checktable with repair_rebuild option. I have a table with more than 100
> > million record with size around 120 G and when I ran the command it
failed
> > because of space issue. I only had around 50 G. of free space left on
that
> > drive.
> > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> there
> > any command that I can use to use Tempdb for the rebuild process instead
> of
> > using the database space?
> >
> > I appreciate your answer.
> >
> >
> >
>|||Thanks for the answer.
In order to find what may have caused this, our Network guys did some online
hardware diognistic and didn't see any problems. Now we are planning to do
offline diognistic. I have one question on this:
Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
focus on? In other words what has been the main culprit from hardware side
causing database corruption, in your experience?
I appreciate your answer.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> However the ESTIMATEONLY option only works out how much space is required
to
> run the check - it does not know what repairs may be necessary and how
much
> space they will require. For an index rebuild, you will need the same
amount
> of free space as if you were running DBCC DBREINDEX on the same index.
> BTW, if you have determined that there is an integrity problem, it is in
> your best interestes to do root-cause analysis of the problem and see why
it
> happened (almost certainly hardware). Examine your event logs, the SQL
> Server error logs and run any hardware diagnostics you can - the odds are
it
> will happen again.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > If your running 2000 then you can specify the ESTIMATE_ONLY option to
see
> > how much space you need in tempdb. Check out BOL for more details.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > Checktable with repair_rebuild option. I have a table with more than
100
> > > million record with size around 120 G and when I ran the command it
> failed
> > > because of space issue. I only had around 50 G. of free space left on
> that
> > > drive.
> > > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> > there
> > > any command that I can use to use Tempdb for the rebuild process
instead
> > of
> > > using the database space?
> > >
> > > I appreciate your answer.
> > >
> > >
> > >
> >
> >
>|||In my experience, the major culprits have been bad drives, cables and
occasional problems with controllers. Make sure you're on the latest
software rev for all your controllers etc.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:uSAp727JEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Thanks for the answer.
> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic. I have one question on this:
> Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
> focus on? In other words what has been the main culprit from hardware side
> causing database corruption, in your experience?
> I appreciate your answer.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> > However the ESTIMATEONLY option only works out how much space is
required
> to
> > run the check - it does not know what repairs may be necessary and how
> much
> > space they will require. For an index rebuild, you will need the same
> amount
> > of free space as if you were running DBCC DBREINDEX on the same index.
> >
> > BTW, if you have determined that there is an integrity problem, it is in
> > your best interestes to do root-cause analysis of the problem and see
why
> it
> > happened (almost certainly hardware). Examine your event logs, the SQL
> > Server error logs and run any hardware diagnostics you can - the odds
are
> it
> > will happen again.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > > If your running 2000 then you can specify the ESTIMATE_ONLY option to
> see
> > > how much space you need in tempdb. Check out BOL for more details.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > > Checktable with repair_rebuild option. I have a table with more than
> 100
> > > > million record with size around 120 G and when I ran the command it
> > failed
> > > > because of space issue. I only had around 50 G. of free space left
on
> > that
> > > > drive.
> > > > Does it require same free space as in DBCC DBReindex (1.2 * Table)?
Is
> > > there
> > > > any command that I can use to use Tempdb for the rebuild process
> instead
> > > of
> > > > using the database space?
> > > >
> > > > I appreciate your answer.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic.
--
Hi James,
You should also consider SQLIOStress tool in your battery of tests:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.
I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com.
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.
|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>
|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how
>
|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and[vbcol=seagreen]
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command
> (how
>

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at com." target="_blank">www.sqlmag.
com.
Check the delta of the CPU usage in sysprocesses to determine how much progr
ess the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how[vbcol=seagreen]
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command[vbcol=seagreen]
> (how
>

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > Hi! I am running dbcc checktable with repair_rebuild option for a table
of
> > 121 Million record (about 150 GB) in size and its already running for 74
> > hours and still going. Table had Keys out of order on page (1:11667248),
> > slots 5 and 6 (Which was clustered Index).
> > Could anyone tell me how long does it normally take to run this command
> for
> > table of this size? Is there any way we can see the status of process
(how
> > far it has gone percentage wise)?
> > Environment:
> > Sql 2k SP2 running on Wi2k Advanced server
> > 8 CPU 2.7 GH and 8 GB RAM.
> >
> >
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> > It's rebuilding the clustered index and all the non-clustered indexes as
> > part of the repair - depending on how much and the distribution of free
> > space this could take a while but I wouldn't expect it to take that
long.
> > What was the exact output from checkdb before you re-ran with repair?
> You'd
> > have been much better off restoring from your backups (which is the
> > recommeneded strategy)
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > > Hi! I am running dbcc checktable with repair_rebuild option for a
table
> of
> > > 121 Million record (about 150 GB) in size and its already running for
74
> > > hours and still going. Table had Keys out of order on page
(1:11667248),
> > > slots 5 and 6 (Which was clustered Index).
> > > Could anyone tell me how long does it normally take to run this
command
> > for
> > > table of this size? Is there any way we can see the status of process
> (how
> > > far it has gone percentage wise)?
> > > Environment:
> > > Sql 2k SP2 running on Wi2k Advanced server
> > > 8 CPU 2.7 GH and 8 GB RAM.
> > >
> > >
> >
> >
>

Saturday, February 25, 2012

Checkpoint and TempDB

We are running SQL 2005, SP1, on Windows 2003.
Occasionally we have a TempDB log that starts growing exponentially. We run
DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
sysindexes for objects like '#%', but nothing shows its face. This happens
very infrequently, and since nothing definitive is found, we reluctantly stop
and restart services.
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time, if checkpoint is on/off, working/not
working?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1I have a similar problelm. We have some in-house databases and some
that we have unfortunately inherited. One of them pulls in millions of
records into tables that are temporary using #tablename. Our company is
not going to pay to change the application at this point, so I have to
deal with it. What I did is create a simple job that checks the size of
the temp db file. I am on 2000 so I used sysfiles. When the size in our
case exceeds 15 gigs, I just use msdb.db.sp_start_job to kick off a job
to shrink the temp db. I think it probably runs about 3X a week, but
has been working to keep everything under control. I also set a max
size of 20 gigs so it doesn't eat up all the space it has on its drive.
Maybe these ideas will help. At least you wouldn't have to stop/start
services and manually intervene. My shrinks generally occur at 2-4 a.m.
when I am sleeping :)
cbrichards via SQLMonster.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:
>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process is
>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
>>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process
>> is
>>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:
>Hi
>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
>> a
>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
>>Hi
>>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2
>> gig
>> a
>>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
*******************************************************************************************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
*******************************************************************************************************************
Roger Wolter[MSFT] wrote:
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>> Wow!
>[quoted text clipped - 14 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> *******************************************************************************************************************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> *******************************************************************************************************************
> Roger Wolter[MSFT] wrote:
>>Does this help?
>>http://support.microsoft.com/kb/317375/
>>If this is urgent you should open a support case.
>> Wow!
>>[quoted text clipped - 14 lines]
>>>
>>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Thanks Uri. I appreciate the info.
Uri Dimant wrote:
>Hi
>> Is there any way to verify at the time of the crisis, if checkpoint is
>> on/off,
>> working/not
>> working?
>1)
>You can monitor the number of pages flushed by a checkpoint
>using PerfMon - SQLServer:Buffer Manager object, Checkpoint
>pages/sec counter
>2)
>You can start SQL Server with the
>traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
>will be recorded in the SQL Server error log, along with the time of the
>checkpoint.
>> While that is a good link, and I have it bookmarked, my initial question
>> that
>[quoted text clipped - 22 lines]
>>>
>>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1

Friday, February 24, 2012

CHECKING the TRANSACTION LOG

I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
2000 Server. Is there a equivalent command or procedure. Thanks.
Hi Tom
Because log files are separate physical files this command is redundant. If
you want to look at the space used in the log file try DBCC SQLPERF
(logspace) http://msdn2.microsoft.com/en-us/library/aa258819(SQL.80).aspx.
For other database consistency checks DBCC CHECKDB, DBCC CHECKTABLE, DBCC
CHECKALLOC...
John
"Tom Reis" wrote:

> I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
> check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
> 2000 Server. Is there a equivalent command or procedure. Thanks.
>
>

CHECKING the TRANSACTION LOG

I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
2000 Server. Is there a equivalent command or procedure. Thanks.What is it in the transaction log that you want to check?
For 7.0 and 2000, DBCC CHECKDB and CHECKCATALOG covers integrity checks for
a database.
For 2005, you don't need CHECKCATALOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OsLvRLaCHHA.4908@.TK2MSFTNGP03.phx.g
bl...
>I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
> check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
> 2000 Server. Is there a equivalent command or procedure. Thanks.
>|||Hi Tom
Because log files are separate physical files this command is redundant. If
you want to look at the space used in the log file try DBCC SQLPERF
(logspace) http://msdn2.microsoft.com/en-us/library/aa258819(SQL.80).aspx.
For other database consistency checks DBCC CHECKDB, DBCC CHECKTABLE, DBCC
CHECKALLOC...
John
"Tom Reis" wrote:

> I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
> check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
> 2000 Server. Is there a equivalent command or procedure. Thanks.
>
>

CHECKING the TRANSACTION LOG

I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
2000 Server. Is there a equivalent command or procedure. Thanks.What is it in the transaction log that you want to check?
For 7.0 and 2000, DBCC CHECKDB and CHECKCATALOG covers integrity checks for a database.
For 2005, you don't need CHECKCATALOG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OsLvRLaCHHA.4908@.TK2MSFTNGP03.phx.gbl...
>I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
> check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
> 2000 Server. Is there a equivalent command or procedure. Thanks.
>|||Hi Tom
Because log files are separate physical files this command is redundant. If
you want to look at the space used in the log file try DBCC SQLPERF
(logspace) http://msdn2.microsoft.com/en-us/library/aa258819(SQL.80).aspx.
For other database consistency checks DBCC CHECKDB, DBCC CHECKTABLE, DBCC
CHECKALLOC...
John
"Tom Reis" wrote:
> I am running SLQ 2000 SP4. I used to use the DBCC CHECKTABLE (syslogs) to
> check the transaction log on a SQL 6.5 server. This doesn't work on a SQL
> 2000 Server. Is there a equivalent command or procedure. Thanks.
>
>

Tuesday, February 14, 2012

Checkdb vs Integrity with indexes

Is there a difference running checkdb -DBCC checkdb ('database') and running
the Integrity with Indexes option of a maintenance plan? The checkdb output
shows detail while the integrity output shows almost no detail.
Thanks
RonWhat about if you manually run DBCC CHECKDB ('database') WITH NO_INFOMSGS?
Do the outputs match then?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> Is there a difference running checkdb -DBCC checkdb ('database') and
running
> the Integrity with Indexes option of a maintenance plan? The checkdb
output
> shows detail while the integrity output shows almost no detail.
> Thanks
> Ron|||No different results.
Because the Integrity with Indexes via the maintenance plan produces
different output than a DBCC checkdb database, I wanted to know if both are
doing the same thing.
I'm thinking of replacing the integrity with a DBCC checkdb, but would like
to know what's the difference between the two besides the output.
"Paul S Randal [MS]" wrote:
> What about if you manually run DBCC CHECKDB ('database') WITH NO_INFOMSGS?
> Do the outputs match then?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > Is there a difference running checkdb -DBCC checkdb ('database') and
> running
> > the Integrity with Indexes option of a maintenance plan? The checkdb
> output
> > shows detail while the integrity output shows almost no detail.
> >
> > Thanks
> >
> > Ron
>
>|||Can you post the output? As far as I'm aware, the integrity check runs DBCC
CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> No different results.
> Because the Integrity with Indexes via the maintenance plan produces
> different output than a DBCC checkdb database, I wanted to know if both
are
> doing the same thing.
> I'm thinking of replacing the integrity with a DBCC checkdb, but would
like
> to know what's the difference between the two besides the output.
> "Paul S Randal [MS]" wrote:
> > What about if you manually run DBCC CHECKDB ('database') WITH
NO_INFOMSGS?
> > Do the outputs match then?
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > > Is there a difference running checkdb -DBCC checkdb ('database') and
> > running
> > > the Integrity with Indexes option of a maintenance plan? The checkdb
> > output
> > > shows detail while the integrity output shows almost no detail.
> > >
> > > Thanks
> > >
> > > Ron
> >
> >
> >|||Here the Integrity output:
1] Database ABCD: Check Data and Index Linkage...
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
Here's the DBCC checkdb output :
Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
04:00:00
DBCC results for 'Cinfo'. [SQLSTATE 01000]
DBCC results for 'sysobjects'. [SQLSTATE 01000]
There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
DBCC results for 'sysindexes'. [SQLSTATE 01000]
There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
DBCC results for 'syscolumns'. [SQLSTATE 01000]
There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
DBCC results for 'systypes'. [SQLSTATE 01000]
There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
DBCC results for 'syscomments'. [SQLSTATE 01000]
There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
DBCC results for 'sysfiles1'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
DBCC results for 'syspermissions'. [SQLSTATE 01000]
There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
DBCC results for 'sysusers'. [SQLSTATE 01000]
There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
DBCC results for 'sysproperties'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
DBCC results for 'sysdepends'. [SQLSTATE 01000]
There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
DBCC results for 'sysreferences'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
[SQLSTATE 01000]
DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
01000]
DBCC results for 'dtproperties'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
CHECKDB found 0 allocation errors and 0 consistency errors in database
'cinfo'. [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. [SQLSTATE 01000]
"Paul S Randal [MS]" wrote:
> Can you post the output? As far as I'm aware, the integrity check runs DBCC
> CHECKDB.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> > No different results.
> >
> > Because the Integrity with Indexes via the maintenance plan produces
> > different output than a DBCC checkdb database, I wanted to know if both
> are
> > doing the same thing.
> >
> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
> like
> > to know what's the difference between the two besides the output.
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > What about if you manually run DBCC CHECKDB ('database') WITH
> NO_INFOMSGS?
> > > Do the outputs match then?
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
> > > running
> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
> > > output
> > > > shows detail while the integrity output shows almost no detail.
> > > >
> > > > Thanks
> > > >
> > > > Ron
> > >
> > >
> > >
>
>|||Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> Here the Integrity output:
> 1] Database ABCD: Check Data and Index Linkage...
>
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Here's the DBCC checkdb output :
> Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
> 04:00:00
> DBCC results for 'Cinfo'. [SQLSTATE 01000]
> DBCC results for 'sysobjects'. [SQLSTATE 01000]
> There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
> DBCC results for 'sysindexes'. [SQLSTATE 01000]
> There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
> DBCC results for 'syscolumns'. [SQLSTATE 01000]
> There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
> DBCC results for 'systypes'. [SQLSTATE 01000]
> There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> DBCC results for 'syscomments'. [SQLSTATE 01000]
> There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
> DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> DBCC results for 'syspermissions'. [SQLSTATE 01000]
> There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
> DBCC results for 'sysusers'. [SQLSTATE 01000]
> There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> DBCC results for 'sysproperties'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
> DBCC results for 'sysdepends'. [SQLSTATE 01000]
> There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
> DBCC results for 'sysreferences'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
> DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
> DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
> DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
> 01000]
> DBCC results for 'dtproperties'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'cinfo'. [SQLSTATE 01000]
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator. [SQLSTATE 01000]
>
> "Paul S Randal [MS]" wrote:
>> Can you post the output? As far as I'm aware, the integrity check runs DBCC
>> CHECKDB.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
>> > No different results.
>> >
>> > Because the Integrity with Indexes via the maintenance plan produces
>> > different output than a DBCC checkdb database, I wanted to know if both
>> are
>> > doing the same thing.
>> >
>> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
>> like
>> > to know what's the difference between the two besides the output.
>> >
>> > "Paul S Randal [MS]" wrote:
>> >
>> > > What about if you manually run DBCC CHECKDB ('database') WITH
>> NO_INFOMSGS?
>> > > Do the outputs match then?
>> > >
>> > > --
>> > > Paul Randal
>> > > Dev Lead, Microsoft SQL Server Storage Engine
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> > >
>> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
>> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
>> > > running
>> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
>> > > output
>> > > > shows detail while the integrity output shows almost no detail.
>> > > >
>> > > > Thanks
>> > > >
>> > > > Ron
>> > >
>> > >
>> > >
>>|||I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
look like the Integrity output.
I guess my question is simply "Does the Integrity (with indexes) job from
the maintenance plan do exactly what DBCC checkdb does?"
"Tibor Karaszi" wrote:
> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> > Here the Integrity output:
> > 1] Database ABCD: Check Data and Index Linkage...
> >
> >
> >
> > (null)
> > (null)
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Here's the DBCC checkdb output :
> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
> > 04:00:00
> >
> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
> > DBCC results for 'systypes'. [SQLSTATE 01000]
> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> > DBCC results for 'syscomments'. [SQLSTATE 01000]
> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
> > DBCC results for 'sysusers'. [SQLSTATE 01000]
> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> > [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
> > 01000]
> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> > CHECKDB found 0 allocation errors and 0 consistency errors in database
> > 'cinfo'. [SQLSTATE 01000]
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator. [SQLSTATE 01000]
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> >> Can you post the output? As far as I'm aware, the integrity check runs DBCC
> >> CHECKDB.
> >>
> >> --
> >> Paul Randal
> >> Dev Lead, Microsoft SQL Server Storage Engine
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> >> > No different results.
> >> >
> >> > Because the Integrity with Indexes via the maintenance plan produces
> >> > different output than a DBCC checkdb database, I wanted to know if both
> >> are
> >> > doing the same thing.
> >> >
> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
> >> like
> >> > to know what's the difference between the two besides the output.
> >> >
> >> > "Paul S Randal [MS]" wrote:
> >> >
> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
> >> NO_INFOMSGS?
> >> > > Do the outputs match then?
> >> > >
> >> > > --
> >> > > Paul Randal
> >> > > Dev Lead, Microsoft SQL Server Storage Engine
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> > >
> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> >> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
> >> > > running
> >> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
> >> > > output
> >> > > > shows detail while the integrity output shows almost no detail.
> >> > > >
> >> > > > Thanks
> >> > > >
> >> > > > Ron
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||> I guess my question is simply "Does the Integrity (with indexes) job from
> the maintenance plan do exactly what DBCC checkdb does?"
All the maintenance wizard does is execute SQL commands. I ran a profiler trace to capture the
command executed by the maintenance wizard:
dbcc checkdb WITH NO_INFOMSGS
If you are curious, you can use profiler while the maint wizard is executing to capture the commands
that it executing against SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:A051C9CE-671A-46FF-BD75-DAE4A408B081@.microsoft.com...
> I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
> look like the Integrity output.
> I guess my question is simply "Does the Integrity (with indexes) job from
> the maintenance plan do exactly what DBCC checkdb does?"
> "Tibor Karaszi" wrote:
>> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
>> > Here the Integrity output:
>> > 1] Database ABCD: Check Data and Index Linkage...
>> >
>> >
>> >
>> > (null)
>> > (null)
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Here's the DBCC checkdb output :
>> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
>> > 04:00:00
>> >
>> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
>> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
>> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
>> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
>> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
>> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
>> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
>> > DBCC results for 'systypes'. [SQLSTATE 01000]
>> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
>> > DBCC results for 'syscomments'. [SQLSTATE 01000]
>> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
>> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
>> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
>> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
>> > DBCC results for 'sysusers'. [SQLSTATE 01000]
>> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
>> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
>> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
>> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
>> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
>> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
>> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
>> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
>> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
>> > [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
>> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
>> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
>> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'cinfo'. [SQLSTATE 01000]
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator. [SQLSTATE 01000]
>> >
>> >
>> > "Paul S Randal [MS]" wrote:
>> >
>> >> Can you post the output? As far as I'm aware, the integrity check runs DBCC
>> >> CHECKDB.
>> >>
>> >> --
>> >> Paul Randal
>> >> Dev Lead, Microsoft SQL Server Storage Engine
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no rights.
>> >>
>> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
>> >> > No different results.
>> >> >
>> >> > Because the Integrity with Indexes via the maintenance plan produces
>> >> > different output than a DBCC checkdb database, I wanted to know if both
>> >> are
>> >> > doing the same thing.
>> >> >
>> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
>> >> like
>> >> > to know what's the difference between the two besides the output.
>> >> >
>> >> > "Paul S Randal [MS]" wrote:
>> >> >
>> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
>> >> NO_INFOMSGS?
>> >> > > Do the outputs match then?
>> >> > >
>> >> > > --
>> >> > > Paul Randal
>> >> > > Dev Lead, Microsoft SQL Server Storage Engine
>> >> > >
>> >> > > This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> > >
>> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
>> >> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
>> >> > > running
>> >> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
>> >> > > output
>> >> > > > shows detail while the integrity output shows almost no detail.
>> >> > > >
>> >> > > > Thanks
>> >> > > >
>> >> > > > Ron
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>|||You can also find the SQL commands that the maintenance plan
runs for whatever options are selected in the following
article:
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
-Sue
On Tue, 15 Feb 2005 06:55:06 -0800, Ron
<Ron@.discussions.microsoft.com> wrote:
>I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
>look like the Integrity output.
>I guess my question is simply "Does the Integrity (with indexes) job from
>the maintenance plan do exactly what DBCC checkdb does?"
>"Tibor Karaszi" wrote:
>> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
>> > Here the Integrity output:
>> > 1] Database ABCD: Check Data and Index Linkage...
>> >
>> >
>> >
>> > (null)
>> > (null)
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Here's the DBCC checkdb output :
>> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
>> > 04:00:00
>> >
>> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
>> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
>> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
>> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
>> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
>> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
>> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
>> > DBCC results for 'systypes'. [SQLSTATE 01000]
>> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
>> > DBCC results for 'syscomments'. [SQLSTATE 01000]
>> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
>> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
>> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
>> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
>> > DBCC results for 'sysusers'. [SQLSTATE 01000]
>> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
>> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
>> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
>> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
>> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
>> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
>> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
>> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
>> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
>> > [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
>> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
>> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
>> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'cinfo'. [SQLSTATE 01000]
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator. [SQLSTATE 01000]
>> >
>> >
>> > "Paul S Randal [MS]" wrote:
>> >
>> >> Can you post the output? As far as I'm aware, the integrity check runs DBCC
>> >> CHECKDB.
>> >>
>> >> --
>> >> Paul Randal
>> >> Dev Lead, Microsoft SQL Server Storage Engine
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no rights.
>> >>
>> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
>> >> > No different results.
>> >> >
>> >> > Because the Integrity with Indexes via the maintenance plan produces
>> >> > different output than a DBCC checkdb database, I wanted to know if both
>> >> are
>> >> > doing the same thing.
>> >> >
>> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
>> >> like
>> >> > to know what's the difference between the two besides the output.
>> >> >
>> >> > "Paul S Randal [MS]" wrote:
>> >> >
>> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
>> >> NO_INFOMSGS?
>> >> > > Do the outputs match then?
>> >> > >
>> >> > > --
>> >> > > Paul Randal
>> >> > > Dev Lead, Microsoft SQL Server Storage Engine
>> >> > >
>> >> > > This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> > >
>> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
>> >> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
>> >> > > running
>> >> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
>> >> > > output
>> >> > > > shows detail while the integrity output shows almost no detail.
>> >> > > >
>> >> > > > Thanks
>> >> > > >
>> >> > > > Ron
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>|||And to answer your question, the integrity job uses DBCC CHECKDB so it's
doing exactly the same as a manual DBCC CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qd8411pgobfai7a405kjpisvroftt9a9ap@.4ax.com...
> You can also find the SQL commands that the maintenance plan
> runs for whatever options are selected in the following
> article:
>
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
> -Sue
> On Tue, 15 Feb 2005 06:55:06 -0800, Ron
> <Ron@.discussions.microsoft.com> wrote:
> >I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it
doesn't
> >look like the Integrity output.
> >
> >I guess my question is simply "Does the Integrity (with indexes) job from
> >the maintenance plan do exactly what DBCC checkdb does?"
> >
> >"Tibor Karaszi" wrote:
> >
> >> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC
commend...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> >> > Here the Integrity output:
> >> > 1] Database ABCD: Check Data and Index Linkage...
> >> >
> >> >
> >> >
> >> > (null)
> >> > (null)
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > Here's the DBCC checkdb output :
> >> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing
2005-02-13
> >> > 04:00:00
> >> >
> >> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
> >> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
> >> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE
01000]
> >> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
> >> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE
01000]
> >> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
> >> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE
01000]
> >> > DBCC results for 'systypes'. [SQLSTATE 01000]
> >> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> >> > DBCC results for 'syscomments'. [SQLSTATE 01000]
> >> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE
01000]
> >> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> >> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
> >> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE
01000]
> >> > DBCC results for 'sysusers'. [SQLSTATE 01000]
> >> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> >> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
> >> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE
01000]
> >> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
> >> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE
01000]
> >> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE
01000]
> >> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
[SQLSTATE 01000]
> >> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE
01000]
> >> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> >> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> >> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> >> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> >> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> >> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> >> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> >> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> >> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> >> > [SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> >> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> >> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> >> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> >> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> >> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE
01000]
> >> > CHECKDB found 0 allocation errors and 0 consistency errors in
database
> >> > 'cinfo'. [SQLSTATE 01000]
> >> > DBCC execution completed. If DBCC printed error messages, contact
your
> >> > system administrator. [SQLSTATE 01000]
> >> >
> >> >
> >> > "Paul S Randal [MS]" wrote:
> >> >
> >> >> Can you post the output? As far as I'm aware, the integrity check
runs DBCC
> >> >> CHECKDB.
> >> >>
> >> >> --
> >> >> Paul Randal
> >> >> Dev Lead, Microsoft SQL Server Storage Engine
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
rights.
> >> >>
> >> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> >> >> > No different results.
> >> >> >
> >> >> > Because the Integrity with Indexes via the maintenance plan
produces
> >> >> > different output than a DBCC checkdb database, I wanted to know if
both
> >> >> are
> >> >> > doing the same thing.
> >> >> >
> >> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but
would
> >> >> like
> >> >> > to know what's the difference between the two besides the output.
> >> >> >
> >> >> > "Paul S Randal [MS]" wrote:
> >> >> >
> >> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
> >> >> NO_INFOMSGS?
> >> >> > > Do the outputs match then?
> >> >> > >
> >> >> > > --
> >> >> > > Paul Randal
> >> >> > > Dev Lead, Microsoft SQL Server Storage Engine
> >> >> > >
> >> >> > > This posting is provided "AS IS" with no warranties, and confers
no
> >> >> rights.
> >> >> > >
> >> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> >> >> > > > Is there a difference running checkdb -DBCC checkdb
('database') and
> >> >> > > running
> >> >> > > > the Integrity with Indexes option of a maintenance plan? The
checkdb
> >> >> > > output
> >> >> > > > shows detail while the integrity output shows almost no
detail.
> >> >> > > >
> >> >> > > > Thanks
> >> >> > > >
> >> >> > > > Ron
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||Thanks Everyone!
"Paul S Randal [MS]" wrote:
> And to answer your question, the integrity job uses DBCC CHECKDB so it's
> doing exactly the same as a manual DBCC CHECKDB.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:qd8411pgobfai7a405kjpisvroftt9a9ap@.4ax.com...
> > You can also find the SQL commands that the maintenance plan
> > runs for whatever options are selected in the following
> > article:
> >
> http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
> >
> > -Sue
> >
> > On Tue, 15 Feb 2005 06:55:06 -0800, Ron
> > <Ron@.discussions.microsoft.com> wrote:
> >
> > >I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it
> doesn't
> > >look like the Integrity output.
> > >
> > >I guess my question is simply "Does the Integrity (with indexes) job from
> > >the maintenance plan do exactly what DBCC checkdb does?"
> > >
> > >"Tibor Karaszi" wrote:
> > >
> > >> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC
> commend...
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > >> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> > >> > Here the Integrity output:
> > >> > 1] Database ABCD: Check Data and Index Linkage...
> > >> >
> > >> >
> > >> >
> > >> > (null)
> > >> > (null)
> > >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > >> >
> > >> > Here's the DBCC checkdb output :
> > >> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing
> 2005-02-13
> > >> > 04:00:00
> > >> >
> > >> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
> > >> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
> > >> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
> > >> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
> > >> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'systypes'. [SQLSTATE 01000]
> > >> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> > >> > DBCC results for 'syscomments'. [SQLSTATE 01000]
> > >> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> > >> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
> > >> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysusers'. [SQLSTATE 01000]
> > >> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> > >> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
> > >> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
> > >> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> > >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> > >> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> > >> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> > >> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> > >> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> > >> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> > >> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> > >> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> > >> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> > >> > [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> > >> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> > >> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> > >> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> > >> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> > >> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE
> 01000]
> > >> > CHECKDB found 0 allocation errors and 0 consistency errors in
> database
> > >> > 'cinfo'. [SQLSTATE 01000]
> > >> > DBCC execution completed. If DBCC printed error messages, contact
> your
> > >> > system administrator. [SQLSTATE 01000]
> > >> >
> > >> >
> > >> > "Paul S Randal [MS]" wrote:
> > >> >
> > >> >> Can you post the output? As far as I'm aware, the integrity check
> runs DBCC
> > >> >> CHECKDB.
> > >> >>
> > >> >> --
> > >> >> Paul Randal
> > >> >> Dev Lead, Microsoft SQL Server Storage Engine
> > >> >>
> > >> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >> >>
> > >> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > >> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> > >> >> > No different results.
> > >> >> >
> > >> >> > Because the Integrity with Indexes via the maintenance plan
> produces
> > >> >> > different output than a DBCC checkdb database, I wanted to know if
> both
> > >> >> are
> > >> >> > doing the same thing.
> > >> >> >
> > >> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but
> would
> > >> >> like
> > >> >> > to know what's the difference between the two besides the output.
> > >> >> >
> > >> >> > "Paul S Randal [MS]" wrote:
> > >> >> >
> > >> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
> > >> >> NO_INFOMSGS?
> > >> >> > > Do the outputs match then?
> > >> >> > >
> > >> >> > > --
> > >> >> > > Paul Randal
> > >> >> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >> >> > >
> > >> >> > > This posting is provided "AS IS" with no warranties, and confers
> no
> > >> >> rights.
> > >> >> > >
> > >> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > >> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > >> >> > > > Is there a difference running checkdb -DBCC checkdb
> ('database') and
> > >> >> > > running
> > >> >> > > > the Integrity with Indexes option of a maintenance plan? The
> checkdb
> > >> >> > > output
> > >> >> > > > shows detail while the integrity output shows almost no
> detail.
> > >> >> > > >
> > >> >> > > > Thanks
> > >> >> > > >
> > >> >> > > > Ron
> > >> >> > >
> > >> >> > >
> > >> >> > >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
>
>

Tuesday, March 27, 2012

cleanup the tempdb

Sunday, March 25, 2012

Clean Caches command vs recompile

Thursday, March 8, 2012

checktable with repair_rebuild

checktable repair_rebuild taking long time

checktable repair_rebuild taking long time

checktable repair_rebuild taking long time

Saturday, February 25, 2012

Checkpoint and TempDB

Friday, February 24, 2012

CHECKING the TRANSACTION LOG

CHECKING the TRANSACTION LOG

CHECKING the TRANSACTION LOG

Tuesday, February 14, 2012

Checkdb vs Integrity with indexes

Checkdb vs Integrity with indexes

check sql licence

Blog Archive

About Me