DBCC SHOWCONTIG and SQL2k Maintenance Plan Question

When performing the DBCC SHOWCONTIG will this perform table locks? If
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below for more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...

quote:

---

> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>

---

DBCC SHOWCONTIG and SQL2k Maintenance Plan Question

When performing the DBCC SHOWCONTIG will this perform table locks? If
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below for more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>

DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-SmithMy guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reindex
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
> >I have a 400 gb database that I reindex once monthly through a
> > maintenance plan. The reindexing has consistently taken 13hrs for the
> > last year or so, and this last month it took almost 66hrs.
> > I haven't had any drastic changes in my DB structure, any massive data
> > loads or deletions, there were no scheduled jobs at the time of the
> > reindex (at least not during the time it usually takes), no one is
> > fessing up to any adhoc queries or data modifications during the normal
> > reindexing time, and there are no errors reported in either the job
> > log, or the SLQ error logs.
> > I have worked a bit with Microsoft PSS on this, but they really can't
> > do any post mortem on this and essentially wished me luck in a
> > reduction in my reindexing time next month.
> >
> > Where else would you look for clues if this were your DB?
> > I am working on redesigning my reindexing plans and potentially moving
> > to a dbcc indexdefrag method for the future, but just leaving it at 'I
> > don't know" isn't sitting well with me or my CIO.
> >
> >
> > Any ideas?
> >
> > Meredith Ryan-Smith
> >

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this makes
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:
> > The database recovery models are set to full, is it simply all the index's
> > are being rebuilt and under the full recovery model everything is being
> > logged?
> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> > Hi all
> >
> > I include the 'Reorganize pages with the original amount of free space'
> > option on my database maintenance plan. On some of my databases this makes
> > the transaction log balloon. What's the mechanics of this option?
> >
> > The database recovery models are set to full, is it simply all the index's
> > are being rebuilt and under the full recovery model everything is being
> > logged?
>sql

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command wil
l first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only
reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/pr...r />
idbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX
or INDEXDEFRAG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this make
s
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:

> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command w
ill first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will onl
y reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/pr.../>
2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREIND
EX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
>

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/pro.../ss2kidbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this makes
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:

> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/pro.../ss2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
>
sql

dbcc question

Is there a way to simulate maintenance plan option for
integrity checking called: attempt to repair minor
problems using some dbcc option preferably in mutli user
mode for MSSQL2000.Milan
The command you want is DBCC CHECKDB. It allows three
levels of repair. See Books on line for format and options.
Regards
John|||However, please be aware of two things:
1) you cannot run repair in multi-user mode - the database must be in
single-user mode
2) you should not automatically repair errors. You should always work out
why an error happened (so you can prevent it in future) and whether the
repair may lose some of your data. A preferable strategem is always to
restore from your most recent backups.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:079201c3502d$575fc370$a601280a@.phx.gbl...
> Milan
> The command you want is DBCC CHECKDB. It allows three
> levels of repair. See Books on line for format and options.
> Regards
> John

DBCC Error Logging

Hello,

I am running some DBCC checks as a routine maintenance task on my sql server 2000 servers. I am running DBCC CHECKDB, DBCC CHECKCONSTRAINTS and DBCC CHECKCATALOG. I have established a set of procedures and tables to capture the output from the error log after running these DBCC commands. After running all three, it appears the same info captured for CHECKDB is not logged for these two: CHECKCONSTRAINTS and CHECKCATALOG. Does anyone know why these are not logged and if it is even necessary for me to run these 2 extra DBCC checks? If not, can anyone recommend what database consistency checks should be executed on a daily basis for a production database server?

DBCC CHECKDB (DBNAME) executed by lshores found 0 errors and repaired 0 errors. Elapsed time: 0 hours 20 minutes 32 seconds.I found help for this question in this article http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
and further info in BOL (sqlmaint Utility.)

DBCC DBREINDEX versus INDEXDEFRAG.

(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John PetersonAs my understanding goes, it is better to go for DBCC DBREINDEX command, as it takes care of all the table and view indexes, and any constraints on the columns are automatically taken care of. One can use, DBCC INDEXDEFRAG, if the indexes needs to be used on-line, which allows any DML operations.
So if locking of rows during index rebuliding is not a criteria, I guess you can go with DBCC DBREINDEX.
Am not sure, if I was able to answer yur query.
Thanks
GYK
-- John Peterson wrote: --
(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John Peterson|||John
BOL says:
Unlike DBCC DBREINDEX (or the index building operation in general), DBCC
INDEXDEFRAG is an online operation. It does not hold locks long term and
thus will not block running queries or updates. A relatively unfragmented
index can be defragmented faster than a new index can be built because the
time to defragment is related to the amount of fragmentation. A very
fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged,
regardless of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the
disk because INDEXDEFRAG shuffles the pages in place. To improve the
clustering of pages, rebuild the index.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Thanks, Gert!
The way I read this article, it sounds as if these two techniques *are* different, and
like many things, it depends on your system.
The article seems to suggest that rebuilding the index (DBCC DBREINDEX) should typically
yield better performance than doing a INDEXDEFRAG, but it showed cases where that might
not always be the case.
Now I'm even more confused than ever. I had hoped that the INDEXDEFRAG was essentially a
subset of the DBREINDEX operation so that my choice was simple. ;-)
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:%23B95Rl2pDHA.976@.tk2msftngp13.phx.gbl...
> Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > For our production environment, we have a decent window which to run some
> maintenance
> > scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
> is that
> > essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
> do everything a
> > INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
> be beneficial to
> > run *both*?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>

DBCC DBReindex and Update Statistics

Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van

>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>
On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>

DBCC DBReindex and Update Statistics

Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van

>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>

DBCC commands stability

Hi all,

I have a server from which keeping clients off for maintenance is
difficult. They all have VPN connections and can be online any time
they want, and uptime as always is important.

Now I need to run dbcc shrinkdatabase, checkdb and of course
checkpoint right before backups, and when the log seems to grow. I
just tried dbcc checkdb on my home computer and apparently its really
io and CPU intensive on this dual P3. Can users be running queries and
the occasional update and insert while checkdb is doing its thing? Or
is it better to lock everyone out?

How about shrinkdatabase? Any benchmarks on the stability of these
commands while other clients are running? If tables are getting locked
during these commands, the log file will grow even if shrinkdatabase
is running...

Any commands to show which tables are locked, and by whom or what?

I just tried our 2.5GB database on my dual P3 with 256MB ram home
computer.. checkdb took 8 minutes and shrinkdb took 1.5 minutes. We've
a dualP3 server at work, IBM xSeries 232 with 1GB ram, but even 5
minutes of downtime can hurt if shrinkdb needs to be run during work
hours.

Any thoughts?"Ghazan Haider" <ghazan@.ghazan.haider.name> wrote in message
news:2f57764a.0404091155.33f4439f@.posting.google.c om...
> Hi all,
> I have a server from which keeping clients off for maintenance is
> difficult. They all have VPN connections and can be online any time
> they want, and uptime as always is important.
> Now I need to run dbcc shrinkdatabase, checkdb and of course
> checkpoint right before backups, and when the log seems to grow. I
> just tried dbcc checkdb on my home computer and apparently its really
> io and CPU intensive on this dual P3. Can users be running queries and
> the occasional update and insert while checkdb is doing its thing? Or
> is it better to lock everyone out?
> How about shrinkdatabase? Any benchmarks on the stability of these
> commands while other clients are running? If tables are getting locked
> during these commands, the log file will grow even if shrinkdatabase
> is running...
> Any commands to show which tables are locked, and by whom or what?
> I just tried our 2.5GB database on my dual P3 with 256MB ram home
> computer.. checkdb took 8 minutes and shrinkdb took 1.5 minutes. We've
> a dualP3 server at work, IBM xSeries 232 with 1GB ram, but even 5
> minutes of downtime can hurt if shrinkdb needs to be run during work
> hours.
> Any thoughts?

Yes, why should anyone contemplate running these things during
production hours when you have an automated task queue at your
disposal? Surely any realtime high volume transaction database
system has its natural cycles of usage and minimal usage?

Always move routine tasks into this window of opportunity via
automation and queuing of the task to the off-peak times.

--
Pete Brown
Winluck P/L
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au/software|||> Yes, why should anyone contemplate running these things during
> production hours when you have an automated task queue at your
> disposal? Surely any realtime high volume transaction database
> system has its natural cycles of usage and minimal usage?
> Always move routine tasks into this window of opportunity via
> automation and queuing of the task to the off-peak times.

The log file simply blows up at the wrongest of times, swallows all of
the 36GB disk in a matter of 12 minutes. Apparently something is
locked while some other heavy transaction or bulk upload is going on.
Some of the financial transactions are really heavy and update lots of
rows. I'd just like to have the flexibility to checkpoint and shrinkdb
the database, and know what is locked and why.|||Why do you want to shrink the database? You've said it has very heavy
usage - if you shrink it, it'll grow again. That's because it needs all the
space for regular running - this is demonstrated by your (I'm assuming) need
to shrink every so often. Why cause the extra work for no gain? You'd be far
better off not shrinking the database at all.

You can run shrink and checkdb at any time, although they can cause up to a
20% drop (observed on a test system - YMMV) in transaction throughput.

Regards.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Ghazan Haider" <ghazan@.ghazan.haider.name> wrote in message
news:2f57764a.0404092151.27001b80@.posting.google.c om...
> > Yes, why should anyone contemplate running these things during
> > production hours when you have an automated task queue at your
> > disposal? Surely any realtime high volume transaction database
> > system has its natural cycles of usage and minimal usage?
> > Always move routine tasks into this window of opportunity via
> > automation and queuing of the task to the off-peak times.
> The log file simply blows up at the wrongest of times, swallows all of
> the 36GB disk in a matter of 12 minutes. Apparently something is
> locked while some other heavy transaction or bulk upload is going on.
> Some of the financial transactions are really heavy and update lots of
> rows. I'd just like to have the flexibility to checkpoint and shrinkdb
> the database, and know what is locked and why.|||"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:<4078339a$1@.news.microsoft.com>...
> Why do you want to shrink the database? You've said it has very heavy
> usage - if you shrink it, it'll grow again. That's because it needs all the
> space for regular running - this is demonstrated by your (I'm assuming) need
> to shrink every so often. Why cause the extra work for no gain? You'd be far
> better off not shrinking the database at all.
> You can run shrink and checkdb at any time, although they can cause up to a
> 20% drop (observed on a test system - YMMV) in transaction throughput.

We've had the system freeze with the message transaction log full (no
more transactions or ERP system logins which inserts rows). That was
when the log file grew to several gigabytes and filled up the disk.

It'd be nice to be able to check what is locked and why, making the
logs grow. The shrink and checkdb are just an assurance after
unlocking whatever is locked, and running checkpoint to make sure
everything has been committed, so work can begin all over again. I
wouldnt need to really shrinkdb if theres a command to show the number
of uncommitted transactions in the log file, so I know everything has
been flushed.|||"Ghazan Haider" <ghazan@.ghazan.haider.name> wrote:

...[trim]...

> wouldnt need to really shrinkdb if theres a command to show the number
> of uncommitted transactions in the log file, so I know everything has
> been flushed.

Look up dbcc opentran

--
Pete Brown
Winluck P/L
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au/software

DBCC CHECKDB WITH NO_INFOMSGS Error

When I run either "DBCC CHECKDB WITH NO_INFOMSGS" or using "check database integrity" in maintenance plan, I always got an error below:

The In-row data RSVD page count for object "tablename", index ID 0, partition ID 76911687696384, alloc unit ID 76911687696384 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1173579219).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.

Can anyone help me please?

Regards,

What happens after you run DBCC UPDATEUSAGE?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thank you Denis. It was perfect after running DBCC UPDATEUSAGE('dbname').

Cheers,

DBCC CheckDB

Hi:

As part of database maintenance plan, I am using the option (checkbox) for checking the integrity of the database before backing up database or transaction log.

I am not sure when this database integrity check happens (DBCC CheckDB). They seem to be happening at different times not necessarily right before database backup. Does anyone have more details on this?

Also I am backing up my user and system databases every night with database integrity checkup. This is a live eCommerce site. Usually database integrity check should not take long but if it does, will it block users. Is it good idea to do it every night? I was thinking of backing up system database only on weekends.

Thanks

Hi Mike. From the sounds of it, you're referring to a SQL 2000 system I assume.

Can't say really if it's a good idea or not to perform the checkdb nightly, I can surely say it's not a bad thing to do it, other than if it causes problems like you stated (i.e. blocking)...another possible problem could be that it 'thrashes' the buffer pool, and possibly leaves unneeded data in your cache rather than data that is normally there, so in the morning you may see some slowdown on queries until the wanted data resides in cache again.

One thing many people will do on larger, OLTP type systems is perform a checkdb on the production system weekly (or something like that) and perform a nightly checkdb on a restore of the live database on a test server.

As for the schedule of when they occur, if you go to the 'integrity' tab of the existing maintanence plan, at the bottom of the screen there will be a schedule listed there for the integrity check portion of the plan only. Each of the tabs (Optimizations, Integrity, Backup, etc.) have a seperate schedule assigned that defines when that particular operation is begun. Additionaly, you could check the schedule of the SQL Agent Jobs that are created to actually kick off the maintanence tasks.

HTH

DBCC CHECKDB

It looks like I have to setup maintenance for my 24x7 SQL Server 2000
manually. Most of the relevant pages in BOL are clear enough, but DBCC
CHECKDB isn't so clear.
I would like to schedule DBCC CHECKDB for nightly execution and (so far) I
haven't seen anything that would alter DDL at night, which is the only
restriction I see. I also have no intent of actually doing a repair
initially.
Is this the right routine? Is this the right usage?
Thanks,
JayHi Jay
"Jay" wrote:
> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
> manually. Most of the relevant pages in BOL are clear enough, but DBCC
> CHECKDB isn't so clear.
> I would like to schedule DBCC CHECKDB for nightly execution and (so far) I
> haven't seen anything that would alter DDL at night, which is the only
> restriction I see. I also have no intent of actually doing a repair
> initially.
> Is this the right routine? Is this the right usage?
> Thanks,
> Jay
>
You may want to check out Paul Randals blog posts on this at
https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
How often you run it will depend on many factors, but I don't know of anyone
that does it nightly! If you have scheduled other DBCC commands you may be
running some checks more than once, but you may want to run the these
commands rather than DBCC CHECKDB to reduce the time/workload on your servers.
John|||After digging in BOL, I see that DBCHECK includes CHECKALLOC and CHECKTABLE
(anything else?).
Since the runtime for CHECKDB is under 3 minutes for the primary database on
the dev system, even when expanded out I don't think it's that big a deal to
just use it.
Still, if I can do everything CHECKDB does (maybe even a little more) in an
application that spreads the load out, I should at least consider it.
So, what else is DBCHECK doing?
What else SHOULD it be doing?
Thanks,
Jay
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
> Hi Jay
> "Jay" wrote:
>> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
>> manually. Most of the relevant pages in BOL are clear enough, but DBCC
>> CHECKDB isn't so clear.
>> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
>> I
>> haven't seen anything that would alter DDL at night, which is the only
>> restriction I see. I also have no intent of actually doing a repair
>> initially.
>> Is this the right routine? Is this the right usage?
>> Thanks,
>> Jay
> You may want to check out Paul Randals blog posts on this at
> https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
> How often you run it will depend on many factors, but I don't know of
> anyone
> that does it nightly! If you have scheduled other DBCC commands you may be
> running some checks more than once, but you may want to run the these
> commands rather than DBCC CHECKDB to reduce the time/workload on your
> servers.
> John|||Include DBCC CHECKCATALOG as well. SQL2005 includes this in CheckDB but 2000
omitted it.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:uowYuIc5HHA.2380@.TK2MSFTNGP02.phx.gbl...
> After digging in BOL, I see that DBCHECK includes CHECKALLOC and
> CHECKTABLE (anything else?).
> Since the runtime for CHECKDB is under 3 minutes for the primary database
> on the dev system, even when expanded out I don't think it's that big a
> deal to just use it.
> Still, if I can do everything CHECKDB does (maybe even a little more) in
> an application that spreads the load out, I should at least consider it.
> So, what else is DBCHECK doing?
> What else SHOULD it be doing?
> Thanks,
> Jay
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
>> Hi Jay
>> "Jay" wrote:
>> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
>> manually. Most of the relevant pages in BOL are clear enough, but DBCC
>> CHECKDB isn't so clear.
>> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
>> I
>> haven't seen anything that would alter DDL at night, which is the only
>> restriction I see. I also have no intent of actually doing a repair
>> initially.
>> Is this the right routine? Is this the right usage?
>> Thanks,
>> Jay
>> You may want to check out Paul Randals blog posts on this at
>> https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
>> How often you run it will depend on many factors, but I don't know of
>> anyone
>> that does it nightly! If you have scheduled other DBCC commands you may
>> be
>> running some checks more than once, but you may want to run the these
>> commands rather than DBCC CHECKDB to reduce the time/workload on your
>> servers.
>> John
>|||Hi Jay
DBCC CHECKDB can take a long time, so as you move forward you would have to
keep an eye on the duration and workload it places on the server.
John
"Jay" wrote:
> After digging in BOL, I see that DBCHECK includes CHECKALLOC and CHECKTABLE
> (anything else?).
> Since the runtime for CHECKDB is under 3 minutes for the primary database on
> the dev system, even when expanded out I don't think it's that big a deal to
> just use it.
> Still, if I can do everything CHECKDB does (maybe even a little more) in an
> application that spreads the load out, I should at least consider it.
> So, what else is DBCHECK doing?
> What else SHOULD it be doing?
> Thanks,
> Jay
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
> > Hi Jay
> >
> > "Jay" wrote:
> >
> >> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
> >> manually. Most of the relevant pages in BOL are clear enough, but DBCC
> >> CHECKDB isn't so clear.
> >>
> >> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
> >> I
> >> haven't seen anything that would alter DDL at night, which is the only
> >> restriction I see. I also have no intent of actually doing a repair
> >> initially.
> >>
> >> Is this the right routine? Is this the right usage?
> >>
> >> Thanks,
> >> Jay
> >>
> > You may want to check out Paul Randals blog posts on this at
> > https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
> >
> > How often you run it will depend on many factors, but I don't know of
> > anyone
> > that does it nightly! If you have scheduled other DBCC commands you may be
> > running some checks more than once, but you may want to run the these
> > commands rather than DBCC CHECKDB to reduce the time/workload on your
> > servers.
> >
> > John
>
>